DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=Uob/unKfQr8NwnL3LhzRJ6Xil0XskeQVT0H3SwBHqc2l8XuLJ1yFt+FZwpWF+WVu6b
	PNpcQNFhb+eJ0zbVzqSA2PCvTjaZwgaz7YYcjQq3b8flAw0lHpr0EE24T83fKlpRVw6u
	9I38kkKMIzHqExXU1yUhJ5TeS0Hd1jUJlDXoQ=
MIME-Version: 1.0
In-Reply-To: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
References: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
Date: Mon, 1 Jun 2009 03:55:44 -0600
Message-ID: <dcc563d10906010255g63f48dds15ab4359c3a1be42@mail.gmail.com>
Subject: Re: Assigning data-entry tasks to multiple concurrent clients
From: Scott Marlowe <scott.marlowe@gmail.com>
To: Jamie Tufnell <diesql@googlemail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Sun, May 31, 2009 at 9:54 PM, Jamie Tufnell <diesql@googlemail.com> wrot=
e:
> BEGIN;
> SELECT * FROM records
> WHERE in_edit_queue AND id NOT IN (
> =A0 SELECT record_id FROM locked_records
> =A0 WHERE locked_since < now() + interval '5 minutes')
> LIMIT 1;
>
> INSERT INTO locked_records (record_id, locked_since) VALUES (?, now());
> COMMIT;

There's a race condition here but a unique constraint on record_id
will take care of that, as long as you catch the error and retry.

> Then to save (first-in wins is acceptable for this environment):
>
> BEGIN;
> UPDATE records SET in_edit_queue =3D false WHERE id =3D ? AND in_edit_que=
ue =3D
> true;
> DELETE FROM locked_records WHERE record_id =3D ?;
> COMMIT;
>
> Is this a sane approach?=A0 Is there a better way to do this with Postgre=
SQL?

It'll work.  The key to any kind of system like this is monitoring the
progress for things that get stuck / fail to be processed and running
them a second time if need be.  I had a system to process 1M rows at a
time from an 880M row db, and I used a secondary sequence and recid/1M
to partition it out.  So, the next job up grabs a sequence id from t
secondary sequence, which matches the record(or set) to be processed.
With that method there's no locking or anything needed, and no one
needs to "check out" the records, because incrementing the secindary
sequence is in fact checking them out.  Just check the finished table
to see if there's any holes and if there are put those jobs back in
the queue by simply updating their id to the next value for the
porimary id sequence.

Sequences can be an elegant way of assigning jobs to multiple threads
without locking issues.