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

Michelle Sullivan michelle at sorbs.net
Tue Jan 6 10:53:49 UTC 2015


Sijo Pappachan wrote:
> 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?
>
Depends on what uniq column you have and why.  Eg if it's customer
account numbers you might want to look at how you are generating the
account numbers... If it's with a sequence then just make the sequences
uniq on each node by *not* replicating the sequence that is generating
them and then setting them to increment the same on each node and
setting the start number to be offset to the others.

ie if you have account_numbers_seq create it the same on each node.  If
you have 4 nodes set the increment value to be 5 (or more - to allow for
expansion) and then set the first node starting value to 1, the second
node start value to 2, third to 3 etc..

If it's customer names then you have an issue that only you might be
able to resolve.

Often the solution is to take a look at the reason for the UNIQUE index
and why you have a separate primary key..  Many UNIQUE indexes are prime
candidates for using as a primary key.

Regards,

Michelle

> 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/
> >


-- 
Michelle Sullivan
http://www.mhix.org/



More information about the Bucardo-general mailing list