From: Tom Lane <tgl@sss.pgh.pa.us>
To: Artur Zakirov <artur.zakirov@adjust.com>
cc: Sergey Fedchenko <seregayoga@bk.ru>,
 Daniel Danzberger <daniel@dd-wrt.com>,
        Andres Freund <andres@anarazel.de>, Sergei Kornilov <sk@zsrv.org>,
        pgsql-hackers@postgresql.org, Peter Eisentraut <peter_e@gmx.net>,
        Marc Dean <marc.dean.jr@gmail.com>,
        Petr Jelinek <petr@2ndquadrant.com>,
        "michael.paul.powers@gmail.com" <michael.paul.powers@gmail.com>,
        Alvaro Herrera <alvherre@2ndquadrant.com>,
        Robert Welin <robert@vaion.com>
Subject: Re: BUG #15293: Stored Procedure Triggered by Logical Replication is
 Unable to use Notification Events
In-reply-to: <284756.1631467798@sss.pgh.pa.us>
References: <20190628212224.GA11418@alvherre.pgsql>
 <923262c473b494c4aec825a360d6f0499c144552.camel@dd-wrt.com>
 <1623669444.237579337@f317.i.mail.ru>
 <CAFt03JNo9=77=W6ggSjFRQ_h85BhrjNPArWd761k_KQye0HjCQ@mail.gmail.com>
 <3113945.1630956452@sss.pgh.pa.us>
 <CAFt03JMY=769Vst0nipzSTpiXG3HBf5m42cGxZqafwiO0uWDYA@mail.gmail.com>
 <284756.1631467798@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Sun, 12 Sep 2021 13:29:58 -0400"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <905636.1631656587.0@sss.pgh.pa.us>
Date: Tue, 14 Sep 2021 17:56:58 -0400
Message-ID: <905668.1631656618@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/905668.1631656618%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <905636.1631656587.1@sss.pgh.pa.us>

I wrote:
> Hence, I present the attached, which also tweaks things to avoid an
> extra pq_flush in the end-of-command code path, and improves the
> comments to discuss the issue of NOTIFYs sent by procedures.

Hearing no comments, I pushed that.

> I'm inclined to think we should flat-out reject LISTEN in any process
> that is not attached to a frontend, at least until somebody takes the
> trouble to add infrastructure that would let it be useful.  I've not
> done that here though; I'm not quite sure what we should test for.

After a bit of looking around, it seems that MyBackendType addresses
that problem nicely, so I propose the attached.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="forbid-LISTEN-in-background.patch";
	charset="us-ascii"
Content-ID: <905636.1631656587.2@sss.pgh.pa.us>
Content-Description: forbid-LISTEN-in-background.patch

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 27fbf1f3aa..bf085aa93b 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -803,6 +803,23 @@ standard_ProcessUtility(PlannedStmt *pstmt,
 				ListenStmt *stmt = (ListenStmt *) parsetree;
 
 				CheckRestrictedOperation("LISTEN");
+
+				/*
+				 * We don't allow LISTEN in background processes, as there is
+				 * no mechanism for them to collect NOTIFY messages, so they'd
+				 * just block cleanout of the async SLRU indefinitely.
+				 * (Authors of custom background workers could bypass this
+				 * restriction by calling Async_Listen directly, but then it's
+				 * on them to provide some mechanism to process the message
+				 * queue.)  Note there seems no reason to forbid UNLISTEN.
+				 */
+				if (MyBackendType != B_BACKEND)
+					ereport(ERROR,
+							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+					/* translator: %s is name of a SQL command, eg LISTEN */
+							 errmsg("cannot execute %s within a background process",
+									"LISTEN")));
+
 				Async_Listen(stmt->conditionname);
 			}
 			break;

------- =_aaaaaaaaaa0--