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
ifyou want to reduce potential lock contention. Write a simple SQL function that uses an UPDATE ... RETURNING statement
tograb a new ID from the counter and increment it. Use that function instead of 'nextval(seqname)' when you want an ID.
TheUPDATE will take a lock out on the customer row (or side-table row if you did it that way) that'll prevent anyone
elseupdating 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,
aslong as I can guarantee that invoice numbers never change and invoices are never deleted. Right?