Skip site navigation (1) Skip section navigation (2)

Re: Insert aborted, but Sequence increased

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Hugo Jonker <hugo(at)gewis(dot)win(dot)tue(dot)nl>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Insert aborted, but Sequence increased
Date: 2002-07-09 15:24:45
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-bugs
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.

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-07-09 15:36:29
Subject: Re: segfault which isn't supposed to happen (including example code)
Previous:From: Tom LaneDate: 2002-07-09 15:21:34
Subject: Re: Insert aborted, but Sequence increased

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group