It would probably be easier for you to make your primary key a bigint
that is not null and do not specify a nextval/etc on it. Just a plain
old bigint with no autoincrement and a unique index. Then have a
sequence for Cayenne to call to populate and cache primary keys. This
is more in the flow of how Cayenne works and will be simpler for you (I
think).
If you already have a sequence created, select the DbEntities in Cayenne
Modeler and under the Entity tab, choose "Custom Sequence" for the PK
Generation Strategy. There you can define the name and the cache size
(increment_by column of the sequence). Otherwise you can use the
Default strategy and have the modeler generate the sequences for you
(Tools -> Generate Database Schema and then on the options window,
select only the Create Primary Key Support option).
Another interesting benefit of the custom sequence strategy is you can
up the cache size for records you expect will have a high insert rate to
boost DB performance. For example, you might have an AuditLog that gets
lots and lots of inserts as users perform actions in the application.
You could set the cache size to 1000 for that entity, while leaving the
others at 20 (or whatever value you choose). Just be sure to update the
increment_by column in your sequence to reflect this.
/dev/mrg
-----Original Message-----
From: Lindsay Steele [mailto:lsteel..inet.net.au]
Sent: Friday, April 21, 2006 3:55 AM
To: cayenne-use..ncubator.apache.org
Subject: Re: PK problem with Postgre
Andrus Adamchik wrote:
>
> create sequence foo_bar_seq;
> create table foo (bar integer unique not null default
> nextval('foo_bar_seq'));
>
Yes, I am using this way.
> (Or is this something else?) The example above does not work with
> Cayenne because Postgres driver as of 8.0 does not implement
> Statement.getGeneratedKeys() thus making it impossible (ok, rather
> very hard) for Cayenne to retrieve the key generated in such way. The
> solution - do not tie a PK column to a sequence in Postgres, let
> Cayenne generate the key.
Maybe that explains things. I migrated from mysql so was hoping - or
expecting it would work the same.
> I guess we may implement an equivalent of
> 'Statement.getGeneratedKeys()' on Cayenne end by using Postgres
> proprietary API to retrieve generated keys (and thus enable scenario
> (1)), still I hoped the driver would provide that.
>
> Andrus
>
Ok, I understand that you need to know the generated key once a record
is entered - which makes sense.
For now .. I did some more testing and will stick with my previous
strategy.
This archive was generated by hypermail 2.0.0 : Fri Apr 21 2006 - 09:25:39 EDT