Re: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events - Mailing list pgsql-hackers

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

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;