Thank you for your answer.

Let me first describe our setup :
1) One Office Database is our central system and is physically located at our data center,
For the majority of the replicated tables (~300) this database acts as the master and the Vessels
described below, as slaves.
2) Many (80+) Vessel databases, which are subsets (+ some local data) of the Office Database,
and are physically located at vessels(ships) at sea. For only two tables those databases 
act as masters and the Office database as slave.

At the Vessels' side, the tables are categorized in:
1) majority of tables (about 300) are slave tables (Vessel acting as slave), no trigger there, 
Vessels passively replay replication SQL data generated by the Office (via exchange of SQL files)
2) two tables for which the vessel act as the master (dbmirror trigger exists only on vessel, the office
behaves as slave for those two tables)
3) two new tables which we must configure as multi-master, (dbmirror triggers must exist both at Vessel
and at the Office)

It is this case 3) that we must deal with. At the vessel side (we don't care for the Office for the moment) 
we want to eliminate bounce back while reading SQL from the master, therefore we would like to allow
triggers operating freely as by default on all occasions except in the program which reads and executes
SQL generated and sent by the Office.


On Wednesday, November 21, 2012 04:24:53 PM Greg Sabino Mullane wrote:
> On Wed, Nov 21, 2012 at 04:57:32PM +0200, Achilleas Mantzios wrote:
> > Solution 2)
> > the ENABLE REPLICA TRIGGER in combination with session_replication_role.
> > Setting default ALTER DATABASE dbname SET session_replication_role TO REPLICA
> 
> Not good. Pretty dangerous unless you are 100% sure that every application 
> logging into the database is aware of that setting. That's backwards of the 
> optimal solution:

I thought it was the opposite. By 
1) setting the database-wide session_replication_role to REPLICA
2) Setting all existing non-replication-related triggers as ENABLE ALWAYS 
3) Setting all replication triggers (the existing two(single-master) + the two new multi-master ones) AS ENABLE REPLICA
4) Touch only one system program (the one which executes SQL from the master), and SET local session_replication_role TO origin , 
5) Leave all other apps untouched

I think I can have the correct behaviour, without ever touching the applications at all.
The apps need not be aware of any setting.

Now that i read your answer again, I think i did not manage to make myself clear enough,
hence you might have thought a complimentary situation than the one we really have.

> 
> > 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.
> 
> Well, this is exactly the use case that session_replication_role was 
> designed for, so I would prefer to see stronger reasons for 
> rejecting it.

Intuitively thinking, and following this thread/post by Chris Browne : http://archives.postgresql.org/pgsql-general/2008-04/msg00786.php
i would suppose that tables with triggers configured as ORIGIN or LOCAL imply tables 
for which the server acts as prime master/origin, while tables with triggers configured 
as REPLICA imply tables residing in a system acting as slave.

If (by solution 3) i left the Vessel configuration at its defaults, and only configure the triggers for those 
two new multi-master tables as ENABLE REPLICA, then i might get away with leaving the 
program which reads replication SQL from the Office intact, but i pay the price of having
to force all apps to issue : SET local session_replication_role TO REPLICA;

So with solution 2) i have to change all existing triggers + change one single program 
(the one which reads the SQL files from the Office), 
while with solution 3) i would have to worry about all applications dealing with those tables.

Am I missing something? 

> 
> /twocents
> 
> 
> 
-
Achilleas Mantzios
IT DEV
IT DEPT
Dynacom Tankers Mgmt
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20121122/45900f66/attachment.htm