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
>