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

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

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

On Fri, Aug 3, 2012 at 1:08 AM, Frank Lanitz <frank@frank.uvena.de> wrote:
> My understanding of all was that it includes sequences. Obviously, I'm
> wrong... but how to do it right?

Sequences are fast and lock-free, but don't guarantee absence of gaps.
Quite a few things can unexpectedly advance a sequence (including
master-slave failover in replication - I've noticed IDs jump by about
32).

What should happen when two transactions simultaneously want a new ID?
Should the second block, waiting for the first one to commit or roll
back? Or will you allow the gaps, just as long as they get filled in
later?

The easiest way is probably to have a dedicated table of available
numbers, and use DELETE ... RETURNING to get the next one.

ChrisA

On 08/02/2012 11:08 PM, 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.
It's interesting that you read the documentation and still got bitten by
this. I'll have to think about writing a patch to add some
cross-references and make the tx exception of sequences more obvious.

The general idea with sequences is that they produce numbers that can be
meaningfully compared for equality and for greater/less-than, but *not*
for distance from each other. Because they're exempt from transactional
rollback you shouldn't use them when you need a gap-less sequence of
numbers.

It's usually a sign of an application design problem when you need a
gapless sequence. Try to work out a way to do what you need when there
can be gaps. Sometimes it's genuinely necessary to have gapless
sequences though - for example, when generating cheque or invoice numbers.

Gap-less sequences are often implemented using a counter table and
UPDATE ... RETURNING, eg:

CREATE TABLE invoice_number (
     last_invoice_number integer primary key
);

-- PostgreSQL specific hack you can use to make
-- really sure only one row ever exists
CREATE UNIQUE INDEX there_can_be_only_one
ON invoice_number( (1) );

-- Start the sequence so the first returned value is 1
INSERT INTO invoice_number(last_invoice_number) VALUES (0);

-- To get a number; PostgreSQL specific but cleaner.
UPDATE invoice_number
SET last_invoice_number = last_invoice_number + 1
RETURNING last_invoice_number;


Note that the `UPDATE ... RETURNING` will serialize all transactions.
Transaction n+1 can't complete the UPDATE ... RETURNING statement until
transaction `n' commits or rolls back. If you are using gap-less
sequences you should try to keep your transactions short and do as
little else in them as possible

--
Craig Ringer

Hi,

Thanks very much for the detailed answer. I totally missed the issue
with concurrent transactions.

Am 03.08.2012 02:00, schrieb Craig Ringer:

> It's interesting that you read the documentation and still got bitten by
> this. I'll have to think about writing a patch to add some
> cross-references and make the tx exception of sequences more obvious.

This would be great. I just read the transaction documentation and had
only a short look onto sequence documentation part. I totally missed the
important window at the end.

> The general idea with sequences is that they produce numbers that can be
> meaningfully compared for equality and for greater/less-than, but *not*
> for distance from each other. Because they're exempt from transactional
> rollback you shouldn't use them when you need a gap-less sequence of
> numbers.
>
> It's usually a sign of an application design problem when you need a
> gapless sequence. Try to work out a way to do what you need when there
> can be gaps. Sometimes it's genuinely necessary to have gapless
> sequences though - for example, when generating cheque or invoice numbers.

Yes. I understood now ;)

> Gap-less sequences are often implemented using a counter table and
> UPDATE ... RETURNING, eg:
>
> CREATE TABLE invoice_number (
>     last_invoice_number integer primary key
> );
>
> -- PostgreSQL specific hack you can use to make
> -- really sure only one row ever exists
> CREATE UNIQUE INDEX there_can_be_only_one
> ON invoice_number( (1) );
>
> -- Start the sequence so the first returned value is 1
> INSERT INTO invoice_number(last_invoice_number) VALUES (0);
>
> -- To get a number; PostgreSQL specific but cleaner.
> UPDATE invoice_number
> SET last_invoice_number = last_invoice_number + 1
> RETURNING last_invoice_number;
>
>
> Note that the `UPDATE ... RETURNING` will serialize all transactions.
> Transaction n+1 can't complete the UPDATE ... RETURNING statement until
> transaction `n' commits or rolls back. If you are using gap-less
> sequences you should try to keep your transactions short and do as
> little else in them as possible

Thanks for the detailed idea how to do it correct. I'm not thinking
about invoice number handling but something I also don't want to have gaps.

Cheers,
Frank

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

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
>
>

On Fri, Aug 3, 2012 at 6:59 AM, Julian <tempura@internode.on.net> wrote:
> 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.

Yes what you basically do is use a sequence internally, where the user
can't see it, and then either use an after trigger or lock the table
for a very quick update to the column used for the row number after
all your processing is done.