[Bucardo-general] Foreign key consistency

[Greg Sabino Mullane]

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 greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 163 bytes
Desc: Digital signature
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20130921/1c02b2b5/attachment.sig>