Re: easy task: concurrent select-updates

Nickolay wrote:
> one important addition: the message cannot be removed from queue table
> until it is transmitted, so DELETE is not an option :)
>> Hi All,
>>
>> I have a trivial task. There is a table with messages queue, let's say
>> "msg_queue".
>> There are a few processes and each of them is taking one message from
>> this table at a time to transmit into communication channel.
>> I've done it my way, but I have postgresql's messages about deadlocks
>> and a lot of warnings.
>>
>> I my program, every process is doing approx the following procedure:
>> SELECT ... FROM msg_queue WHERE busy = false ORDER BY ... LIMIT 1;
>> if a message was found:
>> BEGIN;
>> SELECT id FROM msg_queue WHERE id = ... AND busy = false FOR SHARE;
>> UPDATE msg_queue SET busy = true, channel_id = ... WHERE id = ... AND
>> busy = false;
>> COMMIT;
>>
>>
>> I do understand that this way is stupid, but I have not came with
>> anything else yet.
>> Could somebody share ideas how to do this so the same message 100%
>> WOULD NOT be transmitted over two or more channels.
>> Sorry for the newbie question!
>>
>> Best regards, Nick.
>>
>
>

how about this:

andy=# create table msg (id integer, busy boolean, message text);
CREATE TABLE
andy=# insert into msg values (1, false, 'message one');
INSERT 0 1
andy=# insert into msg values (2, false, 'message two');
INSERT 0 1

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$

It returns -1 if no message found. Not 100% sure, but a quick two session test seemed to work.

-Andy