You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Arturo P�rez <ap...@hayesinc.com> on 2006/09/07 00:46:53 UTC
interactions with PostgreSQL sequences
Hi all,
Using cayenne 1.2, Tomcat 5.0 and pgSQL 8.1.4 on Linux.
I'm having a problem where sequence numbers are being reused
as primary keys. I have everything in Cayenne set to defaults except
that I'm using the jdbc resource defined in the web.xml in order to
not have to set usernames and passwords for the various sites (desktop,
Q/A, production).
What I see is:
1. Getting the nextval from the sequence yields 45, for example.
2. The highest id is 59.
I'm guessing that Cayenne gets a 48 in the above situation, tries to
insert with a primary key of that value and fails with a duplicate.
I'm looking for ideas to debug, ideas on how likely it is that
Cayenne and the database have differing takes on what the next
primary key should be and things like that.
Help!
arturo
Re: interactions with PostgreSQL sequences
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Sep 7, 2006, at 3:32 PM, Arturo Pérez wrote:
> The
> sequence was created like:
>
> CREATE SEQUENCE pk_article INCREMENT 1 START 200
> ;
>
> so I need to redo that with a INCREMENT 20?
Ok, "INCREMENT 1" explains the mismatch. You can either redo the
sequences changing the increment, or edit "PK Generation Strategy"
for each entity, selecting "Custom Sequence" with "Cached PK Size" of
1 (which will work, but result in more trips to db on insert).
Andrus
Re: interactions with PostgreSQL sequences
Posted by Arturo P�rez <ap...@hayesinc.com>.
In article <56...@objectstyle.org>,
Andrus Adamchik <an...@objectstyle.org> wrote:
> > I'm looking for ideas to debug, ideas on how likely it is that
> > Cayenne and the database have differing takes on what the next
> > primary key should be and things like that.
>
> That's what I suspect too... Sequence increment must be the same in
> DB and in Cayenne model (note that in Cayenne increment is 20 by
> default, unless you change it per entity). Did you create sequences
> using the modeler, or by hand?
We used modeller to generate the initial schema then made some
customizations by hand. We were migrating the database from MySQL to
PostgreSQL which is why we did it this way.
We then tried to set the sequences to the new values but it doesn't
seem to have worked. After restarting the application I get this
morning.
iht=> select max(article_id) from article;
max
------
4992
(1 row)
iht=> select nextval('pk_article');
nextval
---------
4986
(1 row)
Cayenne does not appear to be getting numbers 20 at a time. The
sequence was created like:
CREATE SEQUENCE pk_article INCREMENT 1 START 200
;
so I need to redo that with a INCREMENT 20?
>
> In any event you can drop and recreate the sequences again. Since you
> already have some data, you will need to set the initial value. The
> syntax is described in this Jira issue (that we need to add to
> Cayenne eventually)
>
> http://issues.apache.org/cayenne/browse/CAY-401
>
> Andrus
>
>
>
> On Sep 7, 2006, at 2:46 AM, Arturo P�rez wrote:
>
> > Hi all,
> >
> > Using cayenne 1.2, Tomcat 5.0 and pgSQL 8.1.4 on Linux.
> >
> > I'm having a problem where sequence numbers are being reused
> > as primary keys. I have everything in Cayenne set to defaults except
> > that I'm using the jdbc resource defined in the web.xml in order to
> > not have to set usernames and passwords for the various sites
> > (desktop,
> > Q/A, production).
> >
> > What I see is:
> > 1. Getting the nextval from the sequence yields 45, for example.
> > 2. The highest id is 59.
> >
> > I'm guessing that Cayenne gets a 48 in the above situation, tries to
> > insert with a primary key of that value and fails with a duplicate.
> >
> > I'm looking for ideas to debug, ideas on how likely it is that
> > Cayenne and the database have differing takes on what the next
> > primary key should be and things like that.
> >
> > Help!
> > arturo
> >
> >
Re: interactions with PostgreSQL sequences
Posted by Andrus Adamchik <an...@objectstyle.org>.
> I'm looking for ideas to debug, ideas on how likely it is that
> Cayenne and the database have differing takes on what the next
> primary key should be and things like that.
That's what I suspect too... Sequence increment must be the same in
DB and in Cayenne model (note that in Cayenne increment is 20 by
default, unless you change it per entity). Did you create sequences
using the modeler, or by hand?
In any event you can drop and recreate the sequences again. Since you
already have some data, you will need to set the initial value. The
syntax is described in this Jira issue (that we need to add to
Cayenne eventually)
http://issues.apache.org/cayenne/browse/CAY-401
Andrus
On Sep 7, 2006, at 2:46 AM, Arturo Pérez wrote:
> Hi all,
>
> Using cayenne 1.2, Tomcat 5.0 and pgSQL 8.1.4 on Linux.
>
> I'm having a problem where sequence numbers are being reused
> as primary keys. I have everything in Cayenne set to defaults except
> that I'm using the jdbc resource defined in the web.xml in order to
> not have to set usernames and passwords for the various sites
> (desktop,
> Q/A, production).
>
> What I see is:
> 1. Getting the nextval from the sequence yields 45, for example.
> 2. The highest id is 59.
>
> I'm guessing that Cayenne gets a 48 in the above situation, tries to
> insert with a primary key of that value and fails with a duplicate.
>
> I'm looking for ideas to debug, ideas on how likely it is that
> Cayenne and the database have differing takes on what the next
> primary key should be and things like that.
>
> Help!
> arturo
>
>