From: Craig Ringer <craig@postnewspapers.com.au>
To: Brian Karlak <zenkat@metaweb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: maintaining a reference to a fetched row
Date: Wed, 04 Nov 2009 08:03:48 +0800
Message-ID: <4AF0C4E4.2080902@postnewspapers.com.au> (raw)
In-Reply-To: <22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com>
References: <22AC581D-50D1-4863-9FDE-35EDDB171269@metaweb.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig@postnewspapers.com.au, zenkat@metaweb.com
Subject: Re: maintaining a reference to a fetched row
In-Reply-To: <4AF0C4E4.2080902@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox