Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-11 Thread Heikki Linnakangas

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-11 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-11 Thread Heikki Linnakangas

Tom Lane wrote:

Heikki Linnakangas [EMAIL PROTECTED] 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-11 Thread Tom Lane
Heikki Linnakangas [EMAIL PROTECTED] writes:
 Tom Lane wrote:
 Heikki Linnakangas [EMAIL PROTECTED] 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-11 Thread Heikki Linnakangas

Tom Lane wrote:

Heikki Linnakangas [EMAIL PROTECTED] 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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


[HACKERS] LISTEN vs. two-phase commit

2008-03-10 Thread Tom Lane
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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-10 Thread Mark Mielke

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 [EMAIL PROTECTED]


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-10 Thread Tom Lane
Mark Mielke [EMAIL PROTECTED] 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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] LISTEN vs. two-phase commit

2008-03-10 Thread Mark Mielke

Tom Lane wrote:

Mark Mielke [EMAIL PROTECTED] 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 [EMAIL PROTECTED]