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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
