PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15293
PG Version10.4
OSUbuntu 18.04 Desktop x64 - Linux 4.15.0-29-generic
Opened2018-07-24 12:13:34+00
Reported byMichael Powers
StatusOpen

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15293
Logged by:          Michael Powers
Email address:      (redacted)
PostgreSQL version: 10.4
Operating system:   Ubuntu 18.04 Desktop x64 - Linux 4.15.0-29-generic
Description:        

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.

Steps to Reproduce:
Set up Master:
CREATE TABLE test (id SERIAL PRIMARY KEY, msg TEXT NOT NULL);
CREATE PUBLICATION testpub FOR TABLE test;

Set up Replica:
CREATE TABLE test (id SERIAL PRIMARY KEY, msg TEXT NOT NULL);
CREATE SUBSCRIPTION testsub CONNECTION 'host=192.168.0.136 user=test
password=test' PUBLICATION testpub;
CREATE OR REPLACE FUNCTION notify_channel() RETURNS trigger AS $$
BEGIN
    RAISE LOG 'Notify Triggered';
    PERFORM pg_notify('testchannel', 'Testing');
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
DROP TRIGGER queue_insert ON TEST;
CREATE TRIGGER queue_insert AFTER INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE notify_channel();
ALTER TABLE test ENABLE ALWAYS TRIGGER queue_insert;
LISTEN testchannel;

Run the following insert on the master:
INSERT INTO test (msg) VALUES ('test');

In postgresql-10-main.log I get the following:
2018-07-24 07:45:15.705 EDT [6701] LOG:  00000: Notify Triggered
2018-07-24 07:45:15.705 EDT [6701] CONTEXT:  PL/pgSQL function
notify_channel() line 3 at RAISE
2018-07-24 07:45:15.705 EDT [6701] LOCATION:  exec_stmt_raise,
pl_exec.c:3337

But no listeners receive the message. However if an insert is run directly
on the replica:
# INSERT INTO test VALUES (99999, 'test');
INSERT 0 1
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 6701.
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 6701.
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 6701.
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 6701.
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 6701.
Asynchronous notification "testchannel" with payload "Testing" received from
server process with PID 9992.

Backed up notifications are received for previous NOTIFY's.

Messages

DateAuthorSubject
2018-07-24 12:13:34+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 13:19:45+00Sergei KornilovRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 15:58:29+00Andres FreundRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 17:43:30+00Marc DeanRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 18:22:18+00Sergei KornilovRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 19:27:40+00Andres FreundRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 19:33:36+00Michael PowersRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 21:43:30+00Tom LaneRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 21:56:04+00Andres FreundRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 22:01:33+00Tom LaneRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2018-07-24 22:06:30+00Andres FreundRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2019-02-22 16:37:50+00Robert WelinRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2019-02-28 01:01:16+00Marc DeanRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2019-06-28 21:22:24+00Alvaro HerreraRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2019-08-29 15:03:22+00Alan KleimanRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events
2020-06-23 09:40:53+00Daniel DanzbergerRe: BUG #15293: Stored Procedure Triggered by Logical Replication is Unable to use Notification Events