Hi Hackers, (CCing people involved in related discussions) I am starting this thread to propose a new conflict detection type "multiple_unique_conflicts" that identifies when an incoming row during logical replication violates more than one UNIQUE constraint. If multiple constraints (such as the primary key/replica identity along with additional unique indexes) are violated for the same incoming tuple, the apply_worker will trigger a dedicated multiple_unique_conflicts conflict.
---- ISSUE: ---- Currently, when the apply_worker encounters an 'insert_exists' conflict (where the incoming row conflicts with an existing row based on the replica identity), it logs the conflict and errors out immediately. If the user tries to resolve this manually by deleting the conflicting row, the apply worker may fail again due to another unique constraint violation on a different column. This forces users to fix conflicts one by one, making resolution tedious and inefficient. Example: Schema: CREATE TABLE tab1 (col1 integer PRIMARY KEY, col2 integer UNIQUE, col3 integer UNIQUE); - col1 is Replica Identity. Data: - on pub: (1, 11, 111) - on sub: 3 additional local Inserts: (2, 22, 222); (3, 33, 333); (4, 44, 444) - Concurrently on pub, new insert: (2, 33, 444) When the new incoming tuple (2, 33, 444) is applied on the subscriber: - The apply worker first detects an 'insert_exists' conflict on col1 (primary key) and errors out. - If the user deletes the conflicting row (key col1=2) : (2, 22, 222), the apply worker fails again because col2=33 violates another unique constraint for tuple (3, 33, 333); - If the user deletes col2=33, the apply worker fails yet again due to tuple (4, 44, 444) because col3=444 is also unique. Conflicts on both col2 and col3 (which are independent of each other) are an example of a 'Multiple Unique Constraints' violation. In such cases, users are forced to resolve conflicts one by one, making the process slow and error-prone. --- SOLUTION: --- During an INSERT or UPDATE conflict check, instead of stopping at the first encountered conflict, the apply_worker will now check all unique indexes before reporting a conflict. If multiple unique key violations are found, it will report a 'multiple_unique_conflicts' conflict, listing all conflicting tuples in the logs. If only a single key conflict is detected, the existing 'insert_exists' conflict will be raised as it is now. This helps users resolve conflicts in one go, by deleting all conflicting tuples at once, instead of handling them one at a time, unless they choose to skip the transaction. Without an automatic resolution strategy in place, the apply worker will keep retrying until manual intervention resolves the conflict. Like other existing conflict types, statistics for multiple_unique_conflicts will be collected and can be viewed in the pg_stat_subscription_stats view for monitoring. ~~~ Note: Beyond its immediate benefits, this conflict type also helps the future automatic resolution [1]. Handling multiple unique conflicts under the 'insert_exists' or 'update_exists' conflict resolutions can cause unexpected behavior since their resolution strategies are designed for single-tuple conflicts. For example, 'last_update_wins' works well for a single key conflict but fails when the remote tuple is newer than some conflicting local rows and older than others, making resolution unclear. Introducing 'multiple_unique_conflicts' ensures such cases are handled separately and correctly with dedicated resolution strategies. The possible resolution strategies for this conflict type could be: apply: Apply the remote change by deleting all conflicting rows and then inserting or updating the remote row. skip: Skip applying the change. error: Error out on conflict (default behavior). ~~~ The proposal patch is attached. Suggestions and feedback are highly appreciated! [1] https://www.postgresql.org/message-id/CAJpy0uD0-DpYVMtsxK5R%3DzszXauZBayQMAYET9sWr_w0CNWXxQ%40mail.gmail.com -- Thanks, Nisha
v1-0001-Implement-the-conflict-detection-for-multiple_uni.patch
Description: Binary data
