Thread: serial increments on failed insert

I've noticed what seems to be an odd effect in psql 7.3.  It works like
this:

1> Create a table:
CREATE TABLE foo
(
  sval serial,
  uval int UNIQUE
);

2> Run 3 inserts, the second of which fails because it fails the
   unique constraint:
INSERT INTO foo VALUES (DEFAULT,1);
INSERT INTO foo VALUES (DEFAULT,1);   <--- This fails
INSERT INTO foo VALUES (DEFAULT,2);

3> look at the table:
SELECT * FROM foo;
sval | uval
------+------
   1 |    1
   3 |    2   <--- look here
(2 rows)

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?

Thanks,
Dave

David Kammer <djkammer@u.washington.edu> writes:
> 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,

No.  nextval() calls never roll back; see the documentation.

> is there a good work around?

Don't assume that a serial column is without gaps.  It's only intended
to be unique.

            regards, tom lane

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

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/

On Fri, Jan 14, 2005 at 17:49:42 -0800,
  Steve Atkins <steve@blighty.com> wrote:
>
> 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).

Note that the increasing part is only guarenteed within a session,
not accross sessions and only if you haven't changed the sequence
to allow it to roll over.