PRIMARY KEY or UNIQUE Conflicts¶
The most common conflicts are row conflicts, where two operations affect arow with the same key in ways they could not do on a single node. BDR candetect most of those and will apply the update_if_newer conflict resolver.
Row conflicts include:
`INSERT` vs `INSERT`
`UPDATE` vs `UPDATE`
`UPDATE` vs `DELETE`
`INSERT` vs `UPDATE`
`INSERT` vs `DELETE`
`DELETE` vs `DELETE`
The view bdr.node_conflict_resolvers provides information on
howconflict resolution is currently configured for all known conflict
types.
INSERT/INSERT Conflicts¶
The most common conflict, INSERT/INSERT, arises where
INSERTs on twodifferent nodes create a tuple with the same
PRIMARY KEY values (or if noPRIMARY KEY exists, the same
values for a single UNIQUE constraint ).
BDR handles this by retaining the most recently inserted tuple of the two,according to the originating host’s timestamps, unless overridden by auser-defined conflict handler.
This conflict will generate the insert_exists conflict type, which
is bydefault resolved by choosing the newer (based on commit time) row
and keepingonly that one (update_if_newer resolver). Other resolvers
can be configured -see Conflict Resolution
for details.
To resolve this conflict type, you can also use column-level conflictresolution and user-defined conflict triggers.
This type of conflict can be effectively eliminated by use ofGlobal Sequences.
INSERTs that Violate Multiple UNIQUE Constraints¶
An INSERT/INSERT conflict can violate more than one UNIQUE
constraint(of which one might be the PRIMARY KEY). If a new row
violates more thanone UNIQUE constraint and that results in a
conflict against more than oneother row, then the apply of the
replication change will produce amultiple_unique_conflicts
conflict.
In case of such a conflict, some rows must be removed in order for
replicationto continue. Depending on the resolver setting for
multiple_unique_conflicts, the apply process will either exit with
error, skip the incoming row, or deletesome of the rows automatically.
The automatic deletion will always try topreserve the row with the
correct PRIMARY KEY and delete the others.
!!! Warning * In case of multiple rows conflicting this way, if the result of conflict resolution is to proceed with the insert operation, some of the data will always be deleted!
It’s also possible to define a different behaviour using a conflict trigger.
UPDATE/UPDATE Conflicts¶
Where two concurrent UPDATEs on different nodes change the same
tuple(but not its PRIMARY KEY), an UPDATE/UPDATE conflict
can occur on replay.
These can generate different conflict kinds based on the configuration
andsituation. If the table is configured with Row Version Conflict
Detection,then the original (key)
row is compared with the local row;if they are different, the
update_differing conflict is generated.When using Origin Conflict
Detection,the origin of the row is
checked (the origin is the node that the currentlocal row came from); if
that has changed, the update_origin_change conflictis generated. In
all other cases, the UPDATE is normally applied withouta conflict
being generated.
Both of these conflicts are resolved same way as insert_exists, as
describedabove.
UPDATE Conflicts on the PRIMARY KEY¶
BDR cannot currently perform conflict resolution where the
PRIMARY KEYis changed by an UPDATE operation. It is
permissible to update the primarykey, but you must ensure that no
conflict with existing values is possible.
Conflicts on the update of the primary key are Divergent Conflicts andrequire manual operator intervention.
Updating a PK is possible in PostgreSQL, but there areissues in both PostgreSQL and BDR.
Let’s create a very simple example schema to explain:
CREATE TABLE pktest (pk integer primary key, val integer);
INSERT INTO pktest VALUES (1,1);
Updating the Primary Key column is possible, so this SQL succeeds:
UPDATE pktest SET pk=2 WHERE pk=1;
…but if we have multiple rows in the table, e.g.:
INSERT INTO pktest VALUES (3,3);
…then some UPDATEs would succeed:
UPDATE pktest SET pk=4 WHERE pk=3;
SELECT * FROM pktest;
pk | val
----+-----
* 2 | 1
* 4 | 3
(2 rows)
…but other UPDATEs would fail with constraint errors:
UPDATE pktest SET pk=4 WHERE pk=2;
ERROR: duplicate key value violates unique constraint "pktest_pkey"
DETAIL: Key (pk)=(4) already exists
So for PostgreSQL applications that UPDATE PKs, be verycareful to avoid runtime errors, even without BDR.
With BDR, the situation becomes more complex if UPDATEs areallowed from multiple locations at same time.
Executing these two changes concurrently works:
node1: UPDATE pktest SET pk=pk+1 WHERE pk = 2;
node2: UPDATE pktest SET pk=pk+1 WHERE pk = 4;
SELECT * FROM pktest;
pk | val
----+-----
* 3 | 1
* 5 | 3
(2 rows)
…but executing these next two changes concurrently will causea divergent error, since both changes are accepted. But whenthe changes are applied on the other node, this will result inupdate_missing conflicts.
node1: UPDATE pktest SET pk=1 WHERE pk = 3;
node2: UPDATE pktest SET pk=2 WHERE pk = 3;
…leaving the data different on each node:
node1:
SELECT * FROM pktest;
pk | val
----+-----
* 1 | 1
* 5 | 3
(2 rows)
node2:
SELECT * FROM pktest;
pk | val
----+-----
* 2 | 1
* 5 | 3
(2 rows)
This situation can be identified and resolved using LiveCompare.
Concurrent conflicts give problems. Executing these two changesconcurrently is not easily resolvable:
node1: UPDATE pktest SET pk=6, val=8 WHERE pk = 5;
node2: UPDATE pktest SET pk=6, val=9 WHERE pk = 5;
Both changes are applied locally, causing a divergence betweenthe nodes. But then apply on the target fails on both nodes witha duplicate key value violation ERROR, which causes the replicationto halt and currently requires manual resolution.
This duplicate key violation error can now be avoided,and replication
will not break, if you set the conflict_typeupdate_pkey_exists to
skip, update or update_if_newer. Thismay still lead to
divergence depending on the nature of the update.
You can avoid divergence in cases like the one described above where the
sameold key is being updated by the same new key concurrently by
settingupdate_pkey_exists to update_if_newer. However in
certain situations,divergence will happen even with update_if_newer,
namely when 2 differentrows both get updated concurrently to the same
new primary key.
As a result, we recommend strongly against allowing PK UPDATEsin your applications, especially with BDR. If there are partsof your application that change Primary Keys, then to avoid concurrentchanges, make those changes using Eager replication.
!!! Warning * In case the conflict resolution of update_pkey_exists
conflict results in update, one of the rows will always be deleted!
UPDATEs that Violate Multiple UNIQUE Constraints¶
Like INSERTs that Violate Multiple UNIQUE
Constraints,
where an incomingUPDATE violates more than one UNIQUE index
(and/or the PRIMARY KEY), BDRwill raise a
multiple_unique_conflicts conflict.
BDR supports deferred unique constraints. If a transaction can commit on thesource then it will apply cleanly on target, unless it sees conflicts.However, a deferred Primary Key cannot be used as a REPLICA IDENTITY, sothe use cases are already limited by that and the warning above about usingmultiple unique constraints.
UPDATE/DELETE Conflicts¶
It is possible for one node to UPDATE a row that another node
simultaneouslyDELETEs. In this case an UPDATE/DELETE
conflict can occur on replay.
If the DELETEd row is still detectable (the deleted row wasn’t
removed by VACUUM),the update_recently_deleted conflict will be
generated. By default theUPDATE will just be skipped, but the
resolution for this can be configured;see Conflict
Resolution for details.
The deleted row can be cleaned up from the database by the time the
UPDATEis received in case the local node is lagging behind in
replication. In thiscase BDR cannot differentiate between
UPDATE/DELETEconflicts and INSERT/UPDATE
Conflicts and will simply generate
theupdate_missing conflict.
Another type of conflicting DELETE and UPDATE is a DELETE
operationthat comes after the row was UPDATEd locally. In this
situation, theoutcome depends upon the type of conflict detection used.
When using thedefault, Origin Conflict
Detection, no conflict is detected at
all,leading to the DELETE being applied and the row removed. If you
enableRow Version Conflict
Detection, a
delete_recently_updated conflict isgenerated. The default resolution
for this conflict type is to to apply theDELETE and remove the
row, but this can be configured or handled viaa conflict trigger.
INSERT/UPDATE Conflicts¶
When using the default asynchronous mode of operation, a node may
receive anUPDATE of a row before the original INSERT was
received. This can onlyhappen with 3 or more nodes being active (see
Conflicts with 3 or more nodes
below).
When this happens, the update_missing conflict is generated. The
defaultconflict resolver is insert_or_skip, though
insert_or_error or skipmay be used instead. Resolvers that do
insert-or-action will firsttry to INSERT a new row based on datafrom
the UPDATE when possible (when the whole row was received). For
thereconstruction of the row to be possible, the table either needs to
haveREPLICA IDENTITY FULL or the row must not contain any TOASTed
data.
See TOAST Support Details for more info about TOASTed data.
INSERT/DELETE Conflicts¶
Similarly to the INSERT/UPDATE conflict, the node may also
receive aDELETE operation on a row for which it didn’t receive an
INSERT yet. Thisis again only possible with 3 or more nodes set up
(see [Conflicts with 3 ormore nodes] below).
BDR cannot currently detect this conflict type: the INSERT
operationwill not generate any conflict type and the INSERT will be
applied.
The DELETE operation will always generate a delete_missing
conflict, whichis by default resolved by skipping the operation.
DELETE/DELETE Conflicts¶
A DELETE/DELETE conflict arises where two different nodes
concurrentlydelete the same tuple.
This will always generate a delete_missing conflict, which is by
defaultresolved by skipping the operation.
This conflict is harmless since both DELETEs have the same effect,
so oneof them can be safely ignored.
Conflicts with 3 or more nodes¶
If one node INSERTs a row which is then replayed to a 2nd node and
UPDATEdthere, a 3rd node can receive the UPDATE from the 2nd
node before itreceives the INSERT from the 1st node. This is an
INSERT/UPDATE conflict.
These conflicts are handled by discarding the UPDATE. This can lead
todifferent data on different nodes, i.e. these are Divergent
Conflicts.
Note that this conflict type can only happen with 3 or more masters, of which atleast 2 must be actively writing.
Also, the replication lag from node 1 to node 3 must be high enough toallow the following sequence of actions:
node 2 receives INSERT from node 12. node 2 performs UPDATE3. node 3 receives UPDATE from node 24. node 3 receives INSERT from node 1
Using insert_or_error (or in some cases the insert_or_skip
conflict resolverfor the update_missing conflict type) is a viable
mitigation strategy forthese conflicts. Note however that enabling this
option opens the door forINSERT/DELETE conflicts; see below.
node 1 performs UPDATE2. node 2 performs DELETE3. node 3 receives DELETE from node 24. node 3 receives UPDATE from node 1, turning it into an INSERT
If these are problems, it’s recommended to tune freezing settings for a
tableor database so that they are correctly detected as
update_recently_deleted.
Another alternative is to use [Eager Replication] to prevent these conflicts.
INSERT/DELETE conflicts can also occur with 3 or more nodes.Such a
conflict is identical to INSERT/UPDATE, except with
theUPDATE replaced by a DELETE. This can result in a
delete_missingconflict.
BDR could choose to make each INSERT into a check-for-recentlydeleted, as occurs with an update_missing conflict. However, thecost of doing this penalizes the majority of users, so at this timewe simply log delete_missing.
Later releases will automatically resolve INSERT/DELETE anomaliesvia re-checks using LiveCompare when delete_missing conflicts occur.These can be performed manually by applications by checkingconflict logs or conflict log tables; see later.
These conflicts can occur in two main problem use cases:
INSERT, followed rapidly by a DELETE - as can be used in queuing applications
Any case where the PK identifier of a table is re-used
Neither of these cases is common and we recommend not replicatingthe affected tables if these problem use cases occur.
BDR has problems with the latter case because BDR relies upon theuniqueness of identifiers to make replication work correctly.
Applications that insert, delete andthen later re-use the same unique identifiers can cause difficulties.This is known as the ABA Problem. BDR has no way of knowing whetherthe rows are the current row, the last row or much older rows.https://en.wikipedia.org/wiki/ABA_problem
Unique identifier reuse is also a business problem, since it isprevents unique identification over time, which prevents auditing,traceability and sensible data quality. Applications should not needto reuse unique identifiers.
Any identifier reuse that occurs within the time interval it takes forchanges to pass across the system will cause difficulties. Although thattime may be short in normal operation, down nodes may extend thatinterval to hours or days.
We recommend that applications do not reuse unique identifiers, but if theydo, take steps to avoid reuse within a period of less than a year.
Any application that uses Sequences or UUIDs will not suffer from thisproblem.
