Re: How to don't update sequence on rollback of a transaction

Hi Frank,

I believe this is by design. See the bottom of the documentation on sequences where it states ;-

"Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values. setval operations are never rolled back, either."

http://www.postgresql.org/docs/9.1/static/functions-sequence.html

If you really want to reset the sequence, I think you would have to call SELECT SETVAL(.....) at the point you request the roll-back.

Regards
Andrew




On 02/08/12 16:08, Frank Lanitz wrote:

Hi folks,

I did a test with transactions and wondered about an behavior I didn't
expected. At http://pastebin.geany.org/bYQNo/raw/ I posted a complete
backlog for.

To make it short: I created a table with a serial and started a
transactions. After this I was inserting values into the table but did a
rollback. However. The sequence of the serial filed has been incremented
by 1 on each insert (which is fine), but wasn't reset after rollback of
transaction.

Documentation stats:
"If, partway through the transaction, we decide we do not wantto commit
(perhaps we just noticed that Alice's balance went   negative), we can
issue the command ROLLBACK instead of COMMIT, and all our updates so far
will be canceled."

My understanding of all was that it includes sequences. Obviously, I'm
wrong... but how to do it right?

Cheers,
Frank