LISTEN vs. two-phase commit

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

Tom Lane wrote:
> Does it make any sense to allow LISTEN or UNLISTEN in a prepared
> transaction?
> ...
>
> Comments?
>

Assuming I understand your question - I don't think of LISTEN or
UNLISTEN as being valuable from a transaction perspective. It's possible
I'm missing something - but I think the transaction overhead, and
attempts to re-use PostgreSQL tables to implement LISTEN/NOTIFY to be
clever but mis-guided. To be practical, LISTEN/NOTIFY should be as fast
as possible, and should never create performance problems, or incur
performance overhead related to transactions.

I had thought of using LISTEN/NOTIFY recently, and upon reading the
threads leading up to this, I was disappointed to hear, and that see for
myself, how asynchronous notify was not immediate within psql, and how
under some circumstances, even with asynchronous notify, it may take a
rather lengthy time before the notify reaches the target. I expect such
notification to be nearly instantaneous, and given this knowledge, I
would choose to use a LISTEN/NOTIFY mechanism outside PostgreSQL for my
next project. Now, does LISTEN/NOTIFY belong outside PostgreSQL in the
first place? I'm not sure...

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Mark Mielke <mark(at)mark(dot)mielke(dot)cc> writes:
> ... I think the transaction overhead, and
> attempts to re-use PostgreSQL tables to implement LISTEN/NOTIFY to be
> clever but mis-guided.

Oh, I don't disagree with you. As I already mentioned, they desperately
need to be rewritten. However, given that that's not a sanely
back-patchable answer, we have to consider what are the appropriate
semantics for the existing infrastructure.

(Also, if they *were* memory-based then the question of their relation
to 2PC semantics becomes even more urgent.)

regards, tom lane

Tom Lane wrote:
> Mark Mielke <mark(at)mark(dot)mielke(dot)cc> writes:
>
>> ... I think the transaction overhead, and
>> attempts to re-use PostgreSQL tables to implement LISTEN/NOTIFY to be
>> clever but mis-guided.
>>
>
> Oh, I don't disagree with you. As I already mentioned, they desperately
> need to be rewritten. However, given that that's not a sanely
> back-patchable answer, we have to consider what are the appropriate
> semantics for the existing infrastructure.
>
> (Also, if they *were* memory-based then the question of their relation
> to 2PC semantics becomes even more urgent.)
>

Ah k - so count my vote as "I don't think LISTEN should be impacted by
what sort of COMMIT I use, but I don't believe I'll be using LISTEN as
it is today, and I definately won't be using it in two-phase commit
today." For me that is "it should be usable in a two-phase commit - but
it's not usable today." Sorry this isn't a clear answer to your question.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

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

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> So I'm thinking that PREPARE TRANSACTION should throw an error if any
>> LISTEN or UNLISTEN is pending in the current transaction.

> For back-branches, I'm a bit hesitant to do that, as there might be
> applications that do LISTEN in a prepared transaction unknowingly.

I think that's a bit far-fetched...

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

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

Well, with the current definition it is; but that seems like a point
against trying to send the original PID.

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

Yeah, the original reason for sending the PID was exactly so that the
client could tell self-notifies apart from remote ones. The question
is, what the heck is a "self-notify" in the 2PC context?

regards, tom lane

Tom Lane wrote:
> "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
>> 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.
>
> Well, with the current definition it is; but that seems like a point
> against trying to send the original PID.

There's a small window between backend A committing and sending a
NOTIFY, and the time client B receives the notification from backend B
through the connection and reacts to it.

>> 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.
>
> Yeah, the original reason for sending the PID was exactly so that the
> client could tell self-notifies apart from remote ones. The question
> is, what the heck is a "self-notify" in the 2PC context?

I don't know. Perhaps we should just always report -1 as the PID with
2PC? Seems like the safest option.

Often you do use the same connection to send both PREPARE TRANSACTION
and COMMIT PREPARED, and do nothing in-between. If you use it like that,
then the 2PC is not any different from a normal commit from
LISTEN/NOTIFY point of view, and we could interpret self-notify as one
that came from your own backend.

This is all very hand-wavy of course, as we don't know of any real
application that uses LISTEN/NOTIFY with 2PC...

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

"Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
>>> 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.
>>
>> Well, with the current definition it is; but that seems like a point
>> against trying to send the original PID.

> There's a small window between backend A committing and sending a
> NOTIFY, and the time client B receives the notification from backend B
> through the connection and reacts to it.

Sorry, I was unclear: the case that's of interest is telling
self-notifies apart from others. For this purpose, your own backend's
PID *is* sufficiently stable, because you're still connected to it
when the notify is sent to you.

> This is all very hand-wavy of course, as we don't know of any real
> application that uses LISTEN/NOTIFY with 2PC...

Yeah. I'm inclined to leave that alone (but document it) until/unless
someone complains. Without a real use-case to look at, it's a bit hard
to be sure what's a useful behavior.

regards, tom lane

Tom Lane wrote:
> "Heikki Linnakangas" <heikki(at)enterprisedb(dot)com> writes:
>> There's a small window between backend A committing and sending a
>> NOTIFY, and the time client B receives the notification from backend B
>> through the connection and reacts to it.
>
> Sorry, I was unclear: the case that's of interest is telling
> self-notifies apart from others. For this purpose, your own backend's
> PID *is* sufficiently stable, because you're still connected to it
> when the notify is sent to you.

Oh, I see. Yes, that's true.

>> This is all very hand-wavy of course, as we don't know of any real
>> application that uses LISTEN/NOTIFY with 2PC...
>
> Yeah. I'm inclined to leave that alone (but document it) until/unless
> someone complains. Without a real use-case to look at, it's a bit hard
> to be sure what's a useful behavior.

Yep.

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