Logical replication behaves similarly to normal DML operations in that the data will be updated even if it was changed locally on the subscriber node. If incoming data violates any constraints the replication will stop. This is referred to as a conflict. When replicating UPDATE or DELETE operations, missing data is also considered as a conflict, but does not result in an error and such operations will simply be skipped.
Additional logging is triggered, and the conflict statistics are collected (displayed in the pg_stat_subscription_stats view) in the following conflict cases:
insert_exists #Inserting a row that violates a NOT DEFERRABLE unique constraint. Note that to log the origin and commit timestamp details of the conflicting key, track_commit_timestamp should be enabled on the subscriber. In this case, an error will be raised until the conflict is resolved manually.
update_origin_differs #Updating a row that was previously modified by another origin. Note that this conflict can only be detected when track_commit_timestamp is enabled on the subscriber. Currently, the update is always applied regardless of the origin of the local row.
update_exists #The updated value of a row violates a NOT DEFERRABLE unique constraint. Note that to log the origin and commit timestamp details of the conflicting key, track_commit_timestamp should be enabled on the subscriber. In this case, an error will be raised until the conflict is resolved manually. Note that when updating a partitioned table, if the updated row value satisfies another partition constraint resulting in the row being inserted into a new partition, the insert_exists conflict may arise if the new row violates a NOT DEFERRABLE unique constraint.
update_deleted #The tuple to be updated was concurrently deleted by another origin. The update will simply be skipped in this scenario. Note that this conflict can only be detected when track_commit_timestamp and retain_dead_tuples are enabled. Note that if a tuple cannot be found due to the table being truncated, only an update_missing conflict will arise. Additionally, if the tuple was deleted by the same origin, an update_missing conflict will arise.
update_missing #The row to be updated was not found. The update will simply be skipped in this scenario.
delete_origin_differs #Deleting a row that was previously modified by another origin. Note that this conflict can only be detected when track_commit_timestamp is enabled on the subscriber. Currently, the delete is always applied regardless of the origin of the local row.
delete_missing #The row to be deleted was not found. The delete will simply be skipped in this scenario.
multiple_unique_conflicts #Inserting or updating a row violates multiple NOT DEFERRABLE unique constraints. Note that to log the origin and commit timestamp details of conflicting keys, ensure that track_commit_timestamp is enabled on the subscriber. In this case, an error will be raised until the conflict is resolved manually.
Note that there are other conflict scenarios, such as exclusion constraint violations. Currently, we do not provide additional details for them in the log.
The log format for logical replication conflicts is as follows:
LOG: conflict detected on relation "schemaname.tablename": conflict=conflict_typeDETAIL:detailed_explanation[:detail_values[, ... ]]. wheredetail_valuesis one of:key(column_name[, ...])=(column_value[, ...])local row[(column_name[, ...])=](column_value[, ...])remote row[(column_name[, ...])=](column_value[, ...])replica identity{(column_name[, ...])=(column_value[, ...]) | full [(column_name[, ...])=](column_value[, ...])}
The log provides the following information:
LOGschemaname.tablename identifies the local relation involved in the conflict.
conflict_type is the type of conflict that occurred (e.g., insert_exists, update_exists).
DETAILdetailed_explanation includes the origin, transaction ID, and commit timestamp of the transaction that modified the local row, if available.
The key section includes the key values of the local row that violated a unique constraint for insert_exists, update_exists or multiple_unique_conflicts conflicts.
The local row section includes the local row if its origin differs from the remote row for update_origin_differs or delete_origin_differs conflicts, or if the key value conflicts with the remote row for insert_exists, update_exists or multiple_unique_conflicts conflicts.
The remote row section includes the new row from the remote insert or update operation that caused the conflict. Note that for an update operation, the column value of the new row will be null if the value is unchanged and toasted.
The replica identity section includes the replica identity key values that were used to search for the existing local row to be updated or deleted. This may include the full row value if the local relation is marked with REPLICA IDENTITY FULL.
column_name is the column name. For local row, remote row, and replica identity full cases, column names are logged only if the user lacks the privilege to access all columns of the table. If column names are present, they appear in the same order as the corresponding column values.
column_value is the column value. The large column values are truncated to 64 bytes.
Note that in case of multiple_unique_conflicts conflict, multiple detailed_explanation and detail_values lines will be generated, each detailing the conflict information associated with distinct unique constraints.
Logical replication operations are performed with the privileges of the role which owns the subscription. Permissions failures on target tables will cause replication conflicts, as will enabled row-level security on target tables that the subscription owner is subject to, without regard to whether any policy would ordinarily reject the INSERT, UPDATE,