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

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

That makes sense - I just didn't think of it. Doh!

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

I'm not sure what variables I could re-initialise here as the select
statement copies the current values INTO the two variables: vWDResourceID
and vImageStateID.

Thanks for your feedback.

John

Paul Thomas said:
>
> 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   |
> +------------------------------+---------------------------------------------+
>