Assigning data-entry tasks to multiple concurrent clients

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.

Responses

Browse pgsql-sql by date

  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