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();

Barbour

@LaurenzAlbe My concern is that my local database does not have visibility to the INSERT event on the remote server and therefore my trigger never captures the event. – Barbour

I've edited the question to explicitly state where the INSERT is happening. – Barbour

Thanks, now I understand. That is of course not possible, because the local database does not know when an INSERT on the remote table happens. You could create a trigger on the remote table that inserts into the local table via foreign data wrapper. – Denumerable

I might be interpreting things wrong, but this link indicates this should work paquier.xyz/postgresql-2/… – Adrenocorticotropic

Interesting. I'll have to take another look at that. Thank you for the link – Barbour

@JSpratt Did you have any success? I've found myself in the same situation – Jereme

@ZdenekF I think I ended up duplicating the table in the local database and writing a bash polling script to do the transfers. I was under a time constraint when I wrote this question but I still would like to go back and give it another attempt at some point. – Barbour

@JSpratt got it, thanks for the reply. I've found out Postgres supports logical replication from v10 onwards (postgresql.org/docs/12/logical-replication.html), so that's what I plan to set up, even though we'll have to migrate the customer from 9.4 first. – Jereme

@JonathandosSantos I've run into this same issue and while paquier.xyz/postgresql-2/… says it can be done since Postgres 9, I cannot get it to work. – Mackay

@JSpratt I was able to work around this issue by mapping foreign tables to a staging schema, then creating normal tables using AS SELECT * FROM and WITHOUT DATA from the staging foreign tables and dropping the staged foreign tables afterwards. On the foreign tables I installed triggers that use DBLINK to write to the local normal tables, then installed triggers on the local tables. It works as expected. Only caveat is that access to the foreign server is required and may be negated with database upgrades. – Mackay

@michaelpq Thoughts? – Mackay

@Mackay So ultimately you're not using the foreign tables at all? The question I have is, "what if you're not granted create/alter rights on the remote table"? For example, I may not have permission to add a trigger on the remote table but I could on the foreign table defined locally that represents it. – Barbour

@JSpratt Technically I am using the foreign tables to provide DDLs for regular tables, so I am using them as an intermediary step. You are correct in that this solution fails without access to the remote tables. After 3 days of trying to get triggers to fire on foreign tables I gave up and used the remote access. Not optimal, but gets the job done for now. – Mackay

Did someone actually made this work? A trigger on the foreign table still doesnt work for me. I followed this guide: paquier.xyz/postgresql-2/… – Retardment

@Retardment I haven't gotten an opportunity to go back and retry but as far as I know, no one has gotten this to work in the way it's setup in this question. – Barbour

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.

Bascom

Answer 2

It looks it's not directly supported. As far as I understand - it would require writing (in the backend of fdw) a service with access to pg database which would call some stored procedure emulating the "remote trigger". Still You will have a problem with method of detection of those "inserts" (as data is external). Technically would implement it on Multicorn in Python.

Slipway