serial increments on failed insert

[David Kammer]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[David Kammer]

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [email]majordomo@postgresql.org[/email]

[Steve Atkins]

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [email]majordomo@postgresql.org[/email] so that your
message can get through to the mailing list cleanly

[Michael Fuhr]

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
[url]http://www.fuhr.org/~mfuhr/[/url]

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruno Wolff III]

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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[David Fetter]

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 [email]david@fetter.org[/email] [url]http://fetter.org/[/url]
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

[url]http://www.postgresql.org/docs/faqs/FAQ.html[/url]

[rp71]

I had the same problem on one of my tables. What I did was change the column to an integer, create a sequence, create a trigger function, and set a before insert trigger:

<modify the table so the column is an integer>

CREATE SEQUENCE tblloc_rowid;

CREATE OR REPLACE FUNCTION process_tblloc() RETURNS TRIGGER AS $process_tblloc$
BEGIN
IF (TG_OP = 'INSERT') THEN
NEW.rowid = nextval('tblloc_rowid'::regclass);
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$process_tblloc$ LANGUAGE plpgsql;

CREATE TRIGGER tblloc_insert_rowid BEFORE INSERT ON tblloc FOR EACH ROW EXECUTE PROCEDURE process_tblloc();

This only gets the next sequence when a row is being inserted (it isn't called on a failed insert), keeping the sequence from skipping numbers.

Anyway - works for me, maybe it'll work for you too.

Regards,
Ron