Re: LISTEN vs. two-phase commit

Tom Lane wrote:
> 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.

Hmm. Yeah, it's much like the problem with temporary tables.

> 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.

Yeah, that's bad :-(. In practice, prepared transactions should be 
pretty short-lived, so reusing the PID for another backend seems 
unlikely. Entries for non-existent PIDs will be cleared on the next 
NOTIFY, but of course, if the PID is reused before any NOTIFYs, we're in 
trouble.

>  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.

Seems reasonable, for 8.4 at least. I can't think of a use case for 
using 2PC with LISTEN/UNLISTEN.

For back-branches, I'm a bit hesitant to do that, as there might be 
applications that do LISTEN in a prepared transaction unknowingly. Such 
an application wouldn't actually care about the atomicity of the LISTEN, 
but for example just issues a LISTEN at the beginning of each 
transaction, "just in case", together with a connection pool or 
something where it doesn't know which connection it's using.

> 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.

To be honest, I didn't realize the receiver gets to know the PID of the 
sending process, but clearly it does. It seems mostly indifferent to me; 
it's not guaranteed that the PID is valid by the time the client 
application sees it anyway. There is one slightly interesting use case 
though: if the client application ignores self-notifies, it would ignore 
the NOTIFYs of the prepared transactions it commits, even though they 
originally ran in another backend. It's worth mentioning in the docs, 
but I would leave it as it is for now.

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