Re: SERIAL error

On Sat, 27 Nov 2004 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? 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).