Re: notification: pg_notify ?

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> Solution: Create a new system catalog, pg_notify.

It's not apparent to me why that helps much.

There is a very significant performance problem with LISTEN/NOTIFY
via pg_listener: in any application that generates notifications at
a significant rate, pg_listener will accumulate dead tuples at that
same rate, and we will soon find ourselves wasting lots of time
scanning through dead tuples. Frequent VACUUMs might help, but the
whole thing is really quite silly: why are we using a storage mechanism
that's designed entirely for *stable* storage of data to pass inherently
*transient* signals? If the system crashes, we have absolutely zero
interest in the former contents of pg_listener (and indeed need to go
to some trouble to get rid of them).

So if someone wants to undertake a revision of the listen/notify code,
I think the first thing to do ought to be to throw away pg_listener
entirely and develop some lower-overhead, shared-memory-based
communication mechanism. You could do worse than to use the shared
cache inval code as a model --- or perhaps even incorporate LISTEN
signaling into that mechanism. (Actually that seems like a good plan,
so as not to use shared memory inefficiently by dedicating two separate
memory pools to parallel purposes.)

If you follow the SI model then NOTIFY messages would essentially be
broadcast to all backends, and whether any given backend pays attention
to one is its own problem; no one else cares.

A deficiency of the SI implementation (and probably anything else that
relies solely on shared memory) is that it can suffer from buffer
overrun, since there's a fixed-size message pool. For the purposes
of cache inval, we cope with buffer overrun by just invalidating
everything in sight. It might be a workable tradeoff to cope with
buffer overrun for LISTEN/NOTIFY by reporting notifies on all conditions
currently listened for. Assuming that overrun is infrequent, the net
performance gain from being able to use shared memory is probably worth
the occasional episode of wasted work.

BTW, I would like to see a spec for this "notify with parameter" feature
before it's implemented, not after. Exactly what semantics do you have
in mind?

regards, tom lane