Re: Serial not so unique?

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