Re: Incorrect DEFERRABLE usage with PostgreSQL

[Craig L Russell]

Hi Iwata-san,




Thanks!

Craig

On Oct 31, 2007, at 11:08 AM, Ryosuke IWATA (DM) wrote:

Can you please file a jira on this subject at http:// issues.apache.org/jira/browse/OPENJPA

Hello,






        http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html


        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);





Best regards,
- Ryosuke Iwata
  blanche conseiller d'essence, inc.

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 fell 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: As a result of this confusion, SQL statements which OpenJPA generates result in database errors saying : I have to add the following line in the persistence.xml to avoid this problem: <property name="openjpa.jdbc.DBDictionary" value="postgres (SupportsDeferredConstraints=false)"/>

Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:[EMAIL PROTECTED]
P.S. A good JDO? O, Gasp!

smime.p7s
Description: S/MIME cryptographic signature