[Bucardo-general] Foreign key consistency

[Hans van der Riet]

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