Sequences change in a rolled-back transactions
Hi group,
I just noticed getting the next number of a sequence doesn't respect a
transaction.
Here is an example:
=======================================================
erwin=# SELECT nextval('tblofferlabel_offerlabelid_seq'::regclass) as
nextofferlabelid;
nextofferlabelid
------------------
87
(1 row)
erwin=# start transaction;
START TRANSACTION
erwin=# SELECT nextval('tblofferlabel_offerlabelid_seq'::regclass) as
nextofferlabelid;
nextofferlabelid
------------------
88
(1 row)
erwin=# SELECT nextval('tblofferlabel_offerlabelid_seq'::regclass) as
nextofferlabelid;
nextofferlabelid
------------------
89
(1 row)
# rollback;
ROLLBACK
erwin=# SELECT nextval('tblofferlabel_offerlabelid_seq'::regclass) as
nextofferlabelid;
nextofferlabelid
------------------
90
=======================================================
As you can see the sequence just counts on outside the transaction.
I thought a transaction that is rolled back, rolls back *everything*
done in that transaction.
Appearantly sequences are not included.
It is no big deal, since I can easily code this differently, but I was
unpleasantly surprised. :-/
Can anybody comment on this behaviour? Am I missing something?
Thanks!
Regards,
Erwin Moller
Erwin Moller wrote:
I thought a transaction that is rolled back, rolls back *everything*
done in that transaction.
Appearantly sequences are not included.
Yes. This is actually a desirable property, because it allows sequences
to work fine in concurrent scenarios (which are, after all, the whole
point of sequences).
If you actually need an incremental number with no gaps (which is rare
but not unseen), you need to use some other mechanism, which will
probably involve a lock to prevent concurrency.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera schreef:
Erwin Moller wrote:
I thought a transaction that is rolled back, rolls back *everything*
done in that transaction.
Appearantly sequences are not included.
Hi Alvaro,
Thanks for your reply.
Yes. This is actually a desirable property, because it allows sequences
to work fine in concurrent scenarios (which are, after all, the whole
point of sequences).
Ok.
I think I understand what you mean.
Scenario:
1) I start my transaction in which I call nextval(etc.etc).
2) Some other process/person has to wait for that transaction to end to
get its own new sequencenumber.
That would surely be not desirable now I think of it. :-)
So Postgres developers decided to hand them out immediately, since
nothing of real value has changed in the db, and they are just
incremental numbers.
If you actually need an incremental number with no gaps (which is rare
but not unseen), you need to use some other mechanism, which will
probably involve a lock to prevent concurrency.
No, I don't need gap-less numbering.
In (almost all) situations I use sequences it is simply to generate
unique PKs for some table, so I don't care for gaps in the numbering.
I just thought I hitted some stange bug. ;-)
Thanks for your time.
Regards,
Erwin Moller
Alvaro Herrera wrote:
Erwin Moller wrote:
I thought a transaction that is rolled back, rolls back *everything*
done in that transaction.
Appearantly sequences are not included.Yes. This is actually a desirable property, because it allows sequences
to work fine in concurrent scenarios (which are, after all, the whole
point of sequences).If you actually need an incremental number with no gaps (which is rare
but not unseen), you need to use some other mechanism, which will
probably involve a lock to prevent concurrency.
If so, search the list archives for "gapless sequence". You'll see lots
of explanations of options and of the problems with them.
--
Craig Ringer