10

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();
J Spratt
  • 1,762
  • 1
  • 11
  • 22
  • @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. – J Spratt Jun 05 '19 at 20:02
  • I've edited the question to explicitly state where the `INSERT` is happening. – J Spratt Jun 05 '19 at 20:26
  • 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. – Laurenz Albe Jun 06 '19 at 05:56
  • 2
    I might be interpreting things wrong, but this link indicates this should work https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-trigger-foreign-tables/ – Jonathan DS Dec 11 '19 at 18:23
  • Interesting. I'll have to take another look at that. Thank you for the link – J Spratt Dec 19 '19 at 16:06
  • 1
    @JSpratt Did you have any success? I've found myself in the same situation – Zdenek F Apr 08 '20 at 12:25
  • @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. – J Spratt Apr 08 '20 at 18:10
  • @JSpratt got it, thanks for the reply. I've found out Postgres supports logical replication from v10 onwards (https://www.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. – Zdenek F Apr 09 '20 at 08:29
  • @JonathandosSantos I've run into this same issue and while https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-trigger-foreign-tables/ says it can be done since Postgres 9, I cannot get it to work. – Liquidgenius Aug 04 '20 at 13:07
  • @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. – Liquidgenius Aug 05 '20 at 13:52
  • @michaelpq Thoughts? – Liquidgenius Aug 05 '20 at 14:03
  • @Liquidgenius 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. – J Spratt Aug 05 '20 at 17:17
  • @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. – Liquidgenius Aug 07 '20 at 21:46
  • Did someone actually made this work? A trigger on the foreign table still doesnt work for me. I followed this guide: https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-trigger-foreign-tables/ – phips28 Jul 23 '22 at 21:55
  • @phips28 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. – J Spratt Jul 25 '22 at 13:06

1 Answers1

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.

Sergey Myasnikov
  • 21
  • 2