[Bucardo-general] scenario advice

[Grant Maxwell]

Hi there

Im new to bucardo but am pleased at what it is doing for me already. I  
have a scenario that I would like to ask advice on.

I have three (or more) databases arranged as (using my terminology)  
Central, Tower1 and Tower2. The Towers create message records that are  
replicated back to the Central.
The towers share a table (called table A). They both update and insert  
records in their version of table A, and table A needs to be  
replicated between them. The primary key is kept unique by assigning  
sequential numbers from a different range on each tower for inserts.

records are created in Table B and are replicated to Central from each  
tower but not replicated between towers.

a field (F1) in table B is a foreign key (FK) reference to the primary  
key (PK) of Table A.

There is a 2nd field (F2) in Table A that is unique by constraint.

This is all about field F2. Each database has to have exactly one  
record in table A for unique value of F2. F1 is just an integer  
sequential Primary Key and provides glue between table B records and  
table A as a Foreign key. F2 must be consistent across all versions on  
all databases, but F1 must remain consistent (if it already exists) to  
the local database context. Try to put this into english, a book can  
have a different reference number(F1) in multiple shops but has to  
have the same title(F2).

Scenario:
	 A new message record is created on Tower 1 for table B. There was no  
Table A record for field F2 so one was created. The table B and table  
A records need to replicate to Central, and table A records need to  
replicate to Tower 2 (and other towers).

Potential Conflict: (1)
	Central might have a record in table A for unique field F2 but with a  
different PK value in F1
		This means Central has other messages which refer back to its  
version of the Table record via foreign key, but the new Table B  
record which will also be replicated
		to Central will have the same F2 field but a different F1 field.
		Notes:
			It does not matter if the table B records on Central and Tower  
servers do not match for the foreign key, so long as they remain valid  
in the context of their
			own database.
Proposed solution:
		1. use a custom routine before the record is replicated to central  
to check for a conflict on the 2nd field and adjust the outgoing  
message record
		 to point at the Central  version of table A record PK, and
		2. do not replicate the table A record from tower to central.
Question:
		Is it possible in a bucardo conflict management script to determine  
if the conflict was about duplicate values in field F2.

Potential conflict (2)
		Tower 1 Table A records need to be replicated to Tower 2 (and Tower  
3 etc). Conflict resolution is tricky because when field F2 conflicts  
(this happens a lot),
		there are records in Table B of each tower that depends on the local  
F1 FK  version of the table A record.
Proposed solution:
		1. use a conflict custom routine that uses UPDATE to synchronize the  
detail fields to be the same on each tower, but on each tower keep its  
own PK value for both
		fields F1 and F2. This will assure that each tower knows about the  
existence of a Field F2 record but uses a local version of F1 PK to  
maintain the local FK
		integrity, and
		2. do not replicate the table A record between towers.

Because of the work distribution across the towers, these conflicts  
will happen a lot, being created by race conditions.

Questions

1.  Anyone think this might be done better or differently ?
2. Is there a way to create a one to many replication so that each  
tower will replicate to every other tower (kind of a fully meshed  
replication)?


thanks for you input folks


Grant

> P please consider the environment before printing this e-mail
>
The information in this e-mail is confidential and may be legally  
privileged. It is intended solely for the addressee. Access to this e- 
mail by anyone else is unauthorised. If you have received this  
communication in error, please notify us immediately by return e-mail  
with the subject heading "Received in error" or telephone +61 2  
80050094, then delete the email and destroy any copies of it. If you  
are not the intended recipient, any disclosure, copying, distribution  
or any action taken or omitted to be taken in reliance on it, is  
prohibited and may be unlawful. Opinions, conclusions and other  
information in this e-mail and any attachments that do not relate to  
the business of the maXan are neither given nor endorsed by it.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20090818/43d0bcb4/attachment.html