SERIAL error

mstory@uchicago.edu wrote:

I'm using v. 7.4.2, and am encountering a problem with serial types. On an
insert into a table that results in failure for any number of reasone (mostly
violation of contraints) I've found that the Sequence for the column of type
serial will still bump the value up once.

This is FAQ item number 4.15.4:

http://www.postgresql.org/docs/faqs/FAQ.html#4.15.4

HTH,

Mike Mascari

On Sat, Nov 27, 2004 at 10:45:54PM -0600, mstory@uchicago.edu wrote:

I'm using v. 7.4.2, and am encountering a problem with serial types. On an
insert into a table that results in failure for any number of reasone (mostly
violation of contraints) I've found that the Sequence for the column of type
serial will still bump the value up once.

This behavior is intentional. See the PostgreSQL documentation and
the FAQ:

http://www.postgresql.org/docs/7.4/static/functions-sequence.html
http://www.postgresql.org/docs/faqs/FAQ.html#4.15.4

Anyway, was wondering if this needed to be reported as a bug? Seems
to cause problems for certain generic uses for the serial type.

What do you mean by "generic uses"? Sequences are for obtaining
unique values; problems caused by assuming anything else are almost
certainly application bugs.

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

On Sat, 27 Nov 2004 mstory@uchicago.edu wrote:

I'm using v. 7.4.2, and am encountering a problem with serial types. On an
insert into a table that results in failure for any number of reasone (mostly
violation of contraints) I've found that the Sequence for the column of type
serial will still bump the value up once. This can be seen when entering the
next value with no problems, the value is 2 greater than the last value.
Noticed this when experimenting with a serial column with a unique constriant,
and then manually raising the serial value of a row to the next number due in
the sequence, the first attempt caused an error, but on a second attempt it
worked fine but the number was one higher than what i expected. It also occurs
for errors pertaining to other columns, when using a two column table, one
column with a not null constraint, and the other the serial column, entering in
a null value for the first column causes the sequence to fire, and then
following that with a valid entry you'll see the value for the serial column is
+2 of the last value. This is still the case in a transaction. Anyway, was
wondering if this needed to be reported as a bug? Seems to cause problems for
certain generic uses for the serial type.

This is the intended behavior. SERIALS are meant to give non-repeating
values but give no guarantees for sequentialness (mostly to avoid having
to wait to see if a previous transaction that touched the serial has
committed successfully).

My mistake, in the future i will consult the FAQ first, though i'm a bit
surprised i missed it in the documentation. Much thanks for the help. By
generic uses i guess i meant the uses one might have for creating a sequence as
an independant object in the database, and not the sequence in a table, and this
behavior surely would not frustrate that sort of use. Many thanks.

Matthew Story

Quoting Michael Fuhr <mike@fuhr.org>: