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