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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
