Re: notification: pg_notify ? - Mailing list pgsql-hackers

Tom Lane wrote:

> It is *not* reliable, at least not in the sense of "the message is
> guaranteed to be delivered even if there's a system crash".  Which is
> the normal meaning of "reliable" in SQL environments.  If you want that


That is exactly what I mean by "reliable".


Please correct me if I'm wrong but the buffer overrun problem in the new
LISTEN/NOTOFY mechanism means that it is perfectly possible that sending
backend may drop all or some of the pending NOTIFY messages in case of such
an overrun. If this is the case then this new mechanism would be step
backward in terms of functionality relative to the current implementation.

There will be no guaranty even in a no-crash case.


> level of reliability, you need to pass your messages by storing them
> in a regular table.
> 

That is exactly what I do in my application. I store messages in a regular
table and then send a notify to other clients. But I'd like to have a
guaranty that without system crash all my notifies will be delivered.
I use this method when I need to send some additional information except
the notice's name. Another case is similar to your cache invalidation
example. The big difference is that I need to maintain a kind of cache for
the large number of big tables and I need to know promptly when these
tables change. I can't afford to update this cache frequently enough in
case of polling. And when there is no NOTIFY delivery guaranty the only
solution is polling. Occasional delivery of NOTIFY messages may only improve
in some sense the polling strategy. One can not rely on them.

> LISTEN/NOTIFY can optimize your message passing by avoiding unnecessary
> polling of the table in the normal no-crash case.  But they are not a


Guaranteed delivery in the normal no-crash case avoids polling
completely in case of cache invalidation scenario. DB crash recovery is a
very complex task for an application. Some time a recovery is not possible
at all. But for cache invalidation a DB crash is nothing more than cache
reinitialisation (you will get this crash notification without LISTEN/NOTIFY
message ;) Even stronger: you can't receive a crash notification with
LISTEN/NOTIFY mechanism).

And again, this no-crash case guaranty is already here! We don't need to
do anything!


> substitute for having a table, and I don't see a reason to bog them down


Sure their are not substitute, and I'm not the one who proposed to extend 

LISTEN/NOTIFY mechanism with additional information ;) This whole thread
was started to extend LISTEN/NOTIFY mechanism to support optional messages.
If we are agree that LISTEN/NOTIFY is not a substitute for having a table for
such a messages, then what is the purpose to reimplement this feature with
a loss of functionality?
> with an intermediate level of reliability that isn't buying anything.>

If you mean reliability in no-crash case then it gives a lot - it eliminates
need for polling completely. And once again, we already have this level of
reliability.

What exactly PG will get with this new LISTEN/NOTIFY mechanism? If the profit
has so great value, let's implement it as an additional feature, not as a
replacement of the existing one with loss of functionality.


Regards
Mikhail Terekhov