Re: notification: pg_notify ?

On Thu, 2002-03-21 at 22:41, Tom Lane wrote:
> 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.

I've thought about this some more, and I don't think that solution will
be sufficient.

Spurious notifications seems like a pretty serious drawback, and I don't
think they solve anything. As I mentioned earlier, if the event a notify
signifies is non-trivial, this could have serious repercussions.

But more importantly, what happens when the buffer overruns and we
notify all backends? If a listening backend is in the middle of a
transaction when it is notified, it just sets a flag and goes back to
processing (i.e. it doesn't clear the buffer).

If a listening backend is idle when it is notified, it checks the
buffer: but since this is normal behavior, any idle & notified backend
will have already checked the buffer! I don't see how the "notify
everyone" scheme solves anything -- if a backend _could_ respond
quickly, it also would already done so and we wouldn't have an overrun
buffer in the first place.

If we notify all backends and then clear the notification buffer,
backends in the midst of a transaction will check the buffer when they
finish their transaction but find it empty. Since this has the potential
to destroy legitimate notifications, this is clearly not an option.

Ultimately, we're just coming up with kludges to work around a
fundamental flaw (we're using a static buffer for a dynamically sized
resource). (Am I the only one who keeps running into shared memory
limitations? :-)

I can see two viable solutions:

(1) Use the shared-memory-based buffer scheme you suggested. When a
backend executes a NOTIFY, it stores it until transaction commit (as in
current sources). When the transaction commits, it checks to see if
there would be a buffer overflow if it added the NOTIFY to the buffer --
if so, it complains loudly to the log, and sleeps. When it awakens, it
repeats (try to add to buffer; else, sleep).

(2) The pg_notify scheme I suggested. It only marginally improves the
situation, but it does preserve the behavior we have now.

I think #1 isn't as bad as it might at first seem. The notification
buffer only overflows in a rare (and arguably broken) situation: when
the listening backend is in a (very) long-lived transaction, so that the
notification buffer is never checked and eventually fills up. If we
strongly suggest to application developers that they avoid this
situation in the first place (by not starting long-running transactions
in listening backends), and we also make the size of the buffer
configurable, this situation is tolerable.

Comments? Can anyone see a better solution? Is #1 reasonable behavior?

Cheers,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC