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:[EMAIL PROTECTED] Sent: Friday, April 21, 2006 3:55 AM To: [email protected] 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.
