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

On 10/12/2003 09:10 John Sidney-Woollett wrote:
> [snip]
>
> Actually from my tests, the simple code below *sometimes* blocks on the
> locked row, and sometimes skips the locked row, and returns the next
> unlocked row.

That may be because there is no guarantee of the ordering of your returned
records.

> [snip]
> LESS SIMPLE, AND DOES NOT WORK AS EXPECTED
>
> In this example, I tried placing the select into a loop so that it would
> keep trying to locate a matching record until either none were available,
> or it got hold of a matching record.
>
> The problem is that the select fails to return the correct results after
> the first iteration through the loop - it's almost as if it is not
> refiring the query, but only applying the where clause against the
> previously found resultset record(s) (which is not what I want).
>
>   while true loop
>     -- locate the first (unlocked?) ImageHeader awaiting processing
>     -- it will block here
>     select WDResourceID, WPImageStateID
>     into vWDResourceID, vImageStateID
>     from WPImageHeader
>     where WPImageStateID = 1
>     for update
>     limit 1;
>
>     if (vWDResourceID is null) then
>       -- no record was available, so exit
>
>       -- check if there are any more records that might be
>       -- available, if we run round the loop again
>       select count(*) into vCount
>       from WPImageHeader
>       where WPImageStateID = 1;
>
>       if (vCount = 0) then
>         -- if there are not, then abort
>         return -2;
>       end if;
>     else
>       -- see if this record (that *may* have been locked)
>       -- is one that meets our criteria
>       -- if it is then leave the loop
>       if (vImageStateID = 1) then
>         exit;
>       end if;
>     end if;
>
>     -- safety measure to make sure we do leave the loop
>     -- at some point, vAbortCounter initially assigned
>     -- a value of 10
>     vAbortCounter := vAbortCounter - 1;
>     if (vAbortCounter < 0) then
>       return -3;
>     end if;
>   end loop;
>
> Maybe someone can explain what the problem is with the second version -
> I've puzzled it a bit, but am none the wiser.

Maybe you need to re-initialise your variables before executing the select?

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+