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);
I've learned that the first thing is only good for the current session
and I've no idea why the second and third aren't working.
Mostly what I'm hoping for is some debugging tips. I tried setting
log_statement = 'all' but that doesn't show the parameters to prepared
statements nor any access to the sequence.
Does anyone have any experience helping me to pinpoint the cause of
this? Tomcat JDBC pooling? Cayenne caching?
tia
arturo