Thread: BUG #15202: Unexpected behavior with trigger fired on logicalreplicaion using pg_notify

The following bug has been logged on the website:

Bug reference:      15202
Logged by:          Tomáš Chaloupka
Email address:      chalucha@gmail.com
PostgreSQL version: 10.1
Operating system:   Ubuntu 17.10 x86_64
Description:

I've setup logical replication between my nodes which works ok.
I have a REPLICA enabled trigger which send notification when some row is
inserted which works just fine when inserted on local system (so without
replication).
But when row is replicated no notification is received.
It is received when I manually issue the NOTIFY command next, then I get
notification from the replication and the new one.
Same behavior as described here:
https://stackoverflow.com/questions/48641038/notify-from-trigger-on-pglogical-replicated-table
But with built in logical replication instead of pglogical.

* PG version: "PostgreSQL 10.1 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 6.3.0-12ubuntu2) 6.3.0 20170406, 64-bit"
* source and target configurations are the same
* Installed from http://apt.postgresql.org/pub/repos/apt/ repo
* wal_level = logical set in postgresql.conf

Steps to reproduce:

On publisher instance:
create table test (log text);
create publication mypub FOR TABLE test;

On subscriber instance:
create table test (log text);
create subscription mysub CONNECTION 'postgresql://xxx' PUBLICATION mypub;

CREATE OR REPLACE FUNCTION test_notify() RETURNS trigger AS
$BODY$
BEGIN
    PERFORM pg_notify('foo', NEW.log);
    RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';

CREATE TRIGGER trig_test_notify AFTER INSERT ON test FOR EACH ROW EXECUTE
PROCEDURE test_notify();
ALTER TABLE test ENABLE REPLICA TRIGGER trig_test_notify;

Now test for local notifications:
psql: LISTEN foo;
psql: insert into test values ('bar');
INSERT 0 1
Asynchronous notification "foo" with payload "bar" received from server
process with PID 13614.

Now add row to replicate:
insert into test values ('repl');

Now back on subscriber:
select * from test;
 log  
------
 bar
 repl
(2 rows)

So data are ok, but no notification received here.

So lets invoke the new one here:
psql: NOTIFY foo, 'baz';
NOTIFY
Asynchronous notification "foo" with payload "repl" received from server
process with PID 12948.
Asynchronous notification "foo" with payload "baz" received from server
process with PID 13614.

So now it is received.

But I've expected that repl payload should be returned after previous
select.

Same behavior is experienced using my application code so it behaves same as
psql here.

I tried also to make a trigger to insert log to other table after
replication of the new row and it works as expected so only notifications
seems to be a problem.