Re: easy task: concurrent select-updates

Nickolay wrote:
> Kevin McConnell wrote:
>>> CREATE OR REPLACE FUNCTION public.getmsg() RETURNS integer LANGUAGE
>>> plpgsql
>>> AS $function$
>>> declare
>>>       rec record;
>>> begin
>>>       for rec in select id from msg where busy =alse order by id loop
>>>               update msg set busy =rue where id = rec.id and busy =
>>> false;
>>>               if found then
>>>                       return rec.id;
>>>               end if;
>>>       end loop;
>>>       return -1;
>>> end;
>>> $function$
>>>
>>
>> I think you could also do something roughly similar in a statement by
>> using a RETURNING clause on the update, such as:
>>
>>   update msg set busy =rue where id = (select min(id) from msg where
>> busy =alse) returning *;
>>
>> Cheers,
>> Kevin
>>
>
> Thank you guys! But what's min(id) for? Is it neccessary? Is there any
> chance I can replace min(id) to LIMIT 1?
>
> Best regards, Nick.
>

min(id) finds the smallest id in the table.  We made the assumption that you wanted to get the messages out order by id
fromsmallest to largest. 

LIMIT 1 would be ok if you didnt care what order the messages were processed in.

-Andy