[Bucardo-general] How to ignore 'duplicate key value violates unique constraint' exceptions

[Sijo Pappachan]

Thanks Michelle.
Seems like the first case you explained with the Name tables is the issue causing the syncs to error out on unique constraint violation.i.e the out of sync case
As the nodes are remotely located, the network lags or disconnects causes the nodes to be out of sync.And when it tries the sync, once all the nodes are online, I guess it encounters such a scenario.
What would you suggest as the best way to tackle such cases, where network lags or disconnects could jeopardize the syncs?
Thanks,~Sijo

> Date: Mon, 5 Jan 2015 12:45:42 +0100
> From: michelle at sorbs.net
> To: sijo.pappachan at outlook.com
> CC: bucardo-general at bucardo.org
> Subject: Re: [Bucardo-general] How to ignore 'duplicate key value violates unique constraint' exceptions
> 
> Sijo Pappachan wrote:
> > Each of the master nodes at geographically remote sites cater to local
> > inserts.
> > So I am imagining maybe there were similar inserts from any of the
> > 2 separate nodes at the same time 
> > and the syncs trying to resolve to the latest, fails on the unique
> > constraint.
> >
> > Like a text entry with unique constraint value 'fruit::apple::red' was
> > created on different nodes, and the sync tries to replicate
> > the same to the other master and finds it already existing. 
> >
> > Just thinking out loud.
> > Let me know if I am missing anything.
> 
> You are missing something ...  to get 'uniq constraint violation' means
> you have a uniq index (as this is with Bucardo I would suggest other
> than the primary key for a table) and there is an entry that already
> exists...
> 
> EG a table as follows on master A:
> 
> | PK   | NAME     | AGE   |
> ===========================
> | 1    | Mark     | 45    |
> | 2    | Steve    | 23    |
> | 3    | Paul     | 38    |
> 
> 
> Master B as follows:
> 
> | PK   | NAME     | AGE   |
> ===========================
> | 1    | Jerry    | 65    |
> | 2    | Steve    | 23    |
> | 3    | Paul     | 38    |
> | 4    | Mark     | 45    |
> 
> Name has a UNIQUE index...
> 
> There is already a problem with this because the tables are not in sync,
> this allows 'Mark' to be added as Row 4 of Master B..  Bucardo comes
> along and tries to replicate it, it checks for PK 4 (which has just been
> added), and it tries to delete Row 4, gets nothing, then tries to add it
> as per Master B... problem is the UNIQUE index on Master A already sees
> a 'Mark' (which is at Row 1) and causes the unique constraint violation.
> 
> The other way is if both tables are in sync, but Master B doesn't have a
> (working) UNIQUE index on it's NAME column and therefore allows you to
> add another row such as:
> 
> | 5    | Steve    | 29    |
> 
> Again bucardo will fail when it tries to add the row to Master A because
> NAME 'Steve' already exists.
> 
> This is the most simplest form/cause of this type of issue, I have seen
> more subtle issues where you have derived primary keys (Relationship
> table, where the primary key is 2 rows from other tables as a foreign
> constraint) and the values have been crossed with unique indexes...
> 
> Basically to fix look at the data and determine whether it is new rows
> with some UNIQ index that is not the primary key or some other subtle
> error in the data... only then will you know the resolution (and trust
> me, bucardo ignoring uniq index violation is very very very rarely the
> correct way to go (so rare that I can't think of a valid reason.)
> 
> Regards,
> 
> -- 
> Michelle Sullivan
> http://www.mhix.org/
> 
 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20150106/3c9a8bf6/attachment-0001.html>