In article <1157581398.20424.32.camel@state.g2switchworks.com>,
smarlowe@g2switchworks.com (Scott Marlowe) wrote:
> On Wed, 2006-09-06 at 16:56, Arturo Perez wrote:
> > Hi all,
> >
> > My environment is Tomcat5.0, Cayenne 1.2 and PostgreSQL 8.1.4 on a
> > RedHat ES3 machine.
> >
> > My webapplication is reusing sequence numbers and getting duplicate
> > primary key
> > failures because of it (error is "duplicate key violates unique
> > constraint"). The
> > columns are not defined as SERIAL for historical reasons so it fetches
> > nextval and
> > uses that.
> >
> > The webapp stays connected for days at a time. It's only using a
> > handful (usually 2) connections.
> >
> > What happens is that if I do a select nextval('seq') I get a number
> > that's lower than the
> > max primary key id. This is inspite of my doing
> > SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> > ALTER SEQUENCE seq RESTART WITH <max + 1>;
> > select pg_catalog.setval(seq, <max+1>, true);
>
> When are you doing these statements? You shouldn't really need to set a
> sequence to a new number except right after a data load or something
> like that. definitely not when anyone else is using the db.
We (me!) just converted our app from MySQL to PostgreSQL. We wrote a
perl script to copy the data from the MySQL instance to the new
PostgreSQL instance. As part of that data copy we did the first thing
as that was recommended by a comment in the online manual for PostgreSQL.
Ever since then the problem described has been happening. The other two
statements were done in an attempt to correct the problem without
restarting the whole application (ie without bouncing tomcat).
I just had the bounce the app anyway (mgmt :-) so I'm hoping the problem
won't reoccur but I need steps to take if it does.
-arturo