Re: SERIAL error - Mailing list pgsql-general

From Joseph Healy
Subject Re: SERIAL error
Date
Msg-id 20041128042245.GA3746@joes
Whole thread Raw
In response to SERIAL error  (mstory@uchicago.edu)
List pgsql-general
Hi,

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 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?

This is by design.  When using a serial datatype, the default value of
the column is set as nextval('sequence_name'). Calls to nextval are
never rolled back.

Each value given out by the nextval function is only ever returned once
(unless it is reset with setval).

See http://www.postgresql.org/docs/7.4/static/functions-sequence.html

> Seems to cause problems for
> certain generic uses for the serial type.

These uses probably need to make use of nextval() and currval() as
appropriate.

Hope this helps,

Joe

pgsql-general by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: AT TIME ZONE: "convert"?
Next
From: mstory@uchicago.edu
Date:
Subject: Re: SERIAL error