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
