Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Kurt Roeckx <Q@ping.be>
Subject: Re: Selecting a non-locked row.
Date: Sun, 19 Jan 2003 14:34:21 -0800
Cc: pgsql-sql@postgresql.org
References: <20030119133320.GA3984@ping.be>
	<200301191326.10285.josh@agliodbs.com>
	<20030119220348.GA4944@ping.be>
In-Reply-To: <20030119220348.GA4944@ping.be>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200301191434.21913.josh@agliodbs.com>

 Kurt,

> Of course a second person doing the same thing will just wait for
> my commit.
>=20
> 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 ar=
e=20
currently locked, and select the lowest ID from those that aren't.   You ca=
n=20
do this in 7.3 fairly easily throught the "pg_locks" table, but in 7.2.3 it=
=20
involves a rather annoying hack of the "hidden" tuple fields (which I don't=
=20
know very well, so don't ask).

2) (my preference) modify your done/not done field to accept 3 values:  not=
=20
done, in progress, done.   Then add this step between select ... for update=
=20
and the final update that updates the row as "in progress".    Then you can=
=20
more easily select the first "not done" row.  (actually, you would have to=
=20
set a tuple lock with something longer lasting than select for update, and=
=20
arrange to drop it if the connection dies.  but it's still my preferred=20
solution)

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco