Hello.
It seems to me a bug.
At Fri, 05 Jun 2020 11:05:14 +0000, PG Bug reporting form
<nore...@postgresql.org> wrote in
> The following bug has been logged on the website:
>
> Bug reference: 16481
> Logged by: Fabio Vianello
> Email address: fabio.viane...@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....@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
