On 10/24/2010 12:42 AM, Michael Gardner wrote:
On Oct 22, 2010, at 11:03 PM, Craig Ringer wrote:
Instead, maintain a counter, either in the main customer record or in an
associated (customer_id, counter) side table if you want to reduce potential
lock contention. Write a simple SQL function that uses an UPDATE ... RETURNING
statement to grab a new ID from the counter and increment it. Use that function
instead of 'nextval(seqname)' when you want an ID. The UPDATE will take a lock
out on the customer row (or side-table row if you did it that way) that'll
prevent anyone else updating it until the transaction commits or rolls back.
Thanks for the suggestion. It seems like there should be a safe way to use
max() instead of a separate counter though, as long as I can guarantee that
invoice numbers never change and invoices are never deleted. Right?
True. You'll then have to provide your own locking (say, SELECT ... FOR
UPDATE on the customer record) to ensure that no two invoices are
allocated the same number, though. If you use UPDATE ... RETURNING on a
counter field the locking is done for you.
You'll have a UNIQUE(customer_id,order_id) constraint in place anyway,
of course, so you won't have the risk of genuinely duplicate IDs, just
the need to retry a transaction that fails due to a duplicate key error
if two invoice creations on a customer happen concurrently.
Perhaps it's too unlikely to care about, but I just dislike using
max(x)+1 on principle, as it's just a generally unsafe sql programming
idiom.
--
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
