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@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