Postgres: Trigger on FOREIGN TABLE

Question

I would like to use postgres_fdw and house a FOREIGN TABLE in my database. Is it possible to define a trigger on the local server for this FOREIGN TABLE that recognizes an INSERT event on the remote server. If so, please provide an example.

---

Data Flow:

1. Insert data into table on remote server.
2. Recognize insert on local server's foreign table which fires a trigger.
3. Trigger function writes data into some other table.
4. Upon write success, post back to the foreign table

---

Idea as a crude diagram:

---

No error is reported but the write to table_b seems unsuccessful.

Here is what I've tried:

CREATE FOREIGN TABLE x.table_a   -- note the foreign table is in a different schema than the local table
( id        BIGINT           NOT NULL                   
, data_ts   TIMESTAMPTZ      DEFAULT CURRENT_TIMESTAMP
, xchg_ts   TIMESTAMPTZ      DEFAULT NULL
)
SERVER remote_server
OPTIONS (schema_name 'schema_a', table_name 'table_a')
;

CREATE TABLE y.table_b
( xchg_id    BIGINT
, error_msg  TEXT DEFAULT NULL
);

---

CREATE OR REPLACE FUNCTION func_foreign_table_a_after_insert()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO y.table_b
        (xchg_id)
    VALUES
        (NEW.id)
    ;
    RETURN NEW;
END;
$$  LANGUAGE PLPGSQL
;

CREATE TRIGGER trig_foreign_table_a_after_insert
AFTER INSERT ON x.table_a
FOR EACH ROW EXECUTE PROCEDURE func_foreign_table_a_after_insert();

Answer 1

Accoring to https://stackoverflow.com/a/64496191 this is not possible. A trigger on a foreign table will fire only when you modify data on local server, not the remote one.

By the way, the article mentioned in discussion (https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-trigger-foreign-tables/) gives an example on exactly this scenario: the data is modified through foreign table, and thus the trigger is fired.