Re: Listen / Notify - what to do when the queue is full

On Thu, Nov 19, 2009 at 4:12 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> There will now be a nonzero chance
> of transactions failing at commit because of queue full.  If the
> chance is large this will be an issue.  (Is it sane to wait for
> the queue to be drained?)

Exactly. The whole idea of putting the notification system to an slru queue
was to make this nonzero chance a very-close-to-zero nonzero chance.

Currently with pages from 0..0xFFFF we can have something between
160,000,000 (no payload) and 2,000,000 (biggest payload) notifications
in the queue at the same time.

We are free to remove the slru limitation by making slru.c work with 8
character file names. Then you can multiply both limits by 32,000 and
then it should be very-close-to-zero, at least in my point of view...

The actual queue-full behavior is then (or maybe is already now) just
a theoretical aspect that we need to agree on to make the whole
concept sound.

The current patch would just wait until some space becomes available
in the queue and it guarantees that no notification is lost. Furthermore
it guarantees that a transaction can listen on an unlimited number of
channels and that it can send an unlimited number of notifications,
not related to the size of the queue. It can also send that unlimited
number of notifications if it is one of the listeners of those notifications.

The only real limit is now the backend's memory but as long as nobody
proves that he needs unlimited notifications with a limited amount of
memory we just keep it like that.

I will add a CHECK_FOR_INTERRUPTS() and resubmit so that you
can cancel a NOTIFY while the queue is full. Also I've put in an
optimization to only signal those backends in a queue full situation
that are not yet up-to-date (which will probably turn out to be only one
backend - the slowest that is in a long running transaction - after some
time...).

> BTW, did we discuss the issue of 2PC transactions versus notify?
> The current behavior of 2PC with notify is pretty cheesy and will
> become more so if we make this change --- you aren't really
> guaranteed that the notify will happen, even though the prepared
> transaction did commit.  I think it might be better to disallow
> NOTIFY inside a prepared xact.

Yes, I have been thinking about that also. So what should happen
when you prepare a transaction that has sent a NOTIFY before?

Joachim