Re: maintaining a reference to a fetched row

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