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?

See the "Sequence Manipulation Functions" section in the "Functions
and Operators" chapter of the documentation. The page contains
the following note:

Important: To avoid blocking of concurrent transactions that obtain
numbers from the same sequence, a nextval operation is never rolled
back; that is, once a value has been fetched it is considered used,
even if the transaction that did the nextval later aborts. This
means that aborted transactions may leave unused "holes" in the
sequence of assigned values. setval operations are never rolled
back, either.

Sequences are for obtaining numbers guaranteed to be unique; other
assumptions about their behavior are probably unwarranted.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/