[SOLVED] Logical Replication does not Fire FOR EACH STATEMENT Trigger only FOR EACH ROW

Logical Replication does not Fire FOR EACH STATEMENT Trigger only FOR EACH ROW

I'm using logical replication and managed to have the trigger fire when I have the following:

CREATE TRIGGER set_updated_time_trig
    AFTER INSERT OR UPDATE OR DELETE ON test
    FOR EACH ROW EXECUTE FUNCTION set_updated_time();

alter table test enable always trigger set_updated_time_trig;

I tried with ALWAYS -> REPLICA too.

But when I use for each STATEMENT, it is no longer working. I prefer to use for each statement since I can add also OR TRUNCATE.

CREATE TRIGGER set_updated_time_trig
    AFTER INSERT OR UPDATE OR DELETE ON test
    FOR EACH STATEMENT EXECUTE FUNCTION set_updated_time();

How can I have a trigger that is fired on Truncate?

Solution

That can never work, because it is rows that are replicated, not statements. If you run an UPDATE that changes ten rows, the standby will modify those ten individual rows. Contrariwise, if the UPDATE changes no row, nothing will be replicated. So it wouldn't make sense to execute statement level triggers on the standby, because they wouldn't work differently from row level triggers.

The exception to the above is the synchronization phase when you start logical replication, when the table data get synchronized. This is implemented using the COPY machinery, which triggers bot row-level and statement-level INSERT triggers.