PRIMARY KEY or UNIQUE Conflicts¶
The most common conflicts are row conflicts, where two operations affect a row with the same key in ways they could not do on a single node. BDR can detect most of those and will apply the update_if_newer conflict resolver.
Row conflicts include:
INSERTvsINSERTUPDATEvsUPDATEUPDATEvsDELETEINSERTvsUPDATEINSERTvsDELETEDELETEvsDELETE
The view bdr.node_conflict_resolvers provides information on how
conflict resolution is currently configured for all known conflict
types.
INSERT/INSERT Conflicts¶
The most common conflict, INSERT/INSERT, arises where
INSERTs on two different nodes create a tuple with the same
PRIMARY KEY values (or if no PRIMARY 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 a user-defined conflict handler.
This conflict will generate the insert_exists conflict type, which
is by default resolved by choosing the newer (based on commit time) row
and keeping only 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 conflict resolution and user-defined conflict triggers.
This type of conflict can be effectively eliminated by use of Global 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 than one UNIQUE constraint and that results in a
conflict against more than one other row, then the apply of the
replication change will produce a multiple_unique_conflicts
conflict.
In case of such a conflict, some rows must be removed in order for
replication to continue. Depending on the resolver setting for
multiple_unique_conflicts, the apply process will either exit with
error, skip the incoming row, or delete some of the rows automatically.
The automatic deletion will always try to preserve 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
and situation. 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 current local row came from);
if that has changed, the update_origin_change conflict is generated.
In all other cases, the UPDATE is normally applied without a
conflict being generated.
Both of these conflicts are resolved same way as insert_exists, as
described above.
UPDATE Conflicts on the PRIMARY KEY¶
BDR cannot currently perform conflict resolution where the
PRIMARY KEY is changed by an UPDATE operation. It is permissible
to update the primary key, but you must ensure that no conflict with
existing values is possible.
Conflicts on the update of the primary key are Divergent Conflicts and require manual operator intervention.
Updating a PK is possible in PostgreSQL, but there are issues 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 very careful to avoid runtime errors, even without BDR.
With BDR, the situation becomes more complex if UPDATEs are allowed 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 cause a divergent error, since both changes are accepted. But when the changes are applied on the other node, this will result in update_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 changes concurrently 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 between the nodes. But then apply on the target fails on both nodes with a duplicate key value violation ERROR, which causes the replication to 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_type update_pkey_exists to
skip, update or update_if_newer. This may still lead to
divergence depending on the nature of the update.
You can avoid divergence in cases like the one described above where the
same old key is being updated by the same new key concurrently by
setting update_pkey_exists to update_if_newer. However in
certain situations, divergence will happen even with
update_if_newer, namely when 2 different rows both get updated
concurrently to the same new primary key.
As a result, we recommend strongly against allowing PK UPDATEs in your applications, especially with BDR. If there are parts of your application that change Primary Keys, then to avoid concurrent changes, 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 incoming UPDATE violates more than one UNIQUE index
(and/or the PRIMARY KEY), BDR will raise a
multiple_unique_conflicts conflict.
BDR supports deferred unique constraints. If a transaction can commit on the source then it will apply cleanly on target, unless it sees conflicts. However, a deferred Primary Key cannot be used as a REPLICA IDENTITY, so the use cases are already limited by that and the warning above about using multiple unique constraints.
UPDATE/DELETE Conflicts¶
It is possible for one node to UPDATE a row that another node
simultaneously DELETEs. 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 the UPDATE 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
UPDATE is received in case the local node is lagging behind in
replication. In this case BDR cannot differentiate between
UPDATE/DELETE conflicts and INSERT/UPDATE
Conflicts and will simply generate the
update_missing conflict.
Another type of conflicting DELETE and UPDATE is a DELETE
operation that comes after the row was UPDATEd locally. In this
situation, the outcome depends upon the type of conflict detection used.
When using the default, Origin Conflict
Detection, no conflict is detected at
all, leading to the DELETE being applied and the row removed. If you
enable Row Version Conflict
Detection, a
delete_recently_updated conflict is generated. The default
resolution for this conflict type is to to apply the DELETE and
remove the row, but this can be configured or handled via a conflict
trigger.
INSERT/UPDATE Conflicts¶
When using the default asynchronous mode of operation, a node may
receive an UPDATE of a row before the original INSERT was
received. This can only happen 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
default conflict resolver is insert_or_skip, though
insert_or_error or skip may be used instead. Resolvers that do
insert-or-action will first try to INSERT a new row based on data
from the UPDATE when possible (when the whole row was received). For
the reconstruction of the row to be possible, the table either needs to
have REPLICA 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 a DELETE operation on a row for which it didn’t receive an
INSERT yet. This is again only possible with 3 or more nodes set up
(see Conflicts with 3 or more
nodes below).
BDR cannot currently detect this conflict type: the INSERT operation
will not generate any conflict type and the INSERT will be applied.
The DELETE operation will always generate a delete_missing
conflict, which is by default resolved by skipping the operation.
DELETE/DELETE Conflicts¶
A DELETE/DELETE conflict arises where two different nodes
concurrently delete the same tuple.
This will always generate a delete_missing conflict, which is by
default resolved by skipping the operation.
This conflict is harmless since both DELETEs have the same effect,
so one of 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
UPDATEd there, a 3rd node can receive the UPDATE from the 2nd
node before it receives the INSERT from the 1st node. This is an
INSERT/UPDATE conflict.
These conflicts are handled by discarding the UPDATE. This can lead
to different 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 at least 2 must be actively writing.
Also, the replication lag from node 1 to node 3 must be high enough to allow the following sequence of actions:
node 2 receives INSERT from node 1
node 2 performs UPDATE
node 3 receives UPDATE from node 2
node 3 receives INSERT from node 1
Using insert_or_error (or in some cases the insert_or_skip
conflict resolver for the update_missing conflict type) is a viable
mitigation strategy for these conflicts. Note however that enabling this
option opens the door for INSERT/DELETE conflicts; see below.
node 1 performs UPDATE
node 2 performs DELETE
node 3 receives DELETE from node 2
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
table or 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 the
UPDATE replaced by a DELETE. This can result in a delete_missing
conflict.
BDR could choose to make each INSERT into a check-for-recently deleted, as occurs with an update_missing conflict. However, the cost of doing this penalizes the majority of users, so at this time we simply log delete_missing.
Later releases will automatically resolve INSERT/DELETE anomalies via re-checks using LiveCompare when delete_missing conflicts occur. These can be performed manually by applications by checking conflict 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 replicating the affected tables if these problem use cases occur.
BDR has problems with the latter case because BDR relies upon the uniqueness of identifiers to make replication work correctly.
Applications that insert, delete and then later re-use the same unique identifiers can cause difficulties. This is known as the ABA Problem. BDR has no way of knowing whether the 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 is prevents unique identification over time, which prevents auditing, traceability and sensible data quality. Applications should not need to reuse unique identifiers.
Any identifier reuse that occurs within the time interval it takes for changes to pass across the system will cause difficulties. Although that time may be short in normal operation, down nodes may extend that interval to hours or days.
We recommend that applications do not reuse unique identifiers, but if they do, take steps to avoid reuse within a period of less than a year.
Any application that uses Sequences or UUIDs will not suffer from this problem.
