Re: Problems with sequences

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.