Conditional row grained + FK dependency oriented lazy replication

[Achilleus Mantzios]

Hi,
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 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)
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.

Imagine we have a table parts with part
numbers,descriptions,manuals,etc....
Now we have lets say 1 master db in the computer center, and many slaves
for each retailer shop we have.
Each shop maintains an inventory of items,supplies,etc...
Each item has a FK to a part. (instance of a part).
Now we dont want the huge ammount of parts to be replicated
to each remote site, but only those parts, whose partno *will* be
referenced by an item for a specific remote site.
(That is we want to lazilly simulate the actions of a replication trigger
as if we
knew in advance that this part will be referenced by an item
in the remote site).

All i currently am aware off is DBMirror (i took a look at erserv,
which didnt seem that spectacular), and i'd like to know
if people have faced or solved this problem.

In asynchronous situations where bandwidth cost is of primary concern
(e.g.dial-up through satellite), i think that replicating useless data
is a big loss.

Any comments?

Thanx.

--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: [phone]
fax: [phone]
email: achill ( at ) matrix ( dot ) gatewaynet ( dot ) com
mantzios ( at ) softlab ( dot ) ece ( dot ) ntua ( dot ) gr


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Josh Berkus]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings