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