Received: from localhost (unknown [200.46.208.211])
	by mail.postgresql.org (Postfix) with ESMTP id 0719B633CF6
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Jun 2009 02:00:58 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024)
	with ESMTP id 65671-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Mon,  1 Jun 2009 02:00:52 -0300 (ADT)
X-Greylist: delayed 01:05:44.970488 by SQLgrey-1.7.6
Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.25])
	by mail.postgresql.org (Postfix) with ESMTP id 4209F6338B6
	for <pgsql-sql@postgresql.org>; Mon,  1 Jun 2009 02:00:39 -0300 (ADT)
Received: by qw-out-2122.google.com with SMTP id 8so3751096qwh.61
	for <pgsql-sql@postgresql.org>; Sun, 31 May 2009 22:00:38 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
	d=googlemail.com; s=gamma;
	h=domainkey-signature:mime-version:received:date:message-id:subject
	:from:to:content-type;
	bh=YMSMKoTTfNG51bmHZ3wmWInsYxaJKmw6vSdaRda2+oA=;
	b=EuNOzeoLI0ljv9pMOZ0stiFA+osswtbAyG1oepG7AG4iLT/ZrqJZHoIEVuoDN3zR3P
	Oxji53F3JJwzm5bcmzJKH1CLqhv0aDeyiY07lvKpzqfE1onxD8aUcHzoKPaJZrib7CSP
	hGhIQJf8G41AZjmzs5QoDrF+6yADKF0uhFtto=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=mime-version:date:message-id:subject:from:to:content-type;
	b=k12JW99EMYSV241qnRUhIlyh2R6mr+Ozge7IBBbfC6whqoIcwSISAtbPKX4JXTBMGp
	JGrf0C3fEZ2IMiMNPlBc7S8Kjlzi/uUR7ml17zW/udcvqHjqabZyp9cMBv70XYyijnO3
	GfXIM+E1mPN2grYppUoEfv/PcaNhWaO4rP+Gs=
MIME-Version: 1.0
Received: by 10.229.81.66 with SMTP id w2mr1613386qck.12.1243828492786; Sun,
	31 May 2009 20:54:52 -0700 (PDT)
Date: Mon, 1 Jun 2009 13:54:52 +1000
Message-ID: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
Subject: Assigning data-entry tasks to multiple concurrent clients
From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary=00163646d8e6795c49046b4163e0
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200906/2
X-Sequence-Number: 32607

--00163646d8e6795c49046b4163e0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Hi,

I am trying to provide a simple data entry interface to allow multiple
people to efficiently work through every record in a table and fill in the
missing values.

The interface is a web application that simply loads up record after record
until they're all complete.

I want to minimize the possibility of assigning the same record to two
users.

Below is how I'm thinking of assigning records to clients for editing.  The
idea is to pick a record for a user and remove it from the queue
temporarily.  It re-enters the queue after 5 minutes if no edit has been
made.

BEGIN;
SELECT * FROM records
WHERE in_edit_queue AND id NOT IN (
  SELECT record_id FROM locked_records
  WHERE locked_since < now() + interval '5 minutes')
LIMIT 1;

INSERT INTO locked_records (record_id, locked_since) VALUES (?, now());
COMMIT;

Then to save (first-in wins is acceptable for this environment):

BEGIN;
UPDATE records SET in_edit_queue = false WHERE id = ? AND in_edit_queue =
true;
DELETE FROM locked_records WHERE record_id = ?;
COMMIT;

Is this a sane approach?  Is there a better way to do this with PostgreSQL?

All feedback is greatly appreciated..

Cheers,
J.

--00163646d8e6795c49046b4163e0
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,<br><br>I am trying to provide a simple data entry interface to allow mu=
ltiple people to efficiently work through every record in a table and fill =
in the missing values.<br><br>The interface is a web application that simpl=
y loads up record after record until they&#39;re all complete.<br>
<br>I want to minimize the possibility of assigning the same record to two =
users.<br><br>Below is how I&#39;m thinking of assigning records to clients=
 for editing.=A0 The idea is to pick a record for a user and remove it from=
 the queue temporarily.=A0 It re-enters the queue after 5 minutes if no edi=
t has been made.<br>
<br>BEGIN;<br>SELECT * FROM records<br>WHERE in_edit_queue AND id NOT IN (<=
br>=A0 SELECT record_id FROM locked_records<br>=A0 WHERE locked_since &lt; =
now() + interval &#39;5 minutes&#39;)<br>LIMIT 1;<br><br>INSERT INTO locked=
_records (record_id, locked_since) VALUES (?, now());<br>
COMMIT;<br><br>Then to save (first-in wins is acceptable for this environme=
nt):<br><br>BEGIN;<br>UPDATE records SET in_edit_queue =3D false WHERE id =
=3D ? AND in_edit_queue =3D true;<br>DELETE FROM locked_records WHERE recor=
d_id =3D ?;<br>
COMMIT;<br><br>Is this a sane approach?=A0 Is there a better way to do this=
 with PostgreSQL?<br><br>All feedback is greatly appreciated..<br><br>Cheer=
s,<br>J.<br>

--00163646d8e6795c49046b4163e0--