Re: Assigning data-entry tasks to multiple concurrent clients

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.
>