From: | Jamie Tufnell <diesql(at)googlemail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Assigning data-entry tasks to multiple concurrent clients |
Date: | 2009-06-01 03:54:52 |
Message-ID: | b0a4f3350905312054k515ed999wef6431126a81a02d@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jamie Tufnell | 2009-06-01 04:49:51 | Re: Assigning data-entry tasks to multiple concurrent clients |
Previous Message | Tom Lane | 2009-05-28 15:19:04 | Re: exists and is not null equivalence in query |