cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Arturo Pérez <ape...@hayesinc.com>
Subject Re: interactions with PostgreSQL sequences
Date Thu, 07 Sep 2006 11:32:41 GMT
In article <56C61237-7714-4E28-8675-C9861AFD83EF@objectstyle.org>,
 Andrus Adamchik <andrus@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
> >
> >


Mime
View raw message