You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Emanuele Maiarelli <em...@mengozzi.com> on 2009/10/29 00:55:56 UTC
Postgress autogenerated keys.
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"/>
Re: Postgress autogenerated keys.
Posted by Michael Gentry <mg...@masslight.net>.
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
<em...@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"/>
>
>
>
>
>
>
>
Re: Postgress autogenerated keys.
Posted by Reid Thompson <re...@ateb.com>.
Emanuele Maiarelli 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"/>
>
>
>
>
>
>
let the db manage it, that's what it's designed for