Mailing-List: contact users-help@openjpa.apache.org; run by ezmlm
Precedence: bulk
Reply-To: users@openjpa.apache.org
Received-SPF: pass (nike.apache.org: local policy)
From: "Ryosuke IWATA \(DM\)" <dm-r.iwata@blanche-conseiller.co.jp>
To: <users@openjpa.apache.org>
References: <00e901c815f9$a355d350$800101df@societe.blancheconseiller.co.jp>
 <72c1350f0710232211kc9b78cbva4f685f8fa95eba7@mail.gmail.com>
 <000501c816b6$11caee30$800101df@societe.blancheconseiller.co.jp>
Subject: RE: Incorrect IDENTITY sequence name in PostgreSQL
Date: Thu, 1 Nov 2007 02:22:04 +0900
Message-ID: <004001c81be2$8ee60fe0$800101df@societe.blancheconseiller.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcgV/H4BXml1veXVQYiEgI8cxptNRwAtwV0wAUtU1KA=
In-Reply-To: <000501c816b6$11caee30$800101df@societe.blancheconseiller.co.jp>

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]=20
> Sent: Thursday, October 25, 2007 12:21 PM
> To: users@openjpa.apache.org
> Subject: RE: Incorrect IDENTITY sequence name in PostgreSQL
>=20
> Hi Mike,
>=20
> Thank you for the reply.
>=20
> I was using a nightly build snapshot of 2007-10-15. And I=20
> already had a log entry that you mentioned, probably because=20
> there was an entry "<property=20
> name=3D"openjpa.jdbc.DBDictionary"=20
> value=3D"postgres(SupportsDeferredConstraints=3Dfalse)"/>
> "  in my persistence.xml.
>=20
> I downloaded a new build yesterday, and I replaced the entry=20
> with what you suggested : <property=20
> name=3D"openjpa.jdbc.DBDictionary"=20
> value=3D"org.apache.openjpa.jdbc.sql.PostgresDictionary(Supports
> DeferredConstraints=3Dfalse)"/>
>=20
> But, I still have the same problem. I attached a part of the=20
> stacktrace.
>=20
> Can I change the max constraint length by adding some entries=20
> in persistence.xml?
>=20
>=20
> I guess that PostgresDictionary is not enough stable now. I=20
> will show you problems I encountered recently (Some of them=20
> may be already fixed).
>=20
> I have to set "SupportsDeferredConstraints=3Dfalse".
> PostgreSQL allows "DEFERRED" only in FOREIGN KEY constraints.
> But, without "SupportsDeferredConstraints=3Dfalse", DDL SQLs=20
> generated by OpenJPA have "DEFERRED" with constraints other=20
> than FOREIGN KEY, and PostgreSQL does not accept the DDL.
>=20
> I also have to set "<property name=3D"openjpa.jdbc.Schemas"=20
> value=3D"public"/>", because OpenJPA seems to ignore 'schema'=20
> values in @Table annotations.
>=20
>=20
> Best regards,
> - Ryosuke Iwata
>   blanche conseiller d'essence, inc.
>=20
>=20
> > -----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
> >=20
> > Hi Ryosuke,
> >=20
> > Which version of OpenJPA are you using? Unless you're using a very=20
> > recent snapshot you should see something like this (unless you've=20
> > changed the logging level) :
> >=20
> > 12  test  INFO   [main] openjpa.jdbc.JDBC - Using dictionary class "
> > org.apache.openjpa.jdbc.sql.DerbyDictionary" (Apache Derby=20
> 10.2.2.0 -
> > (485682) ,Apache Derby Embedded JDBC Driver 10.2.2.0 - (485682)).
> >=20
> > In your case it should say . . . Using dictionary class "
> > org.apache.openjpa.jdbc.sql.PostgresDictionary" . . .
> >=20
> > I'm guessing that it doesn't. The PostgresDictionary sets the max=20
> > constraint length to 63, the default value for "generic"
> > databases is 18.
> >=20
> > If you're getting something different then there's a bug in=20
> the code=20
> > that detects which database to use. You can probably work=20
> around it by=20
> > adding this property to your persistence.xml file :
> > <property name=3D"openjpa.jdbc.DBDictionary" value=3D"
> > org.apache.openjpa.jdbc.sql.PostgresDictionary.java"/>
> >=20
> > Hope this helps,
> > -Mike
> >=20
> > 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=20
> IDENTITY=20
> > > 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=3D"X_SCHOOL_TRANSFER_EVENT")
> > >         public class SchoolTransferEvent {
> > >                 ...
> > >                 @Id
> > >                 @GeneratedValue(strategy =3D=20
> 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=20
> above, te=20
> > > sequence for the column ENTITYID is named :
> > >         X_SCHOOL_TRANSFER_EVENT_ENTITY_ID_SEQ
> > >
> > >
> > > When you EntityManager#persist() a new instance of=20
> > > 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=3D0, state=3D42P01]
> > >
> > > 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=20
> > > longer than 18 characters. OpenJPA ommits table names, while=20
> > > 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=20
> > > generation now.
> > >
> > > Does anyone know workarouds for this problem? We need IDENTITY=20
> > > generation to make it easier to apply OpenJPA with an
> > existing database.
> > >
> > >
> > > Best regards,
> > > - Ryosuke Iwata
> > >   blanche conseiller d'essence, inc.
> > >
> > >
> >=20
>=20