Re: Insert aborted, but Sequence increased

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(at)postgresql(dot)org)
>

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