db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5823) Multi-row insert fails on table without generated keys with RETURN_GENERATED_KEYS
Date Wed, 30 Oct 2013 15:01:53 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5823?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13809188#comment-13809188
] 

Knut Anders Hatlen commented on DERBY-5823:
-------------------------------------------

There are multiple problems in this area:

1. InsertResultSet collects generated keys even in the case where there are no auto-generated
keys. This is unnecessary, and causes problems when overflowing to a heap conglomerate since
heap conglomerates don't support zero-sized rows. It should stop doing this.

2. On re-execution of a PreparedStatement that was created with RETURN_GENERATED_KEYS, InsertResultSet
will collect all columns, not only key columns. This is because InsertResultSet only calculates
the key columns on the first execution, but it doesn't cache the result, so on re-execution
it will be null, which the code interprets as all columns. This makes the temporary row holder
take up more space than necessary. One odd effect of this problem is that it cancels problem
(1) on re-execution. That is, if you call executeUpdate() on a PS and get the "Conglomerate
could not be created" error mentioned in the bug description, calling executeUpdate() again
on the same PS will succeed. It succeeds the second time because then it adds all inserted
columns to the temporary row holder, and therefore doesn't hit the problem with zero-length
rows. Of course, it should add the same set of columns to the temporary row holder regardless
of how many times the PS is executed, so this should be fixed together with problem (1).

3. As Kristian noted, getGeneratedKeys() is supposed to return an empty ResultSet if no keys
were generated, but it returns a ResultSet with one row. This is because the collected set
of keys is discarded by getGeneratedKeys() and instead the result of VALUES IDENTITY_VAL_LOCAL()
is returned. I believe this part of the problem will be handled by DERBY-3609, and does not
need to be tackled in this issue.

> Multi-row insert fails on table without generated keys with RETURN_GENERATED_KEYS
> ---------------------------------------------------------------------------------
>
>                 Key: DERBY-5823
>                 URL: https://issues.apache.org/jira/browse/DERBY-5823
>             Project: Derby
>          Issue Type: Bug
>          Components: JDBC
>    Affects Versions: 10.7.1.1, 10.8.2.2, 10.9.1.0, 10.10.1.1
>            Reporter: Kristian Waagan
>            Assignee: Knut Anders Hatlen
>            Priority: Minor
>              Labels: derby_triage10_10
>         Attachments: derby-5823-1a-regression_test.diff
>
>
> Exeuting an insert that results in multiple rows being inserted into a table without
any generated keys defined fails if the JDBC statement is configured to return generated keys.
Example stack trace:
> Caused by: ERROR XSCH4: Conglomerate could not be created.
>         at org.apache.derby.iapi.error.StandardException.newException(StandardException.java:268)
>         at org.apache.derby.impl.store.access.heap.Heap.create(Heap.java:296)
>         at org.apache.derby.impl.store.access.heap.HeapConglomerateFactory.createConglomerate(HeapConglomerateFactory.java:206)
>         at org.apache.derby.impl.store.access.RAMTransaction.createConglomerate(RAMTransaction.java:820)
>         at org.apache.derby.impl.sql.execute.TemporaryRowHolderImpl.insert(TemporaryRowHolderImpl.java:302)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(InsertResultSet.java:1007)
>         at org.apache.derby.impl.sql.execute.InsertResultSet.open(InsertResultSet.java:508)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:443)
>         at org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:324)
>         at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1242)
>         ... 30 more
> This happens only with multi-row inserts, and only with the embedded driver (the client
driver uses IDENTITY_VAL_LOCAL).
> The problem is that Derby tries to create a temporary conglomerate with a zero-length
row template. A guard against this scenario is probably missing.
> FYI, the transition threshold (from in-memory to on-disk) is currently five rows.
> I marked versions back to 10.7 as affected, but this bug probably goes back all the way.



--
This message was sent by Atlassian JIRA
(v6.1#6144)

Mime
View raw message