Re: SERIAL error

Hi,

On Sat, Nov 27, 2004 at 10:45:54PM -0600, mstory(at)uchicago(dot)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