pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: James Stuart <slim2k@privatejuris.org>
To: pgsql-general@lists.postgresql.org
Subject: Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY?
Date: Fri, 15 Sep 2023 12:55:38 -0500
Message-ID: <f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org> (raw)

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

view thread (2+ messages)  latest in thread

Message-ID: <f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org>
Permalink:  ../f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org/
Also on:    postgresql.org/message-id/f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: slim2k@privatejuris.org
  Subject: Re: Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY?
  In-Reply-To: <f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org>
  References: <f31290e4-7d3e-4784-252d-2f1863301522@privatejuris.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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