Re: easy task: concurrent select-updates

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.
>