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.