Postgresql: trigger on foreign table to execute function to truncate/insert into local table

Question

I would like to create trigger to execute function to truncate local database table and insert new data. Trigger execution must start after new row have insert in foreign database table. I have read a lot about creating triggers on foreign table, but for me its not working. Trigger seems to not execute function when new row will be inserted in foreign table. It seems like trigger cant see this new row insert event. What I did:

1. Created foreign table in my local database, lets call it 'foreign_table'. I tested, I can read data.

2. Created function to truncate local table and insert new data:

   CREATE or replace FUNCTION public.reset_insert_table()
   RETURNS TRIGGER
       LANGUAGE 'plpgsql'
       SET search_path=public
    AS $BODY$
   BEGIN
       create temporary table temporary_table_tmp
       as select * from public.table1;
       TRUNCATE TABLE public.table2;
       insert into table2
           select * from temporary_table_tmp;
       DROP table temporary_table_tmp; 
   END;    
   $BODY$;
   

3. Created trigger to launch function 'reset_insert_table()'

   CREATE TRIGGER local_table_update
       AFTER INSERT
       ON 'foreign_table'
       FOR EACH ROW EXECUTE PROCEDURE reset_insert_table();
   

4. Made test: inserted new row in foreign database table 'foreign_table', but I cant see that table is truncated and new data is not inserted. Insertion to foreign_tale was done in foreign database. Problem was also testing does this trigger function work, executing manually will produce error: EXECUTE PROCEDURE reset_insert_table(); ERROR: syntax error at or near "execute" Tried also CALL and SELECT. I created same function for testing but instead defining 'RETURNS TRIGGER'used 'RETURNS VOID' and function is working.

Can anyone tell why my solution is not working and does trigger on foreign tables must see events happening in foreign tables?

Answer 1

According to your comments, you seem to be using logical replication.

While data modifications are replayed on the standby with logical replication, the parameter session_replication_role is set to replica to keep triggers and foreign key constraints from working.

If you want a trigger to be triggered by the replay of data via logical replication, you have to declare it as a replica trigger:

ALTER TABLE a2 ENABLE REPLICA TRIGGER trigger_name;