From 73eba5004a06a744b6b8570e42432b9e9f75997b Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Mon, 24 Mar 2025 12:30:44 +0530 Subject: Detect and Log multiple_unique_conflicts type conflict. Introduce a new conflict type, multiple_unique_conflicts, to handle cases where an incoming row during logical replication violates multiple UNIQUE constraints. Previously, the apply worker detected and reported only the first encountered key conflict (insert_exists/update_exists), causing repeated failures as each constraint violation needs to be handled one by one making the process slow and error-prone. With this patch, the apply worker checks all unique constraints upfront once the first key conflict is detected and reports multiple_unique_conflicts if multiple violations exist. This allows users to resolve all conflicts at once by deleting all conflicting tuples rather than dealing with them individually or skipping the transaction. In the future, this will also allow us to specify different resolution handlers for such a conflict type. Add the stats for this conflict type in pg_stat_subscription_stats. Author: Nisha Moond Author: Zhijie Hou Reviewed-by: Amit Kapila Reviewed-by: Peter Smith Reviewed-by: Dilip Kumar Discussion: https://postgr.es/m/CABdArM7FW-_dnthGkg2s0fy1HhUB8C3ELA0gZX1kkbs1ZZoV3Q@mail.gmail.com --- doc/src/sgml/logical-replication.sgml | 31 +++++++++++++++++++++++++++---- doc/src/sgml/monitoring.sgml | 12 ++++++++++++ 2 files changed, 39 insertions(+), 4 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml index 3556ce7cb46..f288c049a5c 100644 --- a/doc/src/sgml/logical-replication.sgml +++ b/doc/src/sgml/logical-replication.sgml @@ -1877,6 +1877,19 @@ test_sub=# SELECT * from tab_gen_to_gen; + + 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 @@ -1935,8 +1948,8 @@ DETAIL: detailed_explanation. The Key section includes the key values of the local tuple that violated a unique constraint for - insert_exists or update_exists - conflicts. + insert_exists, update_exists or + multiple_unique_conflicts conflicts. @@ -1945,8 +1958,8 @@ DETAIL: detailed_explanation. tuple if its origin differs from the remote tuple for update_origin_differs or delete_origin_differs conflicts, or if the key value conflicts with the remote tuple for - insert_exists or update_exists - conflicts. + insert_exists, update_exists or + multiple_unique_conflicts conflicts. @@ -1982,6 +1995,16 @@ DETAIL: detailed_explanation. 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. + + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index aaa6586d3a4..0960f5ba94a 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -2250,6 +2250,18 @@ description | Waiting for a newly initialized WAL file to reach durable storage + + + confl_multiple_unique_conflicts bigint + + + Number of times a row insertion or an updated row values violated multiple + NOT DEFERRABLE unique constraints during the + application of changes. See + for details about this conflict. + + + stats_reset timestamp with time zone -- cgit v1.2.3