Re: BUG #9557: Row not visible after receiving notification

On 3/13/14 10:39 AM, marko(at)joh(dot)to wrote:
> We went live with an application a few days ago, and it's been having
> problems with seeing rows after receiving a notification from the
> transaction that committed it. I can't say for sure how often it happens,
> but I've seen evidence suggesting at least three separate occasions.

I can reproduce this locally with some trickery. Here's what I did:

1) Stop the listener at HandleNotifyInterrupt() (suppose an unrelated
notification comes in in the real world).
2) In another backend, do what the application would normally do, but
stop it before ProcArrayEndTransaction().
3) Let the notification go through to the listener. It sees *both*
notifications (since the new one has been committed to CLOG).
4) SELECT from the frontend after seeing the notification (#2 is
still stuck right before ProcArrayEndTransaction).

This would explain it, as I don't see the notification code checking
whether the transaction is still in PGPROC, just calling
TransactionIdDidCommit().

Any thoughts?

Regards,
Marko Tiikkaja