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

Am 02.08.2012 17:15, schrieb Andrew Hastie:
> 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.

Yepp. Somehow I missed that part of documentation. I don't think setval
will do the trick I want to perform, but Craig's idea looks very well.

Thanks for feedback!

Cheers,
Frank