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

["Roger Keays (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12585428#action_12585428 ] 

Roger Keays commented on OPENJPA-557:
-------------------------------------

Here is my patch for OpenJPA 1.0.2. You might prefer to move the code to the superclass though.

Index: openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java
===================================================================
--- openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java      (revision 641780)
+++ openjpa-jdbc/src/main/java/org/apache/openjpa/jdbc/sql/PostgresDictionary.java      (working copy)
@@ -149,6 +149,21 @@
             "STORE", "VACUUM", "VERBOSE", "VERSION",
         }));
     }
+    
+    /**
+     * Prepend schema names to sequence names if there is one. This
+     * method does not escape reserved words in the schema name or
+     * sequence name.
+     */
+    protected String getGeneratedKeySequenceName(Column col) {
+        String sequence = super.getGeneratedKeySequenceName(col);
+        String schema = col.getSchemaName();
+        if (schema != null && schema.length() > 0) {
+            return schema + "." + sequence;
+        } else {
+            return sequence;
+        }
+    }
 
     public Date getDate(ResultSet rs, int column)
         throws SQLException { 

This patch has been fine in production for me over the last week.

Adam Hardy also adds:

You might want to make that schema.toLowerCase()

Postgres diverges from the JDBC spec by making everything lower case and it
won't find an upper case schema. 

> 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
>
> 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.