On Fri, Sep 20, 2013 at 11:02:48PM +0200, Hans van der Riet wrote: > - 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.
Yes, this is correct, and a known limitation. There are some ways around it: * Create some triggers, as you mention * Sync often to reduce the window it can happen in * Have Bucardo do it with some custom code * Have your app only make such updates on one side * Wait for Bucardo to get smarter. For that last bullet, it is very tricky but doable - there is some starting code already in B5 to handle it. Basically, Bucardo will examine all of the existing foreign keys and make sure it is not doing anything to violate it when doing a sync. -- Greg Sabino Mullane [email protected] End Point Corporation PGP Key: 0x14964AC8
signature.asc
Description: Digital signature
_______________________________________________ Bucardo-general mailing list [email protected] https://mail.endcrypt.com/mailman/listinfo/bucardo-general
