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 from smallest to largest.

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

-Andy