Re: BUG #16481: Stored Procedure Triggered by Logical Replicationis Unable to use Notification Events

Hello.

It seems to me a bug.

At Fri, 05 Jun 2020 11:05:14 +0000, PG Bug reporting form <noreply@postgresql.org> wrote in 
> The following bug has been logged on the website:
> 
> Bug reference:      16481
> Logged by:          Fabio Vianello
> Email address:      fabio.vianello@salvagninigroup.com
> PostgreSQL version: 12.3
> Operating system:   Windows 10
> Description:        
> 
> About the bug BUG #15293, on PostgreSQL version 10.4 and 11.2 as describe
> below, we found the same issue on the PostgreSQL version 12.3.

The HEAD behaves the same way.

> Is it a feature? 
> Becasue in the documentation we didn't found any constraint that says that
> we can not use NOTIFY/LISTEN on logical replication tables.
> 
> "When using logical replication a stored procedure executed on the replica
> is
> unable to use NOTIFY to send messages to other listeners. The stored
> procedure does execute as expected but the pg_notify() doesn't appear to
> have any effect. If an insert is run on the replica side the trigger
> executes the stored procedure as expected and the NOTIFY correctly
> notifies
> listeners.

The message is actually queued, but logical replication worker doesn't
signal that to listener backends. If any ordinary session sent a
message to the same listener after that, both messages would be shown
at once.

That can be fixed by calling ProcessCompletedNotifies() in
apply_handle_commit. The function has a code to write out
notifications to connected clients but it doesn't nothing on logical
replication workers.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center
From b8df8c0cf6ae6c2bcd78ffd7d9bd629f51ab3bee Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyoga.ntt@gmail.com>
Date: Mon, 8 Jun 2020 16:07:41 +0900
Subject: [PATCH] Signal notifications from logical replication workers

Notifications need to be signaled to listeners but logical replication
worker forgets to do that. Fix that by signaling notifications after
committing a transaction.
---
 src/backend/replication/logical/worker.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index a752a1224d..28ae89c574 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -33,6 +33,7 @@
 #include "catalog/pg_inherits.h"
 #include "catalog/pg_subscription.h"
 #include "catalog/pg_subscription_rel.h"
+#include "commands/async.h"
 #include "commands/tablecmds.h"
 #include "commands/trigger.h"
 #include "executor/executor.h"
@@ -517,6 +518,9 @@ apply_handle_commit(StringInfo s)
         pgstat_report_stat(false);
 
         store_flush_position(commit_data.end_lsn);
+
+        /* Send out notify signals */
+        ProcessCompletedNotifies();
     }
     else
     {
-- 
2.18.2