Incorrect DEFERRABLE usage with PostgreSQL
------------------------------------------
Key: OPENJPA-426
URL: https://issues.apache.org/jira/browse/OPENJPA-426
Project: OpenJPA
Issue Type: Bug
Components: sql
Affects Versions: 1.0.0, 1.1.0
Environment: PostgreSQL database 8.1.
Reporter: Ryosuke Iwata
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)"/>
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
