[jira] Updated: (OPENJPA-557) Primary key sequences broken with postgres schemas

["Milosz Tylenda (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Milosz Tylenda updated OPENJPA-557:
-----------------------------------

    Attachment: OPENJPA-557.patch

The patch attached fixes the problem as suggested by Mehmet in OPENJPA-582:

lastGeneratedKeyQuery = "SELECT CURRVAL(''{1}_{0}_seq'')";

This reflects well how PostgreSQL is creating implicit sequences for SERIAL (auto-increment) columns [1]. Lower casing is already handled in DBDictionary.convertSchemaCase(String) method.

I have updated the TestMultipleSchemaNames test case to make the issue visible. Previously the test case was failing in the beginning on PostgreSQL because of schema handling. Derby and DB2 create schema automatically in CREATE TABLE if schema does not exist. PostgreSQL requires explicit schema creation. I have added this to the test case. If we run the test case on Oracle or MySQL, the things are even worse because Oracle treats what we call schema a user name and MySQL treats it as database name.

I added two DogX classes and removed comments from some others as I found them misleading.

[1] http://www.postgresql.org/docs/8.3/interactive/datatype-numeric.html#DATATYPE-SERIAL

> Primary key sequences broken with postgres schemas
> --------------------------------------------------
>
>                 Key: OPENJPA-557
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-557
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 1.0.2
>            Reporter: Roger Keays
>         Attachments: OPENJPA-557.patch
>
>
> as per http://www.nabble.com/forum/ViewPost.jtp?post=15460899&framed=y
> OpenJPA issues a SELECT currval('user_id_seq'); query to get the current PK value on postgres. This should *not* execute correctly when using a schema. The correct query is SELECT currval('schemaname.user_id_seq');

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.