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.
