From: Josh Berkus <josh@agliodbs.com>
To: Kurt Roeckx <Q@ping.be>
Cc: pgsql-sql@postgresql.org
Subject: Re: Selecting a non-locked row.
Date: Sun, 19 Jan 2003 14:34:21 -0800
Message-ID: <200301191434.21913.josh@agliodbs.com> (raw)
In-Reply-To: <20030119220348.GA4944@ping.be>
References: <20030119133320.GA3984@ping.be>
<200301191326.10285.josh@agliodbs.com>
<20030119220348.GA4944@ping.be>
Kurt,
> Of course a second person doing the same thing will just wait for
> my commit.
>
> What I want is that he just gets the next non-locked record.
Well, there's two ways you can do this:
1) hack the system tables to find out which incomplete rows in the table are
currently locked, and select the lowest ID from those that aren't. You can
do this in 7.3 fairly easily throught the "pg_locks" table, but in 7.2.3 it
involves a rather annoying hack of the "hidden" tuple fields (which I don't
know very well, so don't ask).
2) (my preference) modify your done/not done field to accept 3 values: not
done, in progress, done. Then add this step between select ... for update
and the final update that updates the row as "in progress". Then you can
more easily select the first "not done" row. (actually, you would have to
set a tuple lock with something longer lasting than select for update, and
arrange to drop it if the connection dies. but it's still my preferred
solution)
--
-Josh Berkus
Aglio Database Solutions
San Francisco
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-sql@postgresql.org
Cc: josh@agliodbs.com, Q@ping.be
Subject: Re: Selecting a non-locked row.
In-Reply-To: <200301191434.21913.josh@agliodbs.com>
* 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