Does it make any sense to allow LISTEN or UNLISTEN in a prepared
transaction?
It's certainly not sensical for these actions to affect the backend that
actually executes the COMMIT PREPARED, in the sense of creating or
destroying pg_listener entries for it. But how can we say that they
should affect the originating backend either? It might not even be
around anymore.
In the current implementation, LISTEN/UNLISTEN create or delete
pg_listener rows that are then held as uncommitted until COMMIT
PREPARED. This is bad enough for the LISTEN case, as a pg_listener row
becomes active for a backend PID that might not exist any longer, or
might now refer to a different session. In the UNLISTEN case it'd
result in blocking any other backend that is unlucky enough to try to
send a notify to the pending-dead tuple. (Well, actually, there's some
ugly coding in async.c that avoids that, but surely that's a crock.)
And weird as that behavior would be, there would be no way at all to
duplicate it after the long-planned rewrite to get rid of pg_listener
and handle LISTEN/NOTIFY all in memory.
So I'm thinking that PREPARE TRANSACTION should throw an error if any
LISTEN or UNLISTEN is pending in the current transaction. This is
relatively difficult to enforce correctly in the existing code, but
it will be easy in the rewrite that I'm working on in response to
Laurent Birtz's bug report.
BTW, another little issue I just noticed is that while 2PC can cope
with NOTIFY actions, the eventual notify is sent with the PID of the
backend that executes COMMIT PREPARED, not the one that originally
created the prepared transaction. It's not clear if this is good,
bad, or indifferent; but at the least it probably deserves a sentence
someplace in the documentation.
Comments?
regards, tom lane