Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux