OpenJPA
  1. OpenJPA
  2. OPENJPA-426

Incorrect DEFERRABLE usage with PostgreSQL

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Duplicate
    • Affects Version/s: 1.0.0
    • Fix Version/s: 1.1.0
    • Component/s: sql
    • Labels:
      None
    • Environment:
      PostgreSQL database 8.1.

      Description

      OpenJPA generates incorrect CREATE TABLE statements with PostgreSQL, and you will get syntax errors.

      OpenJPA adds "DEFERRABLE" to unique constraints, probably because PostgresDictionary sets "true" as "supportsDeferredConstraints", while PostgreSQL accepts "DEFERRABLE" only with foreign key constraints.

      The description of "CREATE TABLE" in the PostgreSQL documents is so confusing : the syntax description at the top of the page makes us feel that "DEFERRABLE" can be specified with any table constraints, but you can find that it says "Only foreign key constraints currently accept this clause" in the middle of the page. See:
      http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html

      As a result of this confusion, SQL statements which OpenJPA generates result in database errors saying :
      syntax error at or near "DEFERRABLE"

      An example of generated SQLs is :
      CREATE TABLE PUBLIC.FOO (entityId BIGINT NOT NULL,
      memo TEXT, name VARCHAR(255) NOT NULL,
      entityVersion INTEGER NOT NULL,
      PRIMARY KEY (entityId), UNIQUE (name) DEFERRABLE);

      As a workaround, you can add the following line in the persistence.xml to avoid this problem:
      <property name="openjpa.jdbc.DBDictionary" value="postgres(SupportsDeferredConstraints=false)"/>

        Issue Links

          Activity

          Ryosuke Iwata created issue -
          Hide
          Ryosuke Iwata added a comment -

          I'm sorry that I mistook affected versions.
          And I also find an existing and resolved issue in jira.
          I'm sorry again.

          Show
          Ryosuke Iwata added a comment - I'm sorry that I mistook affected versions. And I also find an existing and resolved issue in jira. I'm sorry again.
          Ryosuke Iwata made changes -
          Field Original Value New Value
          Affects Version/s 1.1.0 [ 12312344 ]
          Hide
          Ryosuke Iwata added a comment -

          I'm very sorry that this issue has been already reported and fixed. I confirmed this is fixed in a nightly build of 1.1.0.
          https://issues.apache.org/jira/browse/OPENJPA-282

          Show
          Ryosuke Iwata added a comment - I'm very sorry that this issue has been already reported and fixed. I confirmed this is fixed in a nightly build of 1.1.0. https://issues.apache.org/jira/browse/OPENJPA-282
          Ryosuke Iwata made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Duplicate [ 3 ]
          Fix Version/s 1.1.0 [ 12312344 ]
          Craig L Russell made changes -
          Link This issue duplicates OPENJPA-282 [ OPENJPA-282 ]
          Donald Woods made changes -
          Status Resolved [ 5 ] Closed [ 6 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Ryosuke Iwata
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development