Re: easy task: concurrent select-updates - Mailing list pgsql-general

From Andy Colson
Subject Re: easy task: concurrent select-updates
Date
Msg-id 4AA01605.80802@squeakycode.net
Whole thread Raw
In response to Re: easy task: concurrent select-updates  (Kevin McConnell <kevin.mcconnell@gmail.com>)
Responses Re: easy task: concurrent select-updates  (Nickolay <nitro@zhukcity.ru>)
List pgsql-general
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 = false order by id loop
>>               update msg set busy = true 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 = true where id = (select min(id) from msg where
> busy = false) returning *;
>
> Cheers,
> Kevin
>

I had thought of that, but you'd need to add one thing, in the update ' and busy = false ', cuz two people may get the
sameid from the select min(id). 

update msg set busy = true where busy = false and id = (select min(id) from msg where busy = false) returning *;

but then you'd have to fire it over-and-over until you actually got a row updated.

Seemed easer to put the loop in function, then you can:

select id from getmsg();

-Andy

pgsql-general by date:

Previous
From: Steve Atkins
Date:
Subject: Re: PL/Perl 64-bit and sending emails
Next
From: Josef Wolf
Date:
Subject: Re: pg_ctl with unix domain socket?