Re: BUG #16481: Stored Procedure Triggered by Logical Replication is 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(at)postgresql(dot)org> wrote in
> The following bug has been logged on the website:
>
> Bug reference: 16481
> Logged by: Fabio Vianello
> Email address: fabio(dot)vianello(at)salvagninigroup(dot)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