Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (athena.apache.org: domain of michael.d.dick@gmail.com
 designates 209.85.146.177 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:sender:to:subject:in-reply-to:mime-version:content-type:references:x-google-sender-auth;
        b=iSk5m7dx4O0GZ9HBO2EZppvKuX/BVAbxGahP60a+ikZ7vOgPALOoRV6+cr0EO2b7CLRGaP/iVSh9PHZ75apa8St48icfAeFByxwjYacxNzhmm2Bo4muiwFNVXFOMOzVhPcKYSx6i0xvo1sVnviZdB318iysw4LXOcCe6b0VteJQ=
Message-ID: <72c1350f0710232211kc9b78cbva4f685f8fa95eba7@mail.gmail.com>
Date: Wed, 24 Oct 2007 00:11:56 -0500
From: "Michael Dick" <mikedd@apache.org>
Sender: michael.d.dick@gmail.com
To: users@openjpa.apache.org
Subject: Re: Incorrect IDENTITY sequence name in PostgreSQL
In-Reply-To: <00e901c815f9$a355d350$800101df@societe.blancheconseiller.co.jp>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_5585_20617709.1193202716387"
References: <00e901c815f9$a355d350$800101df@societe.blancheconseiller.co.jp>

------=_Part_5585_20617709.1193202716387
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Ryosuke,

Which version of OpenJPA are you using? Unless you're using a very recent
snapshot you should see something like this (unless you've changed the
logging level) :

12  test  INFO   [main] openjpa.jdbc.JDBC - Using dictionary class "
org.apache.openjpa.jdbc.sql.DerbyDictionary" (Apache Derby 10.2.2.0 -
(485682) ,Apache Derby Embedded JDBC Driver 10.2.2.0 - (485682)).

In your case it should say . . . Using dictionary class "
org.apache.openjpa.jdbc.sql.PostgresDictionary" . . .

I'm guessing that it doesn't. The PostgresDictionary sets the max constraint
length to 63, the default value for "generic" databases is 18.

If you're getting something different then there's a bug in the code that
detects which database to use. You can probably work around it by adding
this property to your persistence.xml file :
<property name="openjpa.jdbc.DBDictionary" value="
org.apache.openjpa.jdbc.sql.PostgresDictionary.java"/>

Hope this helps,
-Mike

On 10/23/07, Ryosuke IWATA (DM) <dm-r.iwata@blanche-conseiller.co.jp> wrote:
>
> Hello,
>
> I am having a problem that I cannot persist entities with IDENTITY primary
> key generation into PostgreSQL databases because OpenJPA does not recognize
> correct names of sequences for database IDENTITY columns.
>
> Let me describe details.
>
>
> Suppose that there is an entity like the following:
>         @Entity
>         @Table(name="X_SCHOOL_TRANSFER_EVENT")
>         public class SchoolTransferEvent {
>                 ...
>                 @Id
>                 @GeneratedValue(strategy = GenerationType.IDENTITY)
>                 private Long entityId;
>                 ...
>         }
>
> OpenJPA generates a table for this class in PostgreSQL 8.1.x like this:
>         CREATE TABLE X_SCHOOL_TRANSFER_EVENT
>         (ENTITYID BIGSERIAL ..., PRIMARY KEY(ENTITYID))
>
> PostgreSQL names sequences for the BIGSERIAL columns in the following
> manner:
>         "TABLE_NAME"_"COLUMN_NAME"_SEQ
> So, in the example above, te sequence for the column ENTITYID is named :
>         X_SCHOOL_TRANSFER_EVENT_ENTITY_ID_SEQ
>
>
> When you EntityManager#persist() a new instance of SchoolTransferEvent,
> you will get an exception as follows:
>         org.apache.openjpa.lib.jdbc.ReportingSQLException:
>         ERROR: relation "x_school_membershi_entityid_seq"
>         does not exist {prepstmnt 2398193 SELECT
>         CURRVAL(X_SCHOOL_TRANSFER_entityId_SEQ')}
>         [code=0, state=42P01]
>
> OpenJPA uses an incorrect name for the IDENTITY column sequence.
>         OK : X_SCHOOL_TRANSFER_EVENT_ENTITY_ID_SEQ
>         NG : 'X_SCHOOL_TRANSFER__ENTITYID_SEQ
>
>
> This problem occurs anytime when your database tables have names longer
> than 18 characters. OpenJPA ommits table names, while PostgreSQL uses
> non-omitted names. I will show another example.
>         Table : X_SCHOOL_MEMBERSHIP
>         OK : X_SCHOOL_MEMBERSHIP_ENTITYID_SEQ
>         NG : X_SCHOOL_MEMBERSHI_ENTITYID_SEQ
>
>
> With this problem, we have to abandon using IDENTITY sequence generation
> now.
>
> Does anyone know workarouds for this problem? We need IDENTITY generation
> to make it easier to apply OpenJPA with an existing database.
>
>
> Best regards,
> - Ryosuke Iwata
>   blanche conseiller d'essence, inc.
>
>

------=_Part_5585_20617709.1193202716387--