Re: Listen/Notify feedback

On Sat, Jul 11, 2020 at 10:44 AM Brian Dunavant <dunavant(at)gmail(dot)com> wrote:

> One aspect is if there is no one listening when a notify happens, the
> message is lost (e.g. no durability). If this is important to you, it can
> be addressed by writing the messages to a table as well when you NOTIFY,
> and the listener deletes messages after they are processed. On connection
> the listener can query the table to catch up on any missed messages, or
> messages that were mid-process during a crash. This is trickier with more
> than one listener. This isn't a whole lot more efficient than just using
> the table alone, but it saves you from having to poll so better response
> times.
>

Good advice from Brian here that mirrors my own experience, I'd like to
point out that if you do go the multiple listener route working a
persistent table, it's important to avoid races with SELECT FOR UPDATE SKIP
LOCKED.

https://www.postgresql.org/docs/current/sql-select.html#SQL-FOR-UPDATE-SHARE

I know this is old news to most of the people on this list, but I've run
into enough folks who don't know about this little gem that I figured I'd
mention it, it's saved my bacon more than once.

-Michel

> On Sat, Jul 11, 2020 at 8:58 AM Rita <rmorgan466(at)gmail(dot)com> wrote:
>
>> I am investigating various pub/sub tools such as ActiveMQ, Rabbit, Redis,
>> etc.I came across Postgresql Listen/Notify and was easily able to write
>> code to listen to messages. For the people who have been using this for a
>> while: what are its downsides, things to consider when writing good code
>> that use pub/sub, how do you deal with large messages, can I have
>> subscribers listen to replica nodes?
>>
>> Thanks
>> --
>> --- Get your facts first, then you can distort them as you please.--
>>
>