Trigger based replication with ENABLE REPLICA triggers and session_replication_role best practice(s)

Hello,

we have based all our replication infrastructure on a heavily hacked version of DBMirror, which now runs in a
single master (office DB) -> multiple slaves (vessels DBs) mode for 80+ slaves and about 300 tables and in
multiple masters (the same vessels DBs as above) (having partitions of the data) -> single slave (the same office db as above) mode
for just two tables.
Now we are in the process of designing a new solution which requires to have some form of multi-master functionality,
without being so much concerned about conflict resolution at this stage.
The issue that we are facing is to prevent replication data originating from a vessel DB and consumed into the office DB,
(or replication data originating from the office DB and consumed into a vessel DB) to be bounced back to the
originating server because of the invocation of the DBMirror trigger.

We have thought of :
Solution 1)
explicitly disabling the triggers at the start of the transaction and re-enabling them,
but that would require knowledge of the name of table in question prior to execution of the replication command,
but in our case this not known unless parsing the SQL file which came from the originating server.
Since the number of those multi-master tables is not large, we could explicit insert the
ALTER TABLE <tblname> DISABLE TRIGGER <tblname>_dbmirror_trig commands prior to actual SQL execution
for each table involved and then insert the respective
ALTER TABLE <tblname> ENABLE TRIGGER <tblname>_dbmirror_trig commands after the SQL execution.
However this would require hardcoding those commands into the code which runs the replication SQL,
and this has many and obvious disadvantages.

Then i looked upon :
Solution 2)
the ENABLE REPLICA TRIGGER in combination with session_replication_role.
Setting default ALTER DATABASE dbname SET session_replication_role TO REPLICA (single master + multi-master ones)
in combination with ALTER TABLE ENABLE REPLICA TRIGGER <tblname>_dbmirror_trig for *all* tables involved in replication
would allow the default behavior of INSERTS/UPDATES/DELETES to result in firing the DBmirror trigger, which would work
similar to the default way it has been running for the single direction replicated tables.
Then in the code which plays the replication SQLs for the multi-master tables we would simply set smth like :
BEGIN ;
SET local session_replication_role TO origin;
<execute SQL here>
END;
preventing the trigger to be called, and thus eliminating the bounce-back effect.

One thing that worries me is setting the database-wide session_replication_role to smth different than the default.
In our case, it would be ideal to be able to set session_replication_role to some value which would have
the complimentary effect of REPLICA, smth like e.g. NOREPLICA which would result in on-demand temporary
disablement of the triggers when leaving session_replication_role to non-REPLICA (such as the default value=origin).

One third solution would be to :
Solution 3)
make the code updating those multi-master tables, replication-aware by putting
SET local session_replication_role TO REPLICA; inside the affecting transactions. But this also has
the obvious disadvantage of making application and system logic blend together, and also
making the app programmer prone to errors.

The most elegant solution IMHO is the 2nd but i am concerned that setting the
database-wide session_replication_role to smth different than the default might just
hide some future risks.

What are your thoughts on that?

Thank you a lot for any input.
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt