Hans van der Riet wrote:
Is there any way to avoid / resolve this type of inconsistency?
After doing some reading, I think Bucardo's design may break referential
integrity in multi-master replication.
This is how I understand Bucardo works: It sets session_replication_role
to 'replica' in Postgres. When it syncs, it deletes changed rows from
all other dbs and copies them over from the source.
Even if you have all relevant tables in the same sync this causes a
problem in the following scenario. An insert that references a row in
the first database happens at (around) the same time as that row is
deleted in the second database. When replication kicks in:
- there is no conflict because different rows changed in both databases
- the new row is inserted in the second database succesfully, the
existence of the referenced row is not checked (because
session_replication_role is 'replica' )
- the referenced row is deleted successfully in the first database
without deleting the new row, the foreign key constraint doesn't block
or cascade (because session_replication_role is 'replica')
So referential integrity is broken and you end up with a new row
referencing a non-existing one.
Are my assumptions correct?
If so, maybe it is viable to ensure integrity by creating some triggers
(before delete on the referenced tables) that are configured as ENABLE
REPLICA, so they are executed during sync. Obviously this will come at
some performance cost.
Kind regards,
--
Hans van der Riet
_______________________________________________
Bucardo-general mailing list
[email protected]
https://mail.endcrypt.com/mailman/listinfo/bucardo-general
