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