Issue with sequence and transactions

From: Ian Meyer <misc(at)crewcial(dot)org>
To: PostgreSQL novice list <pgsql-novice(at)postgresql(dot)org>
Subject: Issue with sequence and transactions
Date: 2004-11-09 03:27:39
Message-ID: 4190392B.90507@crewcial.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hi,

This might just be me not completely understanding how sequences and
transactions work together, or it could be something else is wrong.
Either way I would like more information about this issue which I will
describe below.

I have a table called bco_users:

bco=> \d bco_users
Table "public.bco_users"
Column | Type |
Modifiers
--------------------+-------------------+----------------------------------------------------------------
user_id | integer | not null default
nextval('public.bco_users_user_id_seq'::text)
username | character varying |
password | character varying |
user_private_email | character varying |
Indexes:
"bco_users_pkey" primary key, btree (user_id)
"unique_private_email" unique, btree (user_private_email)
"unique_username" unique, btree (username)

Then I added a couple of rows, which is when I discovered this little
"mess".

bco=> insert into bco_users (username, password) values ('test', 'blank1');
INSERT 17183 1
bco=> select currval('bco_users_user_id_seq');
currval
---------
5
(1 row)

bco=> select * from bco_users;
user_id | username | password | user_private_email
---------+----------+----------+---------------------
1 | asdfff | blank | asdf
4 | asd | blank | asdf
5 | test | blank1 |
(3 rows)

bco=> BEGIN;
BEGIN
bco=> insert into bco_users (username, password) values ('test2', 'blank2');
INSERT 17184 1
bco=> ROLLBACK;
ROLLBACK
bco=> select currval('bco_users_user_id_seq');
currval
---------
6
(1 row)

Why does the sequence not get rolled back? I have looked in
documentation, read endlessly in a PostgreSQL book and can't figure out
if that is the expected behavior.. and if so, why?

What I want to happen (at least, the way I see it happening) is if
someone creates a username, but the query fails, or the username is
taken already, then the transaction is rolled back, and the id that
would have been taken, is still free.

Thanks in advance,
Ian

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2004-11-09 03:44:29 Re: Issue with sequence and transactions
Previous Message Michael Fuhr 2004-11-08 21:24:16 Re: user defined type