how to properly prevent delays from after insert triggers that try to insert data to foreign tables that are not accessible

Question

I use PostgreSQL 11.

I use the postgres_fdw extension to insert rows to a table on a different server.

So I created a foreign table.. lets call it foo, and I have an AFTER INSERT trigger that executes a function that inserts that row to the foreign table on the configured remote server.

If the server is not responding, the trigger freezes until timeout.

So I have several questions regarding this issue:

1. can the query execution return to the client before the after insert is executed, so it won't block the user from executing the other inserts

2. can the system check the validity of this server and if it can't connect to it to flag it for a couple of minutes so it won't even try to send the inserts to it?

In general the inserts are sent to a backup server, if the server is not responding or it takes too much time to respond it shouldn't have any affect on the client.

So I don't want the after insert to delay the row to actually being inserted to the table and I don't want any connection related issues to cause any delays.

How can I implement such a thing ?

I'll create an example code so you'll understand what I'm doing.

Let's say foo is my foreign table.

CREATE OR REPLACE FUNCTION after_insert() RETURNS TRIGGER AS $after_insert$
DECLARE
    res BOOL;
BEGIN
    INSERT INTO foo(bar1,bar2,bar3) values(foo1,foo2,foo3);
    EXCEPTION WHEN OTHERS THEN  raise NOTICE '% %', sqlstate, sqlerrm;
    RETURN NEW;
END;
$after_insert$ LANGUAGE plpgsql;


CREATE TRIGGER after_insert AFTER INSERT
    ON my_table FOR EACH ROW EXECUTE PROCEDURE after_insert();

Answer 1

I came to understand that using after insert triggers to add complex data to other tables is bad practice. i want the row to be inserted even if the after trigger fails, and I really didn't want the after insert trigger to delay the insert.

so this was not the way to go!

instead I created a cron job that runs on the new rows every couple of minutes.

I understand now after a lot of research that if any triggers that I want to create do not really affect the row that i insert and should not delay it.. i should not use triggers. which is now sounds very reasonable :)

thanks you all! :)