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

Hi,
If you want guaranteed "consecutive" sequential numbering you have to
implement your own solution. I was brought to task by a number of people
about this (accountants). So its not a good idea to use a sequence for
things like invoice, receipt and other such accounting objects (not only
monetary), unless its somehow acceptable in your region. You can pretty
much duplicate the functionality of sequences as normal tables with the
benefit of them being transaction safe.
Be sure you are using it for reasons where its absolutely required.
For everything else a sequence does what its intended to do.

Regards,
Julian

P.S I have heard of people using a sequence in an AFTER trigger to
generate consecutive numbering to some success. But anything could happen.

On 08/03/12 17:56, Frank Lanitz wrote:
> 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
>
>