Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to don't update sequence on rollback of a transaction
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to don't update sequence on rollback of a transaction
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to don't update sequence on rollback of a transaction
On Fri, Aug 3, 2012 at 6:59 AM, Julian temp...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to don't update sequence on rollback of a transaction
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
Re: [GENERAL] How to don't update sequence on rollback of a transaction
On Fri, Aug 3, 2012 at 1:08 AM, Frank Lanitz fr...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Singleton table (was Re: [GENERAL] How to don't update sequence on rollback of a transaction)
On 08/03/2012 12:07 PM, Chris Angelico wrote: On Fri, Aug 3, 2012 at 10:00 AM, Craig Ringer ring...@ringerc.id.au wrote: -- 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) ); This will guarantee that there's only one row. Does Postgres then optimize anything based on this? I have a couple of cases where I'm using a table to store global configuration, and selecting/updating that table without a WHERE clause to manipulate it. Can PostgreSQL notice the highlander status of the table and know not to go looking for more? As far as I know, no. If anything it could slow things down a tad. I just like to be really sure it's a single row table. An `ON INSERT OR DELETE` trigger that throws is probably a more sensible option, really. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
