Re: unexpected behavior with pglogical -- bug?

Exactly same situation here. If I LISTEN for notifications, no notification
is sent by subscriber when changes are received from replication. However,
any local change makes all pending notifications be sent in addition to the
corresponding to the local change.

I have tested it on Postgres 12.1

El jue., 27 feb. 2020 a las 15:29, Achilleas Mantzios (<
achill(at)matrix(dot)gatewaynet(dot)com>) escribió:

> Hello
>
> have you tried the same with logical replication in postgresql >= 10?
>
> On 16/6/19 11:45 μ.μ., Torsten Förtsch wrote:
>
> Hi,
>
> out of curiosity I created the following setup, all with 9.6 and pglogical.
>
> D1 is configured as provider with a replication set that contains only 1
> table. Only inserts are replicated.
>
> D2 is configured as subscriber for that replication set. Replication
> works, all inserts on D2 arrive also on D2.
>
> Now, I add the following always firing trigger to the table:
>
> CREATE OR REPLACE FUNCTION notify.trgfn () RETURNS trigger AS $def$
> BEGIN
> PERFORM pg_notify(NEW.channel, NEW.msg);
> RETURN NULL;
> END
> $def$ LANGUAGE plpgsql;
>
> CREATE TRIGGER trg BEFORE INSERT ON notify.notify
> FOR EACH ROW
> EXECUTE PROCEDURE notify.trgfn();
>
> ALTER TABLE notify.notify ENABLE ALWAYS TRIGGER trg;
>
> As you can see, the trigger function should prevent the actual insert and
> only call pg_notify(). In principle this works but there is a catch.
> Notifications generated this way are only delivered after another
> notification genuinely generated on the subscriber node. The channel of
> this notification does not matter. If I replace PERFORM pg_notify() by RAISE
> NOTICE I see the message immediately in the log.
>
> First I thought this is related to session_replication_role=replica. So,
> I tried the direct insert on D2 with this setting using psql. The
> notification was fired immediately. Also, whether the trigger prevents or
> allows the actual insert does not matter. I tried to create the trigger
> function as SECURITY DEFINER and with a specific search_path. That didn't
> help either.
>
> By now I am thinking there must be something missing in pglogical.
>
> Thanks,
> Torsten
>
>
>
> --
> Achilleas Mantzios
> IT DEV Lead
> IT DEPT
> Dynacom Tankers Mgmt
>
>