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

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