I have an issue with catching all the data with AFTER INSERT trigger on my PostgreSQL 16 logical replication. I simplified my tables into
create table customer (id serial, email text);
create table customer_address (id integer, city text);
create table customer_inserts (id integer, email text, city text);
The insert that is happening on primary server is basically something like this:
with customer_insert as
(insert into customer (email)
select '[email protected]'
returning id)
insert into customer_address (id, city)
select id, 'New York'
from customer_insert;
And on replica I need to catch data from both of the tables into the 3rd one but it seems that the trigger does not see the row from customer_address table. If I use some other table, where row was already there before - it works, but with left join to customer_address the column from there is null.
Here the full script with trigger: https://dbfiddle.uk/Mvo7oHRg
I used ALTER TABLE customer ENABLE REPLICA TRIGGER customer_trg; - so I know the trigger generally works but not with join to other table with data inserted in same transaction.
I even made additonal columns in both of them with now() as default to see if there is difference, but in most cases (not all!) it's the same.
Is it a nature of logical replication that data is not replicated in the same transactions as on primary? Or is there any trick with the triggers I should use?
On the primary te same trigger is working like a charm - but the objective is to make it work on replica.
synchronous_commit=remote_applychange anything? @AdrianKlaver That setting is set by the replication system. OP's trigger is correctly configured to fire in that context. The trigger in question is on the subscriber. Same one set up on the publisher "works like a charm" - mentioned at the end.