Re: LISTEN/NOTIFY and notification timing guarantees

I wrote:
> ...
> 3. It is possible for a backend's own self-notifies to not be delivered
> immediately after commit, if they are queued behind some other
> uncommitted transaction's messages. That wasn't possible before either.
> ... We could fix
> #3 by re-instituting the special code path that previously existed for
> self-notifies, ie send them to the client directly from AtCommit_Notify
> and ignore self-notifies coming back from the queue. This would mean
> that a backend might see its own self-notifies in a different order
> relative to other backends' messages than other backends do --- but that
> was the case in the old coding as well. I think preserving the
> property that self-notifies are delivered immediately upon commit might
> be more important than that.

I modified the patch to do that, but after awhile realized that there
are more worms in this can than I'd thought. What I had done was to add
the NotifyMyFrontEnd() calls to the post-commit cleanup function for
async.c. However, that is a horribly bad place to put it because of the
non-negligible probability of a failure. An encoding conversion
failure, for example, becomes a "PANIC: cannot abort transaction NNN,
it was already committed".

The reason we have not seen any such behavior in the field is that
in the historical coding, self-notifies are actually sent *pre commit*.
So if they do happen to fail you get a transaction rollback and no
backend crash. Of course, if some notifies went out before we got to
the one that failed, the app might have taken action based on a notify
for some event that now didn't happen; so that's not exactly ideal
either.

So right now I'm not sure what to do. We could adopt the historical
policy of sending self-notifies pre-commit, but that doesn't seem
tremendously appetizing from the standpoint of transactional
integrity. Or we could do it the way Joachim's submitted patch does,
but I'm quite sure somebody will complain about the delay involved.
Another possibility is to force a ProcessIncomingNotifies scan to occur
before we reach ReadyForQuery if we sent any notifies in the
just-finished transaction --- but that won't help if there are
uncommitted messages in front of ours. So it would only really improve
matters if we forced queuing order to match commit order, as I was
speculating about earlier.

Thoughts?

regards, tom lane