How to avoid infinite loop with triggers between databases using postgres_fdw?

Question

schema of databases

I want to synchronize 3 Postgresql databases across a central pivot db.

For example, if I insert a row in DB1, it sends a query to pivot db with the extension postgresql_fdw and it sends insert query to db2 and db3. I have created 3 triggers with after insert in each database.

The problem: if I insert in db1, the pivot send this query to db2 and db3 which fire their trigger to insert in db2 and db3 in return. Infinite loop :). How can I solve this problem?

Answer 1

Normally, you can check the nesting level with pg_trigger_depth().

Like:

CREATE TRIGGER my_sync_trigger
BEFORE INSERT ON my_table
FOR EACH ROW 
WHEN (pg_trigger_depth() < 1)    -- cancel nested trigger invocation!
EXECUTE PROCEDURE my_sync_function();

• How to prevent a PostgreSQL trigger from being fired by another trigger?

But I have not tested this with postgres_fdw across databases. I doubt it works transparently across databases. You'll have to test ...

A poor man's solution would be to add a boolean flag replicated to each table, set it to true when the row is replicated, and only fire the replication trigger when it's not true.

...
WHEN (NEW.replicated = false)    -- cancel for replicated rows
...

But I can see all kinds of concurrency issues with this in a multi-user environment.

Have you considered one of the proven replication solutions? Find a list in the manual here.