From: Jamie Tufnell <diesql@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Assigning data-entry tasks to multiple concurrent clients
Date: Mon, 1 Jun 2009 13:54:52 +1000
Message-ID: <b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com> (raw)
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: <b0a4f3350905312054k515ed999wef6431126a81a02d@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