cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <mgen...@masslight.net>
Subject Re: Postgress autogenerated keys.
Date Thu, 29 Oct 2009 14:24:21 GMT
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
<emanuele@mengozzi.com> 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"/>
>
>
>
>
>
>
>

Mime
View raw message