Re: Selecting a non-locked row.

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