> 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';
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
