Hi Emanuele, I don't think you should be using BIGSERIAL with a sequence, too.
I believe that BIGSERIAL in PostgreSQL is similar to AUTO_INCREMENT in MySQL. The database will implicitly create a sequence for you and automatically run it on inserts. If you are going to stick with doc_pk being a BIGSERIAL, try setting in CayenneModeler the PK Generation Strategy to Database-Generated and then select doc_pk in the Auto Incremented pulldown. (This is all under the Entity tab for your documenti DbEntity.) However, if you want to use a sequence, which is potentially faster for INSERTs (Cayenne can cache PKs), try changing your doc_pk to be a BIGINT instead. Also, you'll want to re-create your sequence to give it more PKs at a time and make sure that size matches in CayenneModeler. (Right now your sequence is only incrementing by 1, so that's not very efficient.) Maybe create it something like: CREATE SEQUENCE documenti_doc_pk_seq INCREMENT 25 START 200; This would cache 25 primary keys at a time and start them at 200. (If you already have values > 200, choose a higher value.) Tell CayenneModeler to use 25 cached PKs for documenti_doc_pk_seq and everything should stay in sync. Cayenne will only query the sequence when it has exhausted 25 keys and needs new ones. mrg On Wed, Oct 28, 2009 at 7:55 PM, Emanuele Maiarelli <[email protected]> wrote: > I'm using cayenne 3 with postgresql 8.2.14, for instance i've a table > structured like that > > CREATE TABLE documenti > ( > doc_pk bigserial NOT NULL, > doc_desc character varying(255), > doc_mine character varying(255), > doc_prot_fk bigint, > doc_self_fk bigint, > doc_prot_nr bigint, > doc_folder_fk bigint, > doc_uid character varying(255), > doc_prot_anno bigint, > doc_unsubmitted boolean, > CONSTRAINT "DOCUMENTI_pkey" PRIMARY KEY (doc_pk), > CONSTRAINT "DOCUMENTI_DOC_PROT_FK_fkey" FOREIGN KEY (doc_prot_fk) > REFERENCES protocolli (prot_pk) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION, > CONSTRAINT documenti_doc_folder_fk_fkey FOREIGN KEY (doc_folder_fk) > REFERENCES folders (fold_pk) MATCH SIMPLE > ON UPDATE NO ACTION ON DELETE NO ACTION > ) > WITHOUT OIDS; > and a sequnce for generating Pks > > CREATE SEQUENCE documenti_doc_pk_seq > INCREMENT 1 > MINVALUE 1 > MAXVALUE 9223372036854775807 > START 20 > CACHE 1; > > My question is what's the best pratice to ensure correts Pks being generated > from sequence: > > actually entity is configured as follow > > <db-entity name="documenti" schema="public"> > <db-attribute name="doc_desc" type="VARCHAR" length="255"/> > <db-attribute name="doc_folder_fk" type="BIGINT" length="8"/> > <db-attribute name="doc_mine" type="VARCHAR" length="255"/> > <db-attribute name="doc_pk" type="BIGINT" isPrimaryKey="true" > isMandatory="true" length="8"/> > <db-attribute name="doc_prot_anno" type="BIGINT" length="8"/> > <db-attribute name="doc_prot_fk" type="BIGINT" length="8"/> > <db-attribute name="doc_prot_nr" type="BIGINT" length="8"/> > <db-attribute name="doc_self_fk" type="BIGINT" length="8"/> > <db-attribute name="doc_uid" type="VARCHAR" length="255"/> > <db-attribute name="doc_unsubmitted" type="BIT" length="1"/> > <db-key-generator> > <db-generator-type>ORACLE</db-generator-type> > <db-generator-name>documenti_doc_pk_seq</db-generator-name> > <db-key-cache-size>1</db-key-cache-size> > </db-key-generator> > </db-entity> > > but since i missed initially to specify > <db-key-cache-size>1</db-key-cache-size> i got duplicated keys problems. > > Specifing <db-key-cache-size>1</db-key-cache-size> seems like solve the > problem. Is this correct? > > > Or should i change postgresql to automatically create the pk i mean, > changing doc_pk like that "doc_pk bigserial NOT NULL DEFAULT > nextval('documenti_doc_pk_seq') > > and setting > <db-attribute name="doc_pk" type="BIGINT" isPrimaryKey="true" > isGenerated="true" isMandatory="true" length="8"/> > > > > > > >
