Re: Serial not so unique? - Mailing list pgsql-general

Hi Stephen,

That's weird behaviour.  If you'd manually created the sequence like
this :

CREATE SEQUENCE foo_seq MINVALUE 4 MAXVALUE 101 CYCLE

Then referenced it as the default like this :

CREATE TABLE bar (idnum integer UNIQUE DEFAULT nextval('foo_seq') NOT
NULL, otherstuff varchar(20));

That would explain the wrapping around behaviour, but not when the field
is a SERIAL type.

:(

+ Justin Clift


Stephen Robert Norris wrote:
>
> On Sat, Aug 18, 2001 at 03:49:10PM -0700, Joe Conway wrote:
> > > > > Sometimes (about 20%, it seems) with several of the data sets, we
> > > > > get an error trying to insert rows into the table with the serial in
> > it.
> > > > > On investigation, it seems that the serial number has got to 101, then
> > > > > set itself back to 4, causing nextval to return 5, and there are
> > already
> > > > > entries from 1-101.
> > > > >
> > > > > Now, we use the serial as the primary key, and we never explicitly set
> > it.
> > > > >
> > > > > Has anyone seen anything like this? I can work around it by generating
> > > > > a serial number within the application, but that's not ideal.
> > > >
> > > > Odd problem. What do you get if you run:
> > > >     select * from name_of_this_troublesome_sequence;
> > > > particularly for increment_by, max_value, min_value, and is_cycled?
> > > >
> > > > -- Joe
> > >
> > > 1, 2^31 -1, 1, f
> > >
> > > Stephen
> >
> > Nothing stands out there. You might try to drop and recreate the sequence if
> > you haven't already. Or, a longshot, but . . . you might check the table
> > definition to be sure it's using the sequence that you think it is.
> >
> > -- Joe
>
> Recreating the sequence solves the problem, of course. So does setval(102).
> My problem is that it got into this state originally. The test case that
> demonstrates it sometimes takes about 1.5 hours to run, and I have only got
> about 24 hours left, so I may have to stop investigating and make the
> application generate the id instead.
>
>         Stephen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
     - Indira Gandhi