openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Ryosuke IWATA \(DM\)" <dm-r.iw...@blanche-conseiller.co.jp>
Subject RE: Incorrect IDENTITY sequence name in PostgreSQL
Date Wed, 31 Oct 2007 17:22:04 GMT
Hello,

Reading the source codes of DBDictionary and PostgresDictionary, I suspect that PostgresDictionary
have to override "maxAutoAssignNameLength" to fix the problem I reported previously (attached
at the bottom of this e-mail).

I guess that OpenJPA obtains the IDENTITY sequence name by calling DBDictionary#getGeneratedKeySequenceName().
Is it right?

If yes, PostgresDictionary should set "63" as "maxAutoAssignNameLength", because the method
uses it as the maximum length of IDENTITY sequence names.

I hope that I don't misunderstand. And, I also hope that I can use IDENTITY primary key generation
with entities with (not so) long names as long as possible.


Best regards,
- Ryosuke Iwata
  blanche conseiller d'essence, inc.


> -----Original Message-----
> From: Ryosuke IWATA (DM) [mailto:dm-r.iwata@blanche-conseiller.co.jp] 
> Sent: Thursday, October 25, 2007 12:21 PM
> To: users@openjpa.apache.org
> Subject: RE: Incorrect IDENTITY sequence name in PostgreSQL
> 
> Hi Mike,
> 
> Thank you for the reply.
> 
> I was using a nightly build snapshot of 2007-10-15. And I 
> already had a log entry that you mentioned, probably because 
> there was an entry "<property 
> name="openjpa.jdbc.DBDictionary" 
> value="postgres(SupportsDeferredConstraints=false)"/>
> "  in my persistence.xml.
> 
> I downloaded a new build yesterday, and I replaced the entry 
> with what you suggested : <property 
> name="openjpa.jdbc.DBDictionary" 
> value="org.apache.openjpa.jdbc.sql.PostgresDictionary(Supports
> DeferredConstraints=false)"/>
> 
> But, I still have the same problem. I attached a part of the 
> stacktrace.
> 
> Can I change the max constraint length by adding some entries 
> in persistence.xml?
> 
> 
> I guess that PostgresDictionary is not enough stable now. I 
> will show you problems I encountered recently (Some of them 
> may be already fixed).
> 
> I have to set "SupportsDeferredConstraints=false".
> PostgreSQL allows "DEFERRED" only in FOREIGN KEY constraints.
> But, without "SupportsDeferredConstraints=false", DDL SQLs 
> generated by OpenJPA have "DEFERRED" with constraints other 
> than FOREIGN KEY, and PostgreSQL does not accept the DDL.
> 
> I also have to set "<property name="openjpa.jdbc.Schemas" 
> value="public"/>", because OpenJPA seems to ignore 'schema' 
> values in @Table annotations.
> 
> 
> Best regards,
> - Ryosuke Iwata
>   blanche conseiller d'essence, inc.
> 
> 
> > -----Original Message-----
> > From: michael.d.dick@gmail.com
> > [mailto:michael.d.dick@gmail.com] On Behalf Of Michael Dick
> > Sent: Wednesday, October 24, 2007 2:12 PM
> > To: users@openjpa.apache.org
> > Subject: Re: Incorrect IDENTITY sequence name in PostgreSQL
> > 
> > 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.
> > >
> > >
> > 
> 


Mime
View raw message