Identity Column/Unique Constraint

[Raymond Kroeker <ra...@gmail.com>]

Hi All,
   I've upgraded from derby 10.2.2.0 to 10.3.2.1.  My application
caches prepared statements (or more accurately uses dbcp to cache
connections/statements) and now the queries I'm using to
insert/retrieve identity column values no longer work.

   I can run this code fine using 10.2.2.0; then upgrade the same db
to 10.3.2.1 and run the same code again and it fails.  I checked the
release notes specifically the identity tickets; but could not find
anything I thought was relevant.  The symptom is that subsequent
attempts to insert the identity fail with a unique constraint
exception:
java.sql.SQLIntegrityConstraintViolationException: The statement was
aborted because it would have caused a duplicate key value in a unique
or primary key constraint or unique index identified by
'SQL080408092334110' defined on 'X'.
     at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown
Source)
     at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
     at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
     at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
     at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
     at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
     at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown
Source)
     at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(Unknown
Source)
     at org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeUpdate(Unknown
Source)
     at genkeys.Main.runGenerateKeys(Main.java:89)
     at genkeys.Main.run(Main.java:73)
     at genkeys.Main.main(Main.java:39)
 Caused by: java.sql.SQLException: The statement was aborted because
it would have caused a duplicate key value in a unique or primary key
constraint or unique index identified by 'SQL080408092334110' defined
on 'X'.
     at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown
Source)
     at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
     ... 12 more
 Caused by: ERROR 23505: The statement was aborted because it would
have caused a duplicate key value in a unique or primary key
constraint or unique index identified by 'SQL080408092334110' defined
on 'X'.
     at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
     at org.apache.derby.impl.sql.execute.IndexChanger.insertAndCheckDups(Unknown
Source)
     at org.apache.derby.impl.sql.execute.IndexChanger.doInsert(Unknown Source)
     at org.apache.derby.impl.sql.execute.IndexChanger.insert(Unknown Source)
     at org.apache.derby.impl.sql.execute.IndexSetChanger.insert(Unknown Source)
     at org.apache.derby.impl.sql.execute.RowChangerImpl.insertRow(Unknown
Source)
     at org.apache.derby.impl.sql.execute.InsertResultSet.normalInsertCore(Unknown
Source)
     at org.apache.derby.impl.sql.execute.InsertResultSet.open(Unknown Source)
     at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown
Source)
     ... 6 more
 Java Result: 1


   I'm running JDK 1.6.0_02-b05; on Ubuntu Linux 7.04.

   Any pointers would be appreciated.

Example Code:
Table Creation:
        final Connection cx = openConnection();
        PreparedStatement ps = null;
        try {
            ps = cx.prepareStatement("create table X(X_ID bigint
generated always as identity(start with 1000),primary key(X_ID))");
            ps.executeUpdate();

            cx.commit();
        } finally {
            close(cx, ps);
        }
Key Gen:
       final Connection cx = openConnection();
       PreparedStatement ps1 = null, ps2 = null;
       ps1 = cx.prepareStatement("insert into X(X_ID) values(DEFAULT)");
       try {
           ps2 = cx.prepareStatement("select IDENTITY_VAL_LOCAL()
\"ID\" from X");
           try {
               Long xid;
               for (int i = 0; i < GENERATE_X_COUNT; i++) {
                   try {
                       /* insert id */
                       if (1 != ps1.executeUpdate()) {
                           throw new RuntimeException("Could not insert.");
                       }
                   } finally {
                       ps1.clearParameters();
                   }
                   try {
                       ResultSet rs = null;
                       try {
                           /* retreive id */
                           rs = ps2.executeQuery();
                           if (rs.next()) {
                               xid = rs.getLong("ID");
                           } else {
                               xid = null;
                           }
                       } finally {
                           close(rs);
                       }
                   } finally {
                       ps2.clearParameters();
                   }
                   logger.debug("xid:" + xid);
               }
               cx.commit();
           } finally {
               close(ps2);
           }
       } finally {
           close(ps1);
       }

-- 
---------------------------------------------------------
Raymond Kroeker