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.