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 <die...@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. > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
