Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhbl7-003ds3-9r for pgsql-general@arkaria.postgresql.org; Sat, 16 Sep 2023 20:19:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qhbl4-000gur-HJ for pgsql-general@arkaria.postgresql.org; Sat, 16 Sep 2023 20:19:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhD2l-002NjL-CL for pgsql-general@lists.postgresql.org; Fri, 15 Sep 2023 17:55:51 +0000 Received: from mail.privatejuris.org ([37.228.129.12]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qhD2d-005I3O-R9 for pgsql-general@lists.postgresql.org; Fri, 15 Sep 2023 17:55:50 +0000 Content-Type: multipart/alternative; boundary="------------itAhpsxeTw0fM7Uvm0SQWn6F" DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=privatejuris.org; s=mail; t=1694800541; bh=RPzH/TIn/ODvL6vTX5x+mILcEy0yYSTWcIbB9CnAwMQ=; h=From:To:Subject; b=EkcRggyWrx0VmOU+zk3DI3udPVmLnl+iMyOY7iC8D6fW2/++w6vUsL4vdTqagN06V KxPMZelrk67ctLTfhpMl8XakE94uMdLEIMpzamCOWfIGDasqE3XOD71B1cqa6CRGqV YDsP3PWysPQQwKIfxIRORP7E/xjr2Q+hXGHT2/xZ1YTsTkwl6kMb3CF3E6A1FUF0Ae P6ZfE9JYp0dPevVL/YM1NwtMhgmieJnwRfCh96amYqOiSlXHMpRZWrdNp7iDGO29D1 +376/XB66g2/50h/Mm9OTzSyA+SA/aGI7coCySYccDthtme0WUzwjyy3HWGgwRXQ5x 9FxfTog09yBuA== Message-ID: Date: Fri, 15 Sep 2023 12:55:38 -0500 Mime-Version: 1.0 Content-Language: en-US From: James Stuart To: pgsql-general@lists.postgresql.org Subject: Can two-phase commit support LISTEN, UNLISTEN, and NOTIFY? List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------itAhpsxeTw0fM7Uvm0SQWn6F Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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./ --------------itAhpsxeTw0fM7Uvm0SQWn6F Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

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