Brian Karlak wrote: > The setup is relatively simple: there is a central queue table in > postgres. Worker daemons do a bounded, ordered, limited SELECT to grab > a row, which they lock by setting a value in the queue.status column. You can probably do an UPDATE ... RETURNING to turn that into one operation - but that won't work with a cursor :-( > My question is this: is there some way that I can keep a cursor / > pointer / reference / whatever to the row I fetched originally, so that > I don't have to search for it again when I'm ready to write results? You could use a cursor, but it won't work if you're locking rows by testing a 'status' flag, because that requires the worker to commit the transaction (so others can see the status flag) before starting work. A cursor only exists within a transaction. BEGIN; DECLARE curs CURSOR FOR SELECT * FROM queue ORDER BY queue_id LIMIT 1; FETCH NEXT FROM curs; -- -- Set the status - but nobody else can see the change yet because we -- haven't committed! We'll have a Pg row lock on the record due to the -- UPDATE, preventing other UPDATEs but not other SELECTs. -- -- We can't start work until the transaction commits, but committing -- will close the cursor. -- UPDATE queue SET status = 1 WHERE CURRENT OF curs; I don't have a good answer for you there. Perhaps using Pg's locking to do your queueing, rather than updating a status flag, might let you use a cursor? Have a look at the list archives - there's been a fair bit of discussion of queuing mechanisms. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
