Sequences do not obey transactions...

From: Ryan Kirkpatrick <pgsql(at)rkirkpat(dot)net>
To: pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org
Subject: Sequences do not obey transactions...
Date: 2000-06-21 02:36:53
Message-ID: Pine.LNX.4.10.10006202027300.10276-100000@excelsior.rkirkpat.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-sql


Either I am missing something or I found a bug in PostgreSQL.
Hopefully it is the former. :)
Simply, I am trying to use a sequence to generate unique id
numbers for a table. Now, a number of the fields in this table have 'check
constraints'. What happens, is if I attempt to insert a row into the table
that fails to meet the constraints and is rejected, the sequence is still
incremented. Therefore, for each failed insert, a hole results in my id
number sequence. While this is not fatal, it is very annoying.
I even tried wrapping a BEGIN / END around a failing insert and
the sequence still incremented. It appears that whenever the 'nextval'
function is called, no matter where, in a failing insert, inside an
aborted transaction, etc..., the changes it makes to the sequence are
permanent.
So is this supposed to be this way, or did I stumble across a bug?
If the former, would some one please explain why this is this way (and
possibly add it to the documenation). Thanks.

PS. The mailing list search engines on the pgsql web site are
broken. They either find nothing, no matter what search terms one enters,
or complain about not being able to find the needed tables (relations).

---------------------------------------------------------------------------
| "For to me to live is Christ, and to die is gain." |
| --- Philippians 1:21 (KJV) |
---------------------------------------------------------------------------
| Ryan Kirkpatrick | Boulder, Colorado | http://www.rkirkpat.net/ |
---------------------------------------------------------------------------

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Bantos 2000-06-21 03:12:38 Re: Sequences do not obey transactions...
Previous Message Carsten Huettl 2000-06-20 18:50:10 Postgres with php3

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Bantos 2000-06-21 03:12:38 Re: Sequences do not obey transactions...
Previous Message Christopher Sawtell 2000-06-20 23:37:34 Re: \copy...