You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Roger Keays (JIRA)" <ji...@apache.org> on 2008/04/04 09:15:25 UTC
[jira] Created: (OPENJPA-557) Primary key sequences broken with
postgres schemas
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.
Re: [jira] Commented: (OPENJPA-557) Primary key sequences broken with postgres schemas
Posted by Miłosz Tylenda <mt...@o2.pl>.
Hi Catalina,
The issue for schema handling is OPENJPA-842. As you will read, this turned out to be a more general problem, not specific to PostgreSQL.
Greetings,
Milosz
> Hi Milosz,
> I have committed your patch for this issue for future releases of 2.0 and
> 1.3.x.
> If you do need the patch be applied to 1.0.x, please reopen this issue.
>
> Please open separate JIRA issue as you uncover more problem with schema
> handling in PostgresSQL
>
> Thanks.
>
> Catalina Wei
>
> On Sun, Dec 21, 2008 at 4:02 AM, Milosz Tylenda (JIRA) wrote:
>
> >
> > [
> > https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658381#action_12658381]
> >
> > Milosz Tylenda commented on OPENJPA-557:
> > ----------------------------------------
> >
> > Applying the patch and running the test suite reveals another problem with
> > schema handling in PostgreSQL. I will open a separate JIRA issue.
> >
> >
> > > 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.
> >
> >
>
Re: [jira] Commented: (OPENJPA-557) Primary key sequences broken with postgres schemas
Posted by catalina wei <ca...@gmail.com>.
Hi Milosz,
I have committed your patch for this issue for future releases of 2.0 and
1.3.x.
If you do need the patch be applied to 1.0.x, please reopen this issue.
Please open separate JIRA issue as you uncover more problem with schema
handling in PostgresSQL
Thanks.
Catalina Wei
On Sun, Dec 21, 2008 at 4:02 AM, Milosz Tylenda (JIRA) <ji...@apache.org>wrote:
>
> [
> https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658381#action_12658381]
>
> Milosz Tylenda commented on OPENJPA-557:
> ----------------------------------------
>
> Applying the patch and running the test suite reveals another problem with
> schema handling in PostgreSQL. I will open a separate JIRA issue.
>
>
> > 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.
>
>
[jira] Commented: (OPENJPA-557) Primary key sequences broken with
postgres schemas
Posted by "Milosz Tylenda (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12658381#action_12658381 ]
Milosz Tylenda commented on OPENJPA-557:
----------------------------------------
Applying the patch and running the test suite reveals another problem with schema handling in PostgreSQL. I will open a separate JIRA issue.
> 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.
[jira] Resolved: (OPENJPA-557) Primary key sequences broken with
postgres schemas
Posted by "Catalina Wei (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Catalina Wei resolved OPENJPA-557.
----------------------------------
Resolution: Fixed
Fix Version/s: 2.0.0
1.3.0
Patch provided by Milosz Tylenda has been checked in under svn trunk r729180 and branch 1.3.x r729181.
> 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
> Fix For: 1.3.0, 2.0.0
>
> 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.
[jira] Commented: (OPENJPA-557) Primary key sequences broken with
postgres schemas
Posted by "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.
[jira] Updated: (OPENJPA-557) Primary key sequences broken with
postgres schemas
Posted by "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.
[jira] Commented: (OPENJPA-557) Primary key sequences broken with
postgres schemas
Posted by "Michal Borowiecki (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-557?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12649089#action_12649089 ]
Michal Borowiecki commented on OPENJPA-557:
-------------------------------------------
I experienced this bug in geronimo 2.1.3
I checked out openjpa tag 1.0.3 sources, applied the patch, compiled and substituted the class file in openjpa-1.0.3.jar in geronimo 2.1.3 repository.
I confirm the patch works.
This issue is very important and I hope someone applies it to trunk soon :)
Thanks for the patch Roger!
> 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.