Thread: 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