Обсуждение: Logical replication and AFTER UPDATE triggers [PG 16]

After various iterations of logical on PG 15, I bit the bullet and
installed PG 16. (Using the bookworm-pgdg repository.) Turns out, that
basically solved all the problems I'd been having previously - yay!

Got a bit of a curveball thrown at me though. I have a singleton
settings table (see other thread; I added a primary key to it to make
replication reliable) and it has a trigger on it:

create or replace function send_settings_notification() returns
trigger language plpgsql as $$begin perform
pg_notify('stillebot.settings', ''); return null; end$$;
create trigger settings_update_notify after update on
stillebot.settings execute function send_settings_notification();
alter table stillebot.settings enable always trigger settings_update_notify;

Updating the table (even to the same value as it currently has)
correctly notifies any listening clients on the *same* database
instance. However, the replicated node does not fire off a
notification. For testing purposes I have four clients - running on
Sikorsky connected to Sikorsky, on Sikorsky connected to Gideon, on
Gideon connected to Sikorsky, on Gideon connected to Gideon - and any
edit made on Sikorsky sends notifications to the two that are
connected to Sikorsky, and any edit made on Gideon sends notifications
to the two connected to Gideon. According to
https://www.postgresql.org/docs/current/sql-altertable.html this would
be the default behaviour, but with "enable always trigger", it should
fire on both ends, right?

The changes ARE getting replicated out - querying the table on either
end shows that data is flowing correctly - so it's just the trigger.

What's the correct way to use NOTIFY triggers in a replicated system?

ChrisA

On Fri, 2 Feb 2024 at 13:20, Chris Angelico <rosuav@gmail.com> wrote:
> create or replace function send_settings_notification() returns
> trigger language plpgsql as $$begin perform
> pg_notify('stillebot.settings', ''); return null; end$$;
> create trigger settings_update_notify after update on
> stillebot.settings execute function send_settings_notification();
> alter table stillebot.settings enable always trigger settings_update_notify;
>

Ah ha! A discovery. It may be that a FOR EACH STATEMENT trigger (which
is the default) does not fire on the subscriber. Converting to FOR
EACH ROW seems to make this function. Does this seem reasonable? I
can't find anything in the docs that confirms it.

ChrisA