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