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 apply last-update-wins conflict handling or invoke a user-defined conflict handler.
Row conflicts include:
INSERT vs INSERT
INSERT vs UPDATE
UPDATE vs DELETE
INSERT vs DELETE
DELETE vs DELETE
The most common conflict, INSERT vs INSERT, arises where INSERTs on two different nodes create a tuple with the same PRIMARY KEY values (or the same values for a single UNIQUE constraint if no PRIMARY KEY exists). BDR handles this by retaining the most recently inserted tuple of the two according to the originating host's timestamps unless a user-defined conflict handler overrides this.
No special administrator action is required to deal with these conflicts, but the user must undersand that one of the INSERTed tuples is effectively discarded on all nodes - there is no data merging done unless a user defined conflict handler does it.
Conflict handling is only possible when only one constraint is violated by the incoming insert from the remote node; INSERTs that violate multiple UNIQUE constraints are more problematic.
An INSERT/INSERT conflict can violate more than one UNIQUE constraint (of which one might be the PRIMARY KEY).
BDR can only handle an INSERT/INSERT conflict on one unique constraint (including the PRIMARY KEY). If a new row conflicts with more than one UNIQUE constraint then the apply worker that's trying to apply the change will ERROR out with:
ERROR: multiple unique constraints violated by remotely INSERTed tuple
(Older versions would report a "diverging uniqueness conflict" error instead).
In case of such a conflict, you must manually remove the conflicting tuple(s) from the local side by DELETEing it or by UPDATEing it so that it no longer conflicts with the new remote tuple. There may be more than one conflicting tuple. There is not currently any built-in facility to ignore, discard or merge tuples that violate more than one local unique constraint.
Where two concurrent UPDATEs on different nodes change the same tuple (but not its PRIMARY KEY), an UPDATE/UPDATE conflict occurs on replay. These are resolved using last-update-wins handling or, if it exists, a user-defined conflict handler.
Because a PRIMARY KEY must exist in order to match tuples and perform conflict resolution, UPDATEs are rejected on tables without a PRIMARY KEY with:
ERROR: Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.
BDR cannot currently perform last-update-wins 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 update of the primary key are divergent conflicts that require manual operator intervention; see Divergent conflicts.
Like INSERTs that violate multiple UNIQUE constraints, where an incoming UPDATE violates more than one UNIQUE index (and/or the PRIMARY KEY), BDR cannot apply last-update-wins conflict resolution.
This is a divergent conflict that will require operator intervention; see Divergent conflicts.
It is possible for one node to UPDATE a row that another node simultaneously DELETEs. In this case a UPDATE/DELETE conflict occurs on replay. The resolution of this conflict is to discard any UPDATE that arrives after the DELETE unless a user-defined conflict handler specifies otherwise.
Because a PRIMARY KEY must exist in order to match tuples and perform conflict resolution, DELETEs are rejected on tables without a PRIMARY KEY with:
ERROR: Cannot run UPDATE or DELETE on table (tablename) because it does not have a primary key.
Note: BDR cannot currently differentiate between UPDATE/DELETE conflicts and INSERT/UPDATE conflicts. In both cases an UPDATE arrives that affects a nonexistent row. Since BDR is asynchronous and there's no ordering of replay between nodes, it can't tell if this is an update to a new row we haven't yet received the insert for, or a row we've already replayed a delete for. In both cases the resolution is the same - the update is discarded.
If one node INSERTs a row which is then replayed to a 2nd node and UPDATEd there, a 3rd node may receive the UPDATE from the 2nd node before it receives the INSERT from the 1st node. This is an INSERT/UPDATE conflict.
Unless a user defined conflict trigger determines otherwise these conflicts are handled by discarding the UPDATE. This can lead to different data on different nodes. See UPDATE/DELETE conflicts for details.
A DELETE/DELETE conflict arises where two different nodes concurrently delete the same tuple.
This conflict is harmless since both DELETEs have the same effect, so one of them can be safely ignored.
Conflicts between a remote transaction being applied and existing local data can also occur for FOREIGN KEY constraints. These conflicts are usually transient issues that arise from transactions being applied in a different order to the order they appeared to occur logically on the nodes that originated them.
While apply is strictly ordered for any given origin node, there is no enforcement of ordering of transactions between two different nodes, so it's possible for (e.g.) node1 to insert a row into T1, which is replayed to node2. node2 inserts a row into T2 which has a foreign key reference to the row from T1. On node3, if the transaction from node2 that inserts the row into T2 is received before the transaction from node1 that inserts the row into T1, the transaction from node2 will fail to apply. This failure will record a rollback in bdr.pg_stat_bdr and an ERROR with details in the PostgreSQL error log on the applying node (node3). In this case BDR will retry the transaction from node2 periodicially, so once it's replayed the transaction from node1 that it depends on the transaction will commit successfully.
Simple foreign key constraint conflicts are generally transient and require no administrator action, but for transactions that change multiple entities this is not always the case. It is possible for concurrent changes to tables with foreign key constraints to create inter-node replication deadlocks where no node can apply changes from any other node because they conflict with local data. This causes replication activity to stop until the deadlock is broken by a local data change on one or more of the nodes.
For example, take a two node system with two tables and some existing data:
CREATE TABLE parent( id integer primary key ); CREATE TABLE child( id integer primary key, parent_id integer not null references parent(id) ); INSERT INTO parent(id) VALUES (1), (2); INSERT INTO child(id, parent_id) VALUES (11, 1), (11, 2);
If node A does:
INSERT INTO child(id, parent_id) VALUES (21, 2);
and at the same time node B does:
DELETE FROM child WHERE parent_id = 2; DELETE FROM parent WHERE id = 2;
then we have a situation where the transaction from node A cannot apply successfully to the child table on node B because the referenced parent no longer exists. The transaction from node B cannot apply to node A because it deletes a parent tuple that's still referenced, the new one with id=21. Neither transaction can replay, and both will output periodic ERRORs in the log files as they are retried. Since BDR replays transactions from a given node strictly in order, neither node can make progress with replication unless the user, or some 3rd node, makes changes that resolve the deadlock.
It is important to note that when we manually deleted the child tuples on node B, the newly inserted child on node A was not affected because it had not yet replicated to node B. If either node replays the other's transaction before attempting its own local transaction then no problem will occur.
Solving such a foreign key deadlock requires that you fix the constraint issue on each end. In this case, you would need to insert a dummy parent row on node B and delete the new child on node A. Replay will continue past the deadlock point.
BDR can't just apply the changes from each end anyway because doing so would result in tables that violated their declared foreign key constraints, which most users would view as corruption.
BDR doesn't support exclusion constraints and restricts their creation.
Important: If an existing stand-alone database is converted to a BDR database then all exclusion constraints should be manually dropped.
In a distributed asynchronous system it is not possible to ensure that no set of rows that violates the constraint exists, because all transactions on different nodes are fully isolated. Exclusion constraints would lead to replay deadlocks where replay could not progress from any node to any other node because of exclusion constraint violations.
If you force BDR to create an exclusion constraint, or you don't drop existing ones when converting a standalone database to BDR you should expect replication to break. You can get it to progress again by removing or altering the local tuple(s) that an incoming remote tuple conflicts with so that the remote transaction can be applied.
Conflicts can also arise where nodes have global (PostgreSQL-system-wide) data, like roles, that differs. This can result in operations - mainly DDL - that can be run successfully and committed on one node, but then fail to apply to other nodes.
For example, node1 might have a user named fred, but that user was not created on node2. BDR does not replicate CREATE USER (see CREATE ROLE/USER/GROUP ) so this situation can arise easily. If fred on node1 creates a table, it will be replicated with its owner set to fred. When the DDL command is applied to node2 the DDL will fail because there is no user named fred. This failure will emit an ERROR in the PostgreSQL logs on node2 and increment bdr.pg_stat_bdr.nr_rollbacks.
Administrator intervention is required to resolve this conflict by creating the user fred on node2. (It need not have the same permissions, but must exist).
Because BDR apply processes operate very like normal user sessions they are subject to the usual rules around row and table locking. This can sometimes lead to BDR apply processes waiting on locks held by user transactions, or even by each other.
Relevant locking includes;
explicit table-level locking (LOCK TABLE ...) by user sessions
explicit row level locking (SELECT ... FOR UPDATE/FOR SHARE) by user sessions
locking from foreign keys
implicit locking because of row UPDATEs, INSERTs or DELETEs, either from local activity or apply from other servers
It is even possible for a BDR apply process to deadlock with a user transaction, where the user transaction is waiting on a lock held by the apply process and vice versa. Two apply processes may also deadlock with each other. PostgreSQL's deadlock detector will step in and terminate one of the problem transactions. If the BDR apply worker's process is terminated it will simply retry and generally succeed.
All these issues are transient and generally require no administrator action. If an apply process is stuck for a long time behind a lock on an idle user session the administrator may choose to terminate the user session to get replication flowing again, but this is no different to a user holding a long lock that impacts another user session.
Use of the log_lock_waits facility in PostgreSQL can help identify locking related replay stalls.
Divergent conflicts arise when data that should be the same on different nodes differs unexpectedly. Divergent conflicts should not occur, but not all such conflicts can be reliably prevented at time of writing.
Changing the PRIMARY KEY of a row can lead to a divergent conflict if another node changes the key of the same row before all nodes have replayed the change. Avoid changing primary keys, or change them only on one designated node. See UPDATE conflicts on the PRIMARY KEY.
Divergent conflicts involving row data generally require administrator action to manually adjust the data on one of the nodes to be consistent with the other one while replication is temporarily disabled using bdr.do_not_replicate. Such conflicts should not arise so long as BDR is used as documented and settings or functions marked as unsafe are avoided.
The administrator must manually resolve such conflicts. Use of the advanced options bdr.do_not_replicate, bdr.skip_ddl_replication and bdr.permit_unsafe_ddl_commands may be required depending on the nature of the conflict. However, careless use of these options can make things much worse and it isn't possible to give general instructions for resolving all possible kinds of conflict.