Message-ID: <47F30A70.3090507@postnewspapers.com.au>
Date: Wed, 02 Apr 2008 12:24:16 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Icedove 1.5.0.12 (X11/20070607)
MIME-Version: 1.0
To: John Reeve <jreeve@pelagodesign.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sequential non unique IDs
References: 
 <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
In-Reply-To: 
 <E3BA5CEC8F665345BF4993E64A5F5C26C2556B@2exchange.pelagodesign.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

John Reeve wrote:

> I've already considered:
> 1. I can't lock the table, because there are too many inserts happening
> and it will slow down the app.

In a locking approach you may not need to lock the whole table. You
should only need to lock the entry for the customer being altered, eg:

BEGIN;

SELECT 1 FROM task WHERE customerid = 92 FOR UPDATE;

INSERT INTO task (id, customerid, localid) VALUES (nextval('task_id'),
92, (SELECT MAX(localid) + 1 FROM task WHERE customerid = 92));

If I'm not mistaken, that should ensure that for any given customer ID
there's only one transaction holding the locks on that customer.

It won't prevent SELECTs from reading the customer's records, but you
don't mind that so long as they're not using the customer's records to
determine the least free localid. That'll help reduce the hit on your
app's performance, too. If you do mind the , use SELECT ... FOR SHARE
and you'll wait on the FOR UPDATE lock if one is active (however, it
might prove hard to obtain a FOR UPDATE lock if there are lots of FOR
SHARE operations active).

I *think* that'll work, but you should of course test and investigate
before doing anything as crazy as taking my word for it.

> Anyone know a solution for this? This can't be the first time anyone has
> ever tried to do this. Thanks!

If I'm not mistaken about the similarity, you might want to search the
archives for the thread "Primary Key with serial". It might be informative.

--
Craig Ringer