Return-Path: 
 <dev-return-16638-apmail-openjpa-dev-archive=openjpa.apache.org@openjpa.apache.org>
Delivered-To: apmail-openjpa-dev-archive@www.apache.org
Received: (qmail 89622 invoked from network); 9 Jun 2010 13:38:39 -0000
Received: from unknown (HELO mail.apache.org) (140.211.11.3)
  by 140.211.11.9 with SMTP; 9 Jun 2010 13:38:39 -0000
Received: (qmail 35882 invoked by uid 500); 9 Jun 2010 13:38:39 -0000
Delivered-To: apmail-openjpa-dev-archive@openjpa.apache.org
Received: (qmail 35856 invoked by uid 500); 9 Jun 2010 13:38:39 -0000
Mailing-List: contact dev-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:dev-help@openjpa.apache.org>
List-Unsubscribe: <mailto:dev-unsubscribe@openjpa.apache.org>
List-Post: <mailto:dev@openjpa.apache.org>
List-Id: <dev.openjpa.apache.org>
Reply-To: dev@openjpa.apache.org
Delivered-To: mailing list dev@openjpa.apache.org
Received: (qmail 35843 invoked by uid 99); 9 Jun 2010 13:38:39 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 Jun 2010 13:38:39 +0000
X-ASF-Spam-Status: No, hits=-2000.0 required=10.0
	tests=ALL_TRUSTED
X-Spam-Check-By: apache.org
Received: from [140.211.11.22] (HELO thor.apache.org) (140.211.11.22)
    by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 09 Jun 2010 13:38:37 +0000
Received: from thor (localhost [127.0.0.1])
	by thor.apache.org (8.13.8+Sun/8.13.8) with ESMTP id o59DcFQ5015727
	for <dev@openjpa.apache.org>; Wed, 9 Jun 2010 13:38:15 GMT
Message-ID: <4721442.2071276090695346.JavaMail.jira@thor>
Date: Wed, 9 Jun 2010 09:38:15 -0400 (EDT)
From: "Jeremy Bauer (JIRA)" <jira@apache.org>
To: dev@openjpa.apache.org
Subject: [jira] Created: (OPENJPA-1689) The mapping tool does not remove
 user created sequences on PostgreSQL
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
X-JIRA-FingerPrint: 30527f35849b9dde25b450d4833f0394
X-Virus-Checked: Checked by ClamAV on apache.org

The mapping tool does not remove user created sequences on PostgreSQL
---------------------------------------------------------------------

                 Key: OPENJPA-1689
                 URL: https://issues.apache.org/jira/browse/OPENJPA-1689
             Project: OpenJPA
          Issue Type: Bug
          Components: jdbc
    Affects Versions: 2.0.0
         Environment: PostgreSQL 8.4
            Reporter: Jeremy Bauer
            Priority: Minor


As reported on the users forum:

<Christopher Schmidt>
Hi all, using OpenJPA 2.0.0 with Postgresql 8.4 JDBC4

I want to create the schema with the following property:
<property name="openjpa.jdbc.SynchronizeMappings"
value="buildSchema(SchemaAction='drop,add')"/>

The entity is defined as follows:

@Entity
@Table(name = "obj_item")
@Inheritance(strategy = InheritanceType.JOINED)
@SequenceGenerator(name = "obj_item_id_seq", sequenceName =
"obj_item_id_seq", allocationSize = 1)
class ObjectItem ...

it seems that the sequence will not be dropped - so I get the
following exception:
org.apache.openjpa.persistence.PersistenceException: ERROR: relation
"obj_item_id_seq" already exists {stmnt 1834517285 CREATE SEQUENCE
obj_item_id_seq START WITH 1} [code=0, state=42P07]
       at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:556)...
</Christopher Schmidt>

<Jeremy Bauer>
As it turns out, I don't think this problem is the same as OPENJPA-1259.  There is special code in the PostgresDictionary that treats sequences suffixed with "_SEQ" as system managed sequences.  The comment in PostgresDictionary.isSystemSequence reads:

        // filter out generated sequences used for bigserial cols, which are
        // of the form <table>_<col>_seq

This explains why the same code worked for me on DB2.  Are you able to modify the sequence name?  (ex. sequenceName = "seq_obj_item_id")  If not, please file a new JIRA.   It may/should be possible to make the code a bit smarter by actually verifying _seq suffixed sequences are for a bigserial column[1] instead of just making the assumption.

[1] http://www.postgresql.org/docs/8.4/static/datatype-numeric.html#DATATYPE-SERIAL
</Jeremy Bauer>

I have a simple testcase that demonstrates the problem and only spent a very brief amount of time looking into a fix.  Ideally, OpenJPA should be able to identify user created vs. system created sequences with suffix "_seq" and behave appropriately.

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