[Bucardo-general] Automatic Cascading Replication

[David E. Wheeler]

Fellow Bucardoans,

Over the last couple of days, Greg and I made a number of commits to Bucardo 5 to restore the "makedelta" functionality that has been in Bucardo 4 for a while. Unlike in Bucardo 4, it has the added benefit that, on PostgreSQL 8.3 and higher, other syncs are automatically kicked when deltas are created, just as if a target table was directly inserted into. This is great, and will allow one to, for example, have both a multi-master replication sync on a table *and* have a separate source/target sync to yet another database and have it just work.

But I think we can do better.

First, I'm not sure that tracking should be on a dbgroup-basis, but on a db-basis. This would allow one to have two multi-master replication setups on a table and yet have the data in question replicated by only one of those syncs. (Or might that create a race-condition such that they would probably both copy the data anyway?)

But more fundamentally, from a usability point of view, I think we can mostly eliminate the need for the "makedelta" parameter by making it automatic. The rule would be:

When a sync is added or updated, we would iterate over its list of tables. For each table:
  + If it is the source for more than one sync, makedelta is on
  + Otherwise it is off

There is the case where one might have more than one Bucardo doing replication against a database, in which case we would not know about the syncs of the *other* database. To enable that scenario, there should still be a switch to always turn "makedelta" on or off.

Therefore, I propose to replace the table "makdelta" parameter with a new one, "cascade" or "cascade_mode", with three possible values: "auto" (the default), "on", and "off".

I also propose to remove the replication code that inserts the deltas and instead to just enable or disable the triggers based on the cascade requirement for a table.

As an aside: Ideally, when a table is being replicated with makedelta enabled, the deltas would be created, but the autokick, if there is one, is *not* fired. Any other sync autokicks would be. In practice this probably won't work, as enabling or disabling individual triggers requires an ALTER TABLE statement, which probably is not practical while replicating. (Our current design has them *always* fire, which creates a bit more noise, but data is not copied more than once.)

Anyway, that's my proposal:

* Replace "makedelta" with "cascade" and default to "auto"
* Let the triggers do the work as much as possible
* Try to find a way to prevent duplicate replication when syncs sync the same tables

Comments? I've little doubt that I've overlooked or misunderstood a thing or 30.

Best,

David