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

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