OpenJPA
  1. OpenJPA
  2. OPENJPA-904 Testcase failures when using the PostgreSQL, Oracle, and DB2 databases
  3. OPENJPA-903

org.apache.openjpa.persistence.exception.TestException hangs when run using PostgreSQL 8.3 database

    Details

    • Type: Sub-task Sub-task
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.0-M2
    • Fix Version/s: 2.0.0-M2
    • Component/s: jdbc
    • Labels:
      None
    • Environment:
      Windows 2003 Server, maven 2.0.9, Java 1.5.0.15, PostgreSQL 8.3.5
    • Patch Info:
      Patch Available

      Description

      Seems to be hanging in the testThrowsOptimisticException() testcase – see below:

      -------------------------------------------------------
      T E S T S
      -------------------------------------------------------
      Running org.apache.openjpa.persistence.exception.TestException
      31 test INFO [main] openjpa.Runtime - Starting OpenJPA 2.0.0-SNAPSHOT
      484 test INFO [main] openjpa.jdbc.JDBC - Using dictionary class "org.apache.openjpa.jdbc.sql.PostgresDictionary" (PostgreSQL 8.3.5 ,PostgreSQL Native Driver PostgreSQL 8.3 JDBC3 with SSL (build 603)).

      1. OPENJPA-903-TRUNK.patch
        3 kB
        Tim McConnell
      2. OPENJPA-903-12x.patch
        3 kB
        Tim McConnell
      3. OPENJPA-903.patch
        0.8 kB
        Tim McConnell

        Activity

        Hide
        Tim McConnell added a comment -

        Two patches: one for Trunk and one for the 1.2.x branch are attached to fix the problem with this testcase hanging on databases that do not support a locking timout.

        Show
        Tim McConnell added a comment - Two patches: one for Trunk and one for the 1.2.x branch are attached to fix the problem with this testcase hanging on databases that do not support a locking timout.
        Hide
        Tim McConnell added a comment -

        Ok, I finally see what's happening here. For this testcase to work across multiple databases, two things have to be configured correctly for each database. They are:

        1. A locking timeout has to be available and set for each database (or database server). For example, the default setting for Derby is derby.locks.waitTimeout=60 (i.e., 60 seconds), which is at the server level. For DB2, it can be set at a database level, but the default is LOCKTIMEOUT=-1, which disables it. Likewise for PostgreSQL the setting is at a server, not database, granularity, etc...

        2. The sql-error-state-codes.xml file has to be properly populated with the appropriate lock and optimistic key values that correlate the database-dependent SQL codes and states to an optimistic lock.

        I'll provide another patch tomorrow. Thanks

        Show
        Tim McConnell added a comment - Ok, I finally see what's happening here. For this testcase to work across multiple databases, two things have to be configured correctly for each database. They are: 1. A locking timeout has to be available and set for each database (or database server). For example, the default setting for Derby is derby.locks.waitTimeout=60 (i.e., 60 seconds), which is at the server level. For DB2, it can be set at a database level, but the default is LOCKTIMEOUT=-1, which disables it. Likewise for PostgreSQL the setting is at a server, not database, granularity, etc... 2. The sql-error-state-codes.xml file has to be properly populated with the appropriate lock and optimistic key values that correlate the database-dependent SQL codes and states to an optimistic lock. I'll provide another patch tomorrow. Thanks
        Hide
        Tim McConnell added a comment -

        Hi Albert, yes it does help. Thanks much for reviewing....

        Show
        Tim McConnell added a comment - Hi Albert, yes it does help. Thanks much for reviewing....
        Hide
        Albert Lee added a comment -

        Tim,

        From posting in http://archives.postgresql.org/pgsql-jdbc/2002-03/msg00170.php

        It looks like PostgreSQL does not support query timeout. If this is true, the PostgresDictionary needs to update the
        supportsQueryTimeout = false;
        in its constructor to reflect this behavior. (like the InformixDictionary)

        The test case is trying to validate the scenario that em2 should be blocked and time out on flush and hence the OptimisticLockException. Of course, replace the flush with commit will cause the commit to commit and therefore em2.flush will NOT block and the UPDATE statement with the same version update will result with the expected OptimisticLockException but that is not the intent of the test case.

        I ran TestException against DB2 on the trunk release and it works with a timeout and the expected OptLockEx..

        I suggest :
        1) to validate the Postgre's query timeout behavior first and update PostgresDictionary as needed.
        2) to figure out what databases are and are not working/hanging and correlated the queryTimeout support for each of the db.
        3) if the condition is the test case hangs due to no timeout on executing the UPDATE, then test case can be changed to conditionally run if the db support querytimeout by testing this dictionary capability.

        Hope this help.
        Albert Lee

        Show
        Albert Lee added a comment - Tim, From posting in http://archives.postgresql.org/pgsql-jdbc/2002-03/msg00170.php It looks like PostgreSQL does not support query timeout. If this is true, the PostgresDictionary needs to update the supportsQueryTimeout = false; in its constructor to reflect this behavior. (like the InformixDictionary) The test case is trying to validate the scenario that em2 should be blocked and time out on flush and hence the OptimisticLockException. Of course, replace the flush with commit will cause the commit to commit and therefore em2.flush will NOT block and the UPDATE statement with the same version update will result with the expected OptimisticLockException but that is not the intent of the test case. I ran TestException against DB2 on the trunk release and it works with a timeout and the expected OptLockEx.. I suggest : 1) to validate the Postgre's query timeout behavior first and update PostgresDictionary as needed. 2) to figure out what databases are and are not working/hanging and correlated the queryTimeout support for each of the db. 3) if the condition is the test case hangs due to no timeout on executing the UPDATE, then test case can be changed to conditionally run if the db support querytimeout by testing this dictionary capability. Hope this help. Albert Lee
        Hide
        Tim McConnell added a comment -

        Patch attached to commit the first transaction before flushing and then committing the second transaction. This will fix the testThrowsOptimisticException testcase and prevent hangs during the maven build. Now works on Derby, DB2, Oracle, and PostgreSQL databases.

        Show
        Tim McConnell added a comment - Patch attached to commit the first transaction before flushing and then committing the second transaction. This will fix the testThrowsOptimisticException testcase and prevent hangs during the maven build. Now works on Derby, DB2, Oracle, and PostgreSQL databases.
        Hide
        Milosz Tylenda added a comment -

        Tim, I remember it also hanging with some other databases, at least with Oracle (10g Express/Linux). Maybe DB2 also.

        It might be that it can be overcome by setting some database/driver property related to timeout or deadlock.

        Show
        Milosz Tylenda added a comment - Tim, I remember it also hanging with some other databases, at least with Oracle (10g Express/Linux). Maybe DB2 also. It might be that it can be overcome by setting some database/driver property related to timeout or deadlock.

          People

          • Assignee:
            Tim McConnell
            Reporter:
            Tim McConnell
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development