Re: Select for update Question

> Unlikely as PostgreSQL doesn't support read uncommitted...

You're right - Postgres only offers two levels "PostgreSQL offers the Read
Committed and Serializable isolation levels."

> I think you need to play with a couple of psql sessions to sort this out.
> I think you might have a race condition here.

Following your suggestion, I made a test. In my tests with two PSQL
sessions and 1 row in the WPImageHeader table, the following occured:

Session 1: start transaction;
Session 1: select * from WPImageHeader where WDResourceID=1 for update;
Session 2: select GetNextChangedImageHeader();

{This call (Session 2) blocks until Session 1 either commits, or issues a
rollback}

Session 1: update WPImageHeader set WPImageStateID=2 where WDResourceID=1;
Session 2: {returns} -1

In other words GetNextChangedImageHeader() will block if another thread is
also calling GetNextChangedImageHeader() and they are both trying to
access the same record (reading the uncommitted values).

Is there a way to read the WPImageHeader table in such as way that you
skip any rows which have (any kind of) locks on them?

John Sidney-Woollett

ps I attach the function code again (just in case)

CREATE OR REPLACE FUNCTION GetNextChangedImageHeader() RETURNS integer AS '
-- returns the next image header (WDResourceID) awaiting processing
-- and changes the state of the record to being processed
-- Also modifies the state of an unprocessed (child) Image records
-- Either returns a WDResourceID or -1 if no record need processing
DECLARE
vWDResourceID integer := -1;
vImageStateID integer := null;

BEGIN
-- locate the first (unlocked?) ImageHeader awaiting processing
select WDResourceID, WPImageStateID
into vWDResourceID, vImageStateID
from WPImageHeader
where WPImageStateID = 1
for update
limit 1;

-- check that an image header record is available
if (vWDResourceID is null) then
return -1;
end if;

-- check that the state is really awaiting processing (=1)
if (vImageStateID > 1) then
return -1;
end if;

-- change the state to being processed
update WPImageHeader set WPImageStateID = 2
where WDResourceID = vWDResourceID;

-- mark the (child) image records as being processed too
update WPImage set WPImageStateID = 2
where WPImageStateID = 1
and WDResourceID = vWDResourceID;

return vWDResourceID;
END;
' LANGUAGE 'plpgsql';