Re: easy task: concurrent select-updates

Andy Colson wrote:
> Kevin McConnell wrote:
>> 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 *;
>>
>
> 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 same id 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();
>

Thanks a lot for your solution! It works great for now.
Here is the thing I did following your advice:

CREATE TYPE queued_msg_row AS
(id bigint
,sender character varying
,"text" text
...
,msg_type integer);

CREATE OR REPLACE FUNCTION public.get_queued_msg
(_route_id integer
,_channel_id integer)
RETURNS queued_msg_row LANGUAGE plpgsql
AS $function$
declare
rec queued_msg_row;
begin
for rec in SELECT id,sender,"text", ... , msg_type
FROM msg_queue WHERE busy=false AND route_id=_route_id
ORDER BY priority DESC, date_time ASC LIMIT 10 loop
UPDATE msg_queue SET busy=true, channel_id=_channel_id WHERE id =
rec.id AND busy=false;
if found then
return rec;
end if;
end loop;
return NULL;
end;
$function$

The only problem that remains is that this function returns an empty row
when it should return NULL (no row), but that's not a critical issue.

Best regards, Nick.