glassfish
  1. glassfish
  2. GLASSFISH-2500

ID Generation Strategy SEQUENCE fails with Postgres, IDENTITY fails with DB2

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 9.1pe
    • Fix Version/s: 9.1pe
    • Component/s: entity-persistence
    • Labels:
      None
    • Environment:

      Operating System: Solaris
      Platform: Sun

    • Issuezilla Id:
      2,500

      Description

      Glassfish build 33e; JavaEE - Java2DB mode

      Using id generation strategy SEQUENCE with Postgres results in the below
      exception on persisting the entity.

      ***Exception***
      javax.persistence.RollbackException: Exception [TOPLINK-4002] (Oracle TopLink
      Essentials - 9.1 (Build b33)):
      oracle.toplink.essentials.exceptions.DatabaseException
      Internal Exception: org.postgresql.util.PSQLException: ERROR: relation
      "seq_gen_sequence" does not existError Code: 0
      Call:select currval('SEQ_GEN_SEQUENCE')
      Query:ValueReadQuery()
      at
      oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:105)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:45)
      at
      tests.TestEntityUsingSequence.persistEntity(TestEntityUsingSequence.java:34)
      at tests.TestEntityUsingSequence.test(TestEntityUsingSequence.java:21)
      at tests.TestsRunner.main(TestsRunner.java:13)
      Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build
      b33)): oracle.toplink.essentials.exceptions.DatabaseException
      Internal Exception: org.postgresql.util.PSQLException: ERROR: relation
      "seq_gen_sequence" does not existError Code: 0
      Call:select currval('SEQ_GEN_SEQUENCE')
      Query:ValueReadQuery()
      at
      oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
      at
      oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:675)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:213)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeCall(DatasourceCallQueryMechanism.java:199)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelectCall(DatasourceCallQueryMechanism.java:270)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.executeSelect(DatasourceCallQueryMechanism.java:252)
      at
      oracle.toplink.essentials.queryframework.DirectReadQuery.executeNonCursor(DirectReadQuery.java:110)
      at
      oracle.toplink.essentials.queryframework.DataReadQuery.executeDatabaseQuery(DataReadQuery.java:97)
      at
      oracle.toplink.essentials.queryframework.ValueReadQuery.executeDatabaseQuery(ValueReadQuery.java:73)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.internalExecuteQuery(AbstractSession.java:1815)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:894)
      at
      oracle.toplink.essentials.sequencing.QuerySequence.select(QuerySequence.java:299)
      at
      oracle.toplink.essentials.sequencing.QuerySequence.updateAndSelectSequence(QuerySequence.java:245)
      at
      oracle.toplink.essentials.sequencing.StandardSequence.getGeneratedValue(StandardSequence.java:71)
      at
      oracle.toplink.essentials.sequencing.Sequence.getGeneratedValue(Sequence.java:234)
      at
      oracle.toplink.essentials.internal.sequencing.SequencingManager$NoPreallocation_State.getNextValue(SequencingManager.java:548)
      at
      oracle.toplink.essentials.internal.sequencing.SequencingManager.getNextValue(SequencingManager.java:832)
      at
      oracle.toplink.essentials.internal.sequencing.ClientSessionSequencing.getNextValue(ClientSessionSequencing.java:95)
      at
      oracle.toplink.essentials.internal.descriptors.ObjectBuilder.assignSequenceNumber(ObjectBuilder.java:225)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.updateObjectAndRowWithSequenceNumber(DatabaseQueryMechanism.java:977)
      at
      oracle.toplink.essentials.internal.queryframework.DatasourceCallQueryMechanism.insertObject(DatasourceCallQueryMechanism.java:336)
      at
      oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:176)
      at
      oracle.toplink.essentials.internal.queryframework.StatementQueryMechanism.insertObject(StatementQueryMechanism.java:190)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWrite(DatabaseQueryMechanism.java:457)
      at
      oracle.toplink.essentials.queryframework.InsertObjectQuery.executeCommit(InsertObjectQuery.java:74)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedWrite(DatabaseQueryMechanism.java:635)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.performUserDefinedInsert(DatabaseQueryMechanism.java:599)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.insertObjectForWriteWithChangeSet(DatabaseQueryMechanism.java:495)
      at
      oracle.toplink.essentials.queryframework.WriteObjectQuery.executeCommitWithChangeSet(WriteObjectQuery.java:130)
      at
      oracle.toplink.essentials.internal.queryframework.DatabaseQueryMechanism.executeWriteWithChangeSet(DatabaseQueryMechanism.java:283)
      at
      oracle.toplink.essentials.queryframework.WriteObjectQuery.executeDatabaseQuery(WriteObjectQuery.java:67)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.execute(DatabaseQuery.java:609)
      at
      oracle.toplink.essentials.queryframework.DatabaseQuery.executeInUnitOfWork(DatabaseQuery.java:536)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWorkObjectLevelModifyQuery(ObjectLevelModifyQuery.java:123)
      at
      oracle.toplink.essentials.queryframework.ObjectLevelModifyQuery.executeInUnitOfWork(ObjectLevelModifyQuery.java:95)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.internalExecuteQuery(UnitOfWorkImpl.java:2219)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:937)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.executeQuery(AbstractSession.java:894)
      at
      oracle.toplink.essentials.internal.sessions.CommitManager.commitNewObjectsForClassWithChangeSet(CommitManager.java:254)
      at
      oracle.toplink.essentials.internal.sessions.CommitManager.commitAllObjectsWithChangeSet(CommitManager.java:175)
      at
      oracle.toplink.essentials.internal.sessions.AbstractSession.writeAllObjectsWithChangeSet(AbstractSession.java:2638)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabase(UnitOfWorkImpl.java:1030)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.commitToDatabase(RepeatableWriteUnitOfWork.java:371)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitToDatabaseWithChangeSet(UnitOfWorkImpl.java:1112)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.base.RepeatableWriteUnitOfWork.commitRootUnitOfWork(RepeatableWriteUnitOfWork.java:91)
      at
      oracle.toplink.essentials.internal.sessions.UnitOfWorkImpl.commitAndResume(UnitOfWorkImpl.java:842)
      at
      oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:87)
      ... 4 more

        Activity

        Hide
        vr143562 added a comment -

        Created an attachment (id=764)
        unzip the test case. To use the test, define
        a environment variable S1AS_HOME that points to glassfish installation dir. Run
        the test by invoking "ant ee-java2db" or "ant se-java2db" from the command-line.

        Show
        vr143562 added a comment - Created an attachment (id=764) unzip the test case. To use the test, define a environment variable S1AS_HOME that points to glassfish installation dir. Run the test by invoking "ant ee-java2db" or "ant se-java2db" from the command-line.
        Hide
        vr143562 added a comment -
        • Apologies for attaching exception snapshot from build 33 instead of build 33e.
          The exception though is the same for build 33e.
        • Additional Notes on how to use the attached Test Case with Postgres:

        1. unzip the test case. define a environment variable S1AS_HOME that points to
        glassfish installation dir. Create domain - domain1 in glassfish
        2. Copy the Postgres driver to <glassfish-install-loc>/domains/domain/lib/ext.
        This is used as classpath by common.xml files's persistence-classpath-id. You
        can modify this location in common.xml to reflect location of postgres driver.
        3. Update <host> entry in the postgres.properties file.
        4. Run the test by invoking "ant ee-java2db" for EE mode and "ant se-java2db"
        for SE mode from the command-line.

        Show
        vr143562 added a comment - Apologies for attaching exception snapshot from build 33 instead of build 33e. The exception though is the same for build 33e. Additional Notes on how to use the attached Test Case with Postgres: 1. unzip the test case. define a environment variable S1AS_HOME that points to glassfish installation dir. Create domain - domain1 in glassfish 2. Copy the Postgres driver to <glassfish-install-loc>/domains/domain/lib/ext. This is used as classpath by common.xml files's persistence-classpath-id. You can modify this location in common.xml to reflect location of postgres driver. 3. Update <host> entry in the postgres.properties file. 4. Run the test by invoking "ant ee-java2db" for EE mode and "ant se-java2db" for SE mode from the command-line.
        Hide
        vr143562 added a comment -

        Similar problem with strategy IDENTITY for DB2.
        With DB2 strategy SEQUENCE is treated as IDENTITY and for Postgres IDENTITY is
        treated as SEQUENCE. So the issue appears with DB2 and Postgres with both the
        strategies.

        Should a separate bug be filed for DB2?

            • Exception Snapshot for DB2 ***
              javax.persistence.RollbackException: Exception [TOPLINK-4002] (Oracle TopLink
              Essentials - 9.1 (Build b33e-beta)):
              oracle.toplink.essentials.exceptions.DatabaseException
              Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC
              Driver][DB2]UNACCEPTABLE SQL STATEMENTError Code: -84
              Call:VALUES IDENTITY_VAL_LOCAL()
              Query:ValueReadQuery()
              at
              oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:105)
              at
              oracle.toplink.essentials.internal.ejb.cmp3.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:45)
              at
              tests.TestEntityUsingSequence.persistEntity(TestEntityUsingSequence.java:34)
              at tests.TestEntityUsingSequence.test(TestEntityUsingSequence.java:21)
              at tests.TestsRunner.main(TestsRunner.java:13)
              Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build
              b33e-beta)): oracle.toplink.essentials.exceptions.DatabaseException
              Internal Exception: java.sql.SQLException: [sunm][DB2 JDBC
              Driver][DB2]UNACCEPTABLE SQL STATEMENTError Code: -84
              Call:VALUES IDENTITY_VAL_LOCAL()
              Query:ValueReadQuery()
              at
              oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303)
              at
              oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551)
              at
              oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437)
              at
              oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:675)
        Show
        vr143562 added a comment - Similar problem with strategy IDENTITY for DB2. With DB2 strategy SEQUENCE is treated as IDENTITY and for Postgres IDENTITY is treated as SEQUENCE. So the issue appears with DB2 and Postgres with both the strategies. Should a separate bug be filed for DB2? Exception Snapshot for DB2 *** javax.persistence.RollbackException: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33e-beta)): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: java.sql.SQLException: [sunm] [DB2 JDBC Driver] [DB2] UNACCEPTABLE SQL STATEMENTError Code: -84 Call:VALUES IDENTITY_VAL_LOCAL() Query:ValueReadQuery() at oracle.toplink.essentials.internal.ejb.cmp3.transaction.base.EntityTransactionImpl.commit(EntityTransactionImpl.java:105) at oracle.toplink.essentials.internal.ejb.cmp3.transaction.EntityTransactionImpl.commit(EntityTransactionImpl.java:45) at tests.TestEntityUsingSequence.persistEntity(TestEntityUsingSequence.java:34) at tests.TestEntityUsingSequence.test(TestEntityUsingSequence.java:21) at tests.TestsRunner.main(TestsRunner.java:13) Caused by: Exception [TOPLINK-4002] (Oracle TopLink Essentials - 9.1 (Build b33e-beta)): oracle.toplink.essentials.exceptions.DatabaseException Internal Exception: java.sql.SQLException: [sunm] [DB2 JDBC Driver] [DB2] UNACCEPTABLE SQL STATEMENTError Code: -84 Call:VALUES IDENTITY_VAL_LOCAL() Query:ValueReadQuery() at oracle.toplink.essentials.exceptions.DatabaseException.sqlException(DatabaseException.java:303) at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.basicExecuteCall(DatabaseAccessor.java:551) at oracle.toplink.essentials.internal.databaseaccess.DatabaseAccessor.executeCall(DatabaseAccessor.java:437) at oracle.toplink.essentials.internal.sessions.AbstractSession.executeCall(AbstractSession.java:675)
        Hide
        marina vatkina added a comment -

        Can you please check if it's a duplicate of
        https://glassfish.dev.java.net/issues/show_bug.cgi?id=283? Please check very
        carefully, as there is a lot of information there .

        thanks,
        -marina

        Show
        marina vatkina added a comment - Can you please check if it's a duplicate of https://glassfish.dev.java.net/issues/show_bug.cgi?id=283? Please check very carefully, as there is a lot of information there . thanks, -marina
        Hide
        mf125085 added a comment -

        This Postgres behavior should be visibly documented, and not only as comment in
        issue 283.

        Show
        mf125085 added a comment - This Postgres behavior should be visibly documented, and not only as comment in issue 283.
        Hide
        vr143562 added a comment -

        This is a duplicate of issue 283. The suggestion (workaround) described in the
        first entry of the bug worked with the attached test case for this issue. You
        can close this as a duplicate.

        thanks.

        --varun.

        Show
        vr143562 added a comment - This is a duplicate of issue 283. The suggestion (workaround) described in the first entry of the bug worked with the attached test case for this issue. You can close this as a duplicate. thanks. --varun.
        Hide
        marina vatkina added a comment -

        closing as a duplicate

            • This issue has been marked as a duplicate of 283 ***
        Show
        marina vatkina added a comment - closing as a duplicate This issue has been marked as a duplicate of 283 ***

          People

          • Assignee:
            tware
            Reporter:
            vr143562
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: