Select for update Question

Hi

I have a function called from a java app (via jdbc) which identifies
images awaiting processing. This is determined by checking the
WPImageStateID field on the WPImageHeader record (1=awaiting, 2=being
processed, 3=complete).

The (jdbc) connection to the database is a standard one so I suspect that
the transaction isolation level is Read uncommitted.

What I need is for the call to GetNextChangedImageHeader() to return the
WDResourceID of the next WPImageHeader record awaiting processing.

The way it is written (I think that) it will either return the ID of a
WPImageHeader record that genuinely is awaiting processing (if one is
available), or will return -1 because it waited on a row lock which was
released by another transaction on the same WPImageHeader record, but
whose WPImageStateID is now no longer 1.

Does this look correct?

Thanks

John Sidney-Woollett

ps The function was converted from Oracle which allows a "select for
update NOWAIT" which meant that the procedure was written very differently
because this doesn't block, and either returns a row, or fails.

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
vIsLocked boolean := false;
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';