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

Michelle Sullivan michelle at sorbs.net
Mon Jan 5 11:45:42 UTC 2015


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/



More information about the Bucardo-general mailing list