Select for update Question - Mailing list pgsql-general

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';