Обсуждение: Insert aborted, but Sequence increased
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.
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