Thread: serial increments on failed insert

Ive 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

On Fri, Jan 14, 2005 at 04:48:12PM -0800, David Kammer wrote:
> Ive 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?

Perhaps the docs need revision to clarify this.  The SERIAL type is
meant to generate id's (in this case, integers) which are guaranteed
to be unique.  Because they have this uniqueness property, they are
*not* guaranteed to be gapless.

Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!