Insert aborted, but Sequence increased

Hugo Jonker <hugo@gewis.win.tue.nl> writes:

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.

Then you didn't read the documentation. Sorry, this is not a bug.

regards, tom lane

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.

Well, nextval() doesn't hold a lock for the duration of the transaction,
so it still increments. It is a performance optimization.

---------------------------------------------------------------------------

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.

Detailed report:
----------------
Table projectlist looks like:

wwf-projects=# \d projectlist
Table "projectlist"
Column | Type | Modifiers
-------------+-----------------------+--------------------------------------------------------
id | integer | not null default nextval('"projectlist_id_seq"'::text)
dbms_host | character varying(30) | not null
dbms_port | numeric(5,0) | not null
dbms_type | character varying(10) | not null
db_name | character varying(20) | not null
admin_login | character varying(20) | not null
admin_pwd | character varying(20) | not null
Unique keys: projectlist_id_key

We have a SEQUENCE for id:

wwf-projects=# select currval('"projectlist_id_seq"'::text);
currval
---------
6
(1 row)

Now if we try to perform a faulty INSERT, the INSERT aborts. However,
the SEQUENCE is increased!:

wwf-projects=# insert into projectlist (dbms_host, dbms_port, dbms_type,
db_name, admin_login, admin_pwd) values ('webadmict.tue.nl','5432',
'hugotest-2', 'test', 'test');
ERROR: ExecAppend: Fail to add null value in not null attribute admin_pwd

wwf-projects=# select currval('"projectlist_id_seq"'::text);
currval
---------
7
(1 row)

So 'currval' increased due to an INSERT that was aborted.
(Yes, the insert is definately not in the table).

I'm not sure if one can call this a bug, but it is unexpected behaviour
-- to me, at least.

Regards,

Hugo Jonker.
PS: My apologies about not being on the list, but it was taking longer than
expected to get on.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026