Message-ID: <4AF0C4E4.2080902@postnewspapers.com.au>
Date: Wed, 04 Nov 2009 08:03:48 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Brian Karlak <zenkat@metaweb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: maintaining a reference to a fetched row
References: <22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com>
In-Reply-To: <22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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