Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.0
    • Fix Version/s: 2.2.2, 2.2.1.1, 2.3.0
    • Component/s: jdbc, jpa
    • Labels:
      None
    • Environment:
      OpenJPA-2.2.0
      Transaction-type JTA
      Postgres 9.1
      JBoss 6.1
    • Patch Info:
      Patch Available

      Description

      Sorry my bad english ..

      when annotated a entity with @SequenceGenerator and with allocationSize = 1 and initialValue = 1, the method DBDictionary.commonCreateAlterSequenceSQL is creating a alter sequence sql invalid for Postgres 9.1.
      Despite the documentation of postgres inform you that the other parameters are optional (http://www.postgresql.org/docs/9.1/static/sql-altersequence.html), an error occurs in executing the sql below.

      Eg: ALTER SEQUENCE schema_experimento.usuario_sq (no other attributes)

      Even the method NativeJDBCSeq.udpateSql being fault tolerant, the connection is marked for rollback and not allowing run the next val of sequence.

      Hêndi Marcos

        Issue Links

          Activity

          Hide
          Kevin Sutter added a comment - - edited

          Through another discussion on the OpenJPA mailing list (http://openjpa.208410.n2.nabble.com/Postgres-sequence-current-transaction-is-aborted-td7580299.html), we've come to the bottom of this issue. The creation of this "alter sequence.." statement stops prematurely when it's discovered that the sequence allocation size is not greater than 1. I think this conditional should be greater than 0 (instead of 1). Regardless, we have to be smarter with creating these "create/alter sequence.." statements so that they are complete before attempting to execute.

          Show
          Kevin Sutter added a comment - - edited Through another discussion on the OpenJPA mailing list ( http://openjpa.208410.n2.nabble.com/Postgres-sequence-current-transaction-is-aborted-td7580299.html ), we've come to the bottom of this issue. The creation of this "alter sequence.." statement stops prematurely when it's discovered that the sequence allocation size is not greater than 1. I think this conditional should be greater than 0 (instead of 1). Regardless, we have to be smarter with creating these "create/alter sequence.." statements so that they are complete before attempting to execute.
          Hide
          Kevin Sutter added a comment -

          Albert, I'm going to assign to you since this issue is in the area of the sequence updates you did via openjpa-1376 and openjpa-2069. Good luck!

          Show
          Kevin Sutter added a comment - Albert, I'm going to assign to you since this issue is in the area of the sequence updates you did via openjpa-1376 and openjpa-2069. Good luck!
          Hide
          Kevin Sutter added a comment -

          Since the title of this JIRA is generic enough, I'm also going to post the other issue that came about due to the discussion on the mailing list (http://openjpa.208410.n2.nabble.com/Postgres-sequence-current-transaction-is-aborted-td7580299.html).

          We have an issue with the permissions required to alter a database sequence with Postgres. Or, at least, that's the only database that seems to be reporting this error. With the changes introduced by OpenJPA-1376 and OpenJPA-2069, we are now blindly attempting to issue an "alter sequence..". This is not good for Postgres users since only the owner of the Sequence can alter it. So far, we must be lucking out with the other databases.

          A couple of things that need to be re-looked at for this Sequence update. Thanks.

          Show
          Kevin Sutter added a comment - Since the title of this JIRA is generic enough, I'm also going to post the other issue that came about due to the discussion on the mailing list ( http://openjpa.208410.n2.nabble.com/Postgres-sequence-current-transaction-is-aborted-td7580299.html ). We have an issue with the permissions required to alter a database sequence with Postgres. Or, at least, that's the only database that seems to be reporting this error. With the changes introduced by OpenJPA-1376 and OpenJPA-2069, we are now blindly attempting to issue an "alter sequence..". This is not good for Postgres users since only the owner of the Sequence can alter it. So far, we must be lucking out with the other databases. A couple of things that need to be re-looked at for this Sequence update. Thanks.
          Hide
          Scott Parkerson added a comment -

          Consider, also, this scenario, where OpenJPA is using PostgreSQL and the owner of sequence is not the same user of the client (but client does have the USAGE grant on the sequence to call nextval, etc.). Let i be the initial starting value of the database sequence.

          1. An OpenJPA application connects to the database, and inserts n records into the database where n > 1 and n < allocationSize. On the first insert, the ALTER SEQUENCE call is made fails silently (although PostgreSQL will log an error serverside, but since the exception is swallowed by the client, no one is the wiser – including the code that "thinks" it has the next allocationSize ids cached. The database, however, still thinks that next sequence number is i+1.

          2. If the application is stopped and restarted, the next insert will fail due to a duplicate key violation (because, since the first ALTER SEQUENCE failed, the database still thinks its current sequence is i+1, but OpenJPA allowed that record to be inserted in the first run.

          I'm not seeing the behavior where the ALTER SEQUENCE call marks the transaction for rollback, though. I think I'm in a JTA-managed transaction, too... so not sure about that. Looking at the code in NativeJDBCSeq.allocateInternal, it appears that a new connection is grabbed from the store; not sure that connection is under JTA.

          At minimum, I think that OpenJPA should detect the failure to ALTER SEQUENCE and not cache allocationSize values (perhaps logging to WARN level the first time this happens per session per sequence). Additionally, the original DBDictionary.commonCreateAlterSequenceSQL should always specify INCREMENT BY even if it is set to 1, as that breaks the "workaround" for users who wish emulate previous OpenJPA behavior using useNativeSequenceCache=False.

          Show
          Scott Parkerson added a comment - Consider, also, this scenario, where OpenJPA is using PostgreSQL and the owner of sequence is not the same user of the client (but client does have the USAGE grant on the sequence to call nextval, etc.). Let i be the initial starting value of the database sequence. 1. An OpenJPA application connects to the database, and inserts n records into the database where n > 1 and n < allocationSize. On the first insert, the ALTER SEQUENCE call is made fails silently (although PostgreSQL will log an error serverside, but since the exception is swallowed by the client, no one is the wiser – including the code that "thinks" it has the next allocationSize ids cached. The database, however, still thinks that next sequence number is i+1. 2. If the application is stopped and restarted, the next insert will fail due to a duplicate key violation (because, since the first ALTER SEQUENCE failed, the database still thinks its current sequence is i+1, but OpenJPA allowed that record to be inserted in the first run. I'm not seeing the behavior where the ALTER SEQUENCE call marks the transaction for rollback, though. I think I'm in a JTA-managed transaction, too... so not sure about that. Looking at the code in NativeJDBCSeq.allocateInternal, it appears that a new connection is grabbed from the store; not sure that connection is under JTA. At minimum, I think that OpenJPA should detect the failure to ALTER SEQUENCE and not cache allocationSize values (perhaps logging to WARN level the first time this happens per session per sequence). Additionally, the original DBDictionary.commonCreateAlterSequenceSQL should always specify INCREMENT BY even if it is set to 1, as that breaks the "workaround" for users who wish emulate previous OpenJPA behavior using useNativeSequenceCache=False.
          Hide
          Scott Parkerson added a comment -

          This patch works around the case where the ALTER SEQUENCE statement fails to execute (which will happen on PostgreSQL if the sequence is not owned by the current connection's user). A warning message is logged for each sequence indicating that the sequence was not modified and that sequence values are not cached.

          Show
          Scott Parkerson added a comment - This patch works around the case where the ALTER SEQUENCE statement fails to execute (which will happen on PostgreSQL if the sequence is not owned by the current connection's user). A warning message is logged for each sequence indicating that the sequence was not modified and that sequence values are not cached.
          Hide
          Albert Lee added a comment -

          I only commit the fix in trunk (2.3.0). If you need fixes in 2.2.x, you need to work with the release manager for inclusion. 2.2.x is a maintenance release for WebSphere, one can request a service call to the WebSphere product, if needed.

          Show
          Albert Lee added a comment - I only commit the fix in trunk (2.3.0). If you need fixes in 2.2.x, you need to work with the release manager for inclusion. 2.2.x is a maintenance release for WebSphere, one can request a service call to the WebSphere product, if needed.

            People

            • Assignee:
              Albert Lee
              Reporter:
              Hendi Marcos Ramos Silva
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - 4h
                4h
                Remaining:
                Remaining Estimate - 4h
                4h
                Logged:
                Time Spent - Not Specified
                Not Specified

                  Development