DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:content-type:content-transfer-encoding;
	b=VQqllLvym/mOFfZs0qhqUhbtgmtV+LfbRnc4oozKhXE12DUgeGVJ5dqL4U1b0Q6LHI
	jlIpXM0XWm+Ykag2TVggnAWs6fEPKDiLbnWYtiZloLdBmxc5Np1AqBFzt72yhGtENQzb
	7jBK020lfd+525IgGgmjcOaSIV0jn4EdrtJxI=
MIME-Version: 1.0
In-Reply-To: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
References: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
Date: Mon, 1 Jun 2009 14:49:51 +1000
Message-ID: <b0a4f3350905312149t672c2dbar117a641958baa67d@mail.gmail.com>
Subject: Re: Assigning data-entry tasks to multiple concurrent clients
From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Just want to correct a small but significant error in the first query:

WHERE locked_since < now() + interval '5 minutes'

Should read:

WHERE locked_since BETWEEN now() - interval '5 minutes' AND now();

On 6/1/09, Jamie Tufnell <diesql@googlemail.com> wrote:
> 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.
>