Re: Insert aborted, but Sequence increased

On Mon, 8 Jul 2002, Hugo Jonker wrote:

> Hi,
>
> While using Postgres, I encountered some unexpected behaviour.
>
> In short:
> ---------
> Upon doing a faulty INSERT which left a column with default value
> nextval('sequence') unspecified, the INSERT aborted due to an error.
> However, a call had been placed to nextval('sequence'), thus
> increasing the sequence, while this value never got used.
>
> I certainly didn't expect this.

This is the intended behavior.

From User's guide section 4.11 (in the 7.2.1 interactive docs):

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.