Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>,
	pgsql-general@postgresql.org, <pgreplication-general@svr3.postgresql.org>
Subject: Re: [SQL] Conditional row grained + FK dependency oriented lazy
	replication
Date: Mon, 29 Sep 2003 10:40:55 -0700
User-Agent: KMail/1.4.3
References: <Pine.LNX.4.44.0309291817180.4381-100000@matrix.gatewaynet.com>
In-Reply-To: <Pine.LNX.4.44.0309291817180.4381-100000@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
Message-Id: <200309291040.55994.josh@agliodbs.com>

Achilleus,

> first off, i broadcast my case to as many people as possible,
> maybe off topic for many, but maybe as well of interest for some.

Also, I'm not sure what mailing list is appropriate.  Probably General.

> Also i speak sort of DBMirror terminology ( a great and simple tool).
> The problem is as follows:
> Tables must be replicated to remote sites (1 master, many slaves).
> The tables are classified as
> 1) Non replicated (nothing is specified for them, and eventually nothing
> is done)
> 2) Replicated to all slaves
> 3) Conditionally replicated, so that a row is replicated only if it
> matches some criteria (usually a comparison of a column value against a
> remote site ID)

You're talking about a fairly complex replication setup, over low bandwidth.    
For that, I would expect to create custom code to carry it out -- packaged 
solutions simply can't be optimized for anything as particular as this.

> 4) Implicitly replicated. This is the hard case. Nothing is explicitly
> declared for those tables, but the necessity of replicating them
> comes from the FK constraints that tables in case 3) point to.
> The problem with those tables is that we cannot know in advance if any
> future record in some table of case 3) will point to a record currently
> getting inserted into the db, and also there is a need for accounting
> info for those tables, since we want to know whether they got inserted
> at a remote site or not.

Were it my project, I would replicate *all* updates for the FK tables, becuase 
of the impossibility of knowing what records would be needed in the future.   
Further, I would place some tight constraints on how and how much those 
tables could be updated as a way of controlling the size of the replication 
updates.  Of course, segregating those referral records which could not 
possibly be used by certain sites would also restrict bandwitdh, but you've 
thought of that, I'm sure.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco