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

On Tue, 2010-01-19 at 19:24 -0500, Tom Lane wrote:
> (I'm still
> wondering if we couldn't do without the lock altogether though.)

Here's the problem as I see it:

If we insert the notifications into the queue before actually recording
the commit, there's a window in between where another backend could
perform the expected sequence as you wrote:

1. LISTEN foo; (and commit the listen)
2. examine current database state
3. assume that we'll get a NOTIFY for any change that commits
subsequently to what we saw in step 2

and miss the NOTIFYs, and not see the updated database state.

But I don't think that the NOTIFYs will actually be missed. Once put
into the queue, the notification will only be removed from the queue
after all backends have read it. But no backend will advance past it as
long as the notification is from an uncommitted transaction. By the time
the notifying transaction is committed, the listening transaction will
also be committed, and therefore subscribed to the queue.

The newly-listening backend will be awakened properly as well, because
that's done after the notifying transaction commits, and therefore will
wake up any listening transactions that committed earlier.

However, there's still a problem inserting into the queue when no
backends are listening. Perhaps that can be solved right before we wake
up the listening backends after the notifying transaction commits: if
there are no listening backends, clear the queue.

We still might get spurious notifications if they were committed before
the LISTEN transaction was committed. And we also might get spurios
notifications if the UNLISTEN doesn't take effect quite quickly enough.
Those are both acceptable.

If the above scheme is too complex, we can always use a heavyweight
lock. However, there's no pg_listener so it's not obvious what LOCKTAG
to use. We can just pick something arbitrary, like the Oid of the new
pg_listening() function, I suppose. Is there any precedent for that?

Thoughts?

Regards,
Jeff Davis