Re: Listen / Notify - what to do when the queue is full - Mailing list pgsql-hackers

Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> A better approach is to do something similar to what we do now: at
>> prepare, just store the notifications in the state file like we do
>> already. In notify_twophase_postcommit(), copy the messages to the
>> shared queue. Although it's the same approach we have now, it becomes a
>> lot cleaner with the patch, because we're not piggybacking the messages
>> on the backend-private queue of the current transaction, but sending the
>> messages directly on behalf of the prepared transaction being committed.
> 
> This is still ignoring the complaint: you are creating a clear risk
> that COMMIT PREPARED will fail.
> 
> I'm not sure that it's really worth it, but one way this could be made
> safe would be for PREPARE to "reserve" the required amount of queue
> space, such that nobody else could use it during the window from
> PREPARE to COMMIT PREPARED.

Hmm, ignoring 2PC for a moment, I think the patch suffers from a little
race condition:

Session 1: BEGIN;
Session 1: INSERT INTO foo ..;
Session 1: NOTIFY 'foo';
Session 1: COMMIT -- commit begins
Session 1: [commit processing runs AtCommit_NotifyBeforeCommit()]
Session 2: LISTEN 'foo';
Session 2: SELECT * FROM foo;
Session 1: [AtCommit_NotifyAfterCommit() signals listening backends]
Session 2: [waits for notifications]

Because session 2 began listening after session 1 had already sent its
notifications, it missed them. But the SELECT didn't see the INSERT,
because the inserting transaction hadn't fully finished yet.

The window isn't as small as it might seem at first glance, because the
WAL is fsynced between the BeforeCommit and AfterCommit actions.

I think we could fix that by arranging things so that a backend refrains
from advancing its own 'pos' beyond the first notification it has
written itself, until commit is completely finished. I'm not sure but
might already be true if we don't receive interrupts between
BeforeCommit and AfterCommit. LISTEN can then simply start reading from
QUEUE_TAIL instead of QUEUE_HEAD, and in the above example session 2
will see the notifications sent by session 1.

That will handle 2PC as well. We can send the notifications in
prepare-phase, and any LISTEN that starts after the prepare-phase will
see the notifications because they're still in the queue. There is no
risk of running out of disk space in COMMIT PREPARED, because the
notifications have already been written to disk. However, the
notification queue can't be truncated until the prepared transaction
finishes; does anyone think that's a show-stopper?

> On the whole I'd be just as happy to disallow NOTIFY in a 2PC
> transaction.  We have no evidence that anyone out there is using the
> combination, and if they are, they can do the work to make it safe.

Yeah, I doubt we'd hear many complaints in practice.

--  Heikki Linnakangas EnterpriseDB   http://www.enterprisedb.com