From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Assigning data-entry tasks to multiple concurrent clients
Date: Mon, 1 Jun 2009 14:49:51 +1000
Message-ID: <b0a4f3350905312149t672c2dbar117a641958baa67d@mail.gmail.com> (raw)
In-Reply-To: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
References: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: diesql@googlemail.com
Subject: Re: Assigning data-entry tasks to multiple concurrent clients
In-Reply-To: <b0a4f3350905312149t672c2dbar117a641958baa67d@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox