Overview v5
By default, conflicts are resolved at row level. When changes from two nodes conflict, either the local or remote tuple is selected and the other is discarded. For example, commit timestamps for the two conflicting changes might be compared and the newer one kept. This approach ensures that all nodes converge to the same result and establishes commit-order-like semantics on the whole cluster.
However, it might sometimes be appropriate to resolve conflicts at the column level rather than the row level, at least in some cases.
When to resolve at the column level
Consider a simple example in which table t has two integer columns, a and b, and a single row (1,1). On one node execute:
UPDATE t SET a = 100
On another node, before receiving the preceding UPDATE, concurrently execute:
UPDATE t SET b = 100
Note
The attributes modified by an UPDATE are determined by comparing the old and new row in a trigger. This means that if the attribute doesn't change a value, it isn't detected as modified even if it's explicitly set. For example, UPDATE t SET a = a doesn't mark a as modified for any row. Similarly, UPDATE t SET a = 1 doesn't mark a as modified for rows that are already set to 1.
This sequence results in an UPDATE-UPDATE conflict. With the update_if_newer conflict resolution, the commit timestamps are compared, and the new row version is kept. Assuming the second node committed last, the result is (1,100), which effectively discards the change to column a.
For many use cases, this behavior is desired and expected. However, for some use cases, this might be an issue. Consider, for example, a multi-node cluster where each part of the application is connected to a different node, updating a dedicated subset of columns in a shared table. In that case, the different components might conflict and overwrite changes.
For such use cases, it might be more appropriate to resolve conflicts on a given table at the column level. To achieve that, PGD tracks the timestamp of the last change for each column separately and uses that to pick the most recent value, essentially performing update_if_newer.
Applied to the previous example, the result is (100,100) on both nodes, despite neither of the nodes ever seeing such a row.
When thinking about column-level conflict resolution, it can be useful to see tables as vertically partitioned, so that each update affects data in only one slice. This approach eliminates conflicts between changes to different subsets of columns. In fact, vertical partitioning can even be a practical alternative to column-level conflict resolution.
Column-level conflict resolution requires the table to have REPLICA IDENTITY FULL. The bdr.alter_table_conflict_detection() function checks that and fails with an error if this setting is missing.
Special problems for column-level conflict resolution
By treating the columns independently, it's easy to violate constraints in a way that isn't possible when all changes happen on the same node. Consider, for example, a table like this:
CREATE TABLE t (id INT PRIMARY KEY, a INT, b INT, CHECK (a > b)); INSERT INTO t VALUES (1, 1000, 1);
Assume one node does:
UPDATE t SET a = 100;
Another node concurrently does:
UPDATE t SET b = 500;
Each of those updates is valid when executed on the initial row and so passes on each node. But when replicating to the other node, the resulting row violates the CHECK (a > b) constraint, and the replication stops until the issue is resolved manually.
Handling column-level conflicts using CRDT data types
By default, column-level conflict resolution picks the value with a higher timestamp and discards the other one. You can, however, reconcile the conflict in different, more elaborate ways. For example, you can use CRDT types that allow merging the conflicting values without discarding any information.