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

Re: Insert aborted, but Sequence increased

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
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:40:49
Message-ID: 200207091540.g69FenM25940@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2002-07-09 18:58:38
Subject: Bug #705: Invalid UNICODE character sequence found (0xc236)
Previous:From: Tom LaneDate: 2002-07-09 15:36:29
Subject: Re: segfault which isn't supposed to happen (including example code)

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