Re: serial increments on failed insert

On Fri, Jan 14, 2005 at 04:57:19PM -0800, David Kammer wrote:

> Notice that even though the second insert failed, it still incremented
> the serial value. This seems counter intuative to the way that serial
> should work. Is this truly a bug, or is there a good work around?

That's correct, documented behaviour. A serial column is mostly just a
sequence in disguise. A sequence is guaranteed to give unique,
increasing values, but in many cases may miss a value (for several
reasons - in this case because once a sequence value is used, it's
used, even if the transaction it was used in is rolled back).

Do you really need that column to increase one at a time? Or just
to increase and be unique?

You could look at the maximum value in the column and use the maximum
value plus one (and be prepared to retry if there's an index on that
column to guarantee uniqueness).

Cheers,
Steve