pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY?
2+ messages / 2 participants
[nested] [flat]

* Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY?
@ 2023-09-15 17:55 James Stuart <slim2k@privatejuris.org>
  2023-09-16 23:03 ` Re: Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: James Stuart @ 2023-09-15 17:55 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

Can you please clarify, is lack of support for "LISTEN, UNLISTEN, and 
NOTIFY" with two-phase commits an architecture limitation or is this 
lacking implementation?  The NOTIFY happens after the transaction 
commits and can't undo the transaction, so I can't think of any reason 
why this could interfere with the two-phase commit.

https://www.postgresql.org/docs/current/sql-prepare-transaction.html

 > "It is not currently allowed to PREPARE a transaction that has 
executed any operations involving temporary tables or the session's 
temporary namespace, created any cursors WITH HOLD, or executed LISTEN, 
UNLISTEN, or NOTIFY. Those features are too tightly tied to the current 
session to be useful in a transaction to be prepared."

I can't see a work-around even by writing to an event queue table.  The 
event queue table can't be populated by either during a two-phase commit 
so it would have to be polled negating the purpose of LISTEN / NOTIFY.


-- 
*— James Stuart*
/This private communication and any attachment(s) are protected by the 
expectation of privacy and is for the sole use of the intended recipient 
and contains privileged and/or confidential information. No monitoring 
of my communication or other means of surveillance, electronic and 
otherwise, is permitted and I reserve all my rights, without recourse 
and without prejudice, nunc pro tunc.  I do not consent to anyone 
tampering with, altering, monitoring or delaying any incoming or 
outgoing communication./

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY?
  2023-09-15 17:55 Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY? James Stuart <slim2k@privatejuris.org>
@ 2023-09-16 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2023-09-16 23:03 UTC (permalink / raw)
  To: James Stuart <slim2k@privatejuris.org>; +Cc: pgsql-general@lists.postgresql.org

James Stuart <slim2k@privatejuris.org> writes:
> Can you please clarify, is lack of support for "LISTEN, UNLISTEN, and 
> NOTIFY" with two-phase commits an architecture limitation or is this 
> lacking implementation?

The point of LISTEN and UNLISTEN is to change the listening state of
the current session, so it's hard to see how it'd make sense to
decouple them into a prepared transaction that might get committed by
a different session.  That'd end up with that other session changing
listen state (barring some really messy mechanism for propagating
the change back to the original session, assuming it still exists);
which seems surprising and probably not very useful.

I suppose you could argue that NOTIFY is a fire-and-forget operation
that could be postponed into a prepared transaction without making
a semantic mess.  But even then, it's not very clear whether the
message ought to carry the PID of the original session or the
committing session.  The original session would make more sense,
were it not that that might not exist anymore (and indeed its PID
could now be in use by some unrelated process).

Another problem, which perhaps is an implementation detail but
is real nonetheless, is that the current NOTIFY logic goes out
of its way to ensure that NOTIFY cannot cause a post-commit failure.
It does that by pushing the pending NOTIFY message(s) into the shared
queue before we reach the point of atomic commit.  If we run out of
space in the notify queue we can still fail the current transaction.
This approach is dependent on the assumption that it won't be very
long from pushing the messages into the queue to actual commit, else
we run the risk of clogging the queue.  So we couldn't perform that
step during PREPARE, and then there's a problem that we've created a
way for COMMIT PREPARED to fail, which ideally it should never do.

So I think there are indeed semantic issues, it's not entirely
a matter of "nobody got around to it".

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2023-09-16 23:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-15 17:55 Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY? James Stuart <slim2k@privatejuris.org>
2023-09-16 23:03 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox