Re: Select for update Question - Mailing list pgsql-general

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