Skip to content

Commit 73eba50

Browse files
author
Amit Kapila
committed
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 <nisha.moond412@gmail.com> Author: Zhijie Hou <houzj.fnst@fujitsu.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Reviewed-by: Peter Smith <smithpb2250@gmail.com> Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com> Discussion: https://postgr.es/m/CABdArM7FW-_dnthGkg2s0fy1HhUB8C3ELA0gZX1kkbs1ZZoV3Q@mail.gmail.com
1 parent 35a92b7 commit 73eba50

File tree

14 files changed

+276
-88
lines changed

14 files changed

+276
-88
lines changed

‎doc/src/sgml/logical-replication.sgml

Lines changed: 27 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1877,6 +1877,19 @@ test_sub=# SELECT * from tab_gen_to_gen;
18771877
</para>
18781878
</listitem>
18791879
</varlistentry>
1880+
<varlistentry id="conflict-multiple-unique-conflicts" xreflabel="multiple_unique_conflicts">
1881+
<term><literal>multiple_unique_conflicts</literal></term>
1882+
<listitem>
1883+
<para>
1884+
Inserting or updating a row violates multiple
1885+
<literal>NOT DEFERRABLE</literal> unique constraints. Note that to log
1886+
the origin and commit timestamp details of conflicting keys, ensure
1887+
that <link linkend="guc-track-commit-timestamp"><varname>track_commit_timestamp</varname></link>
1888+
is enabled on the subscriber. In this case, an error will be raised until
1889+
the conflict is resolved manually.
1890+
</para>
1891+
</listitem>
1892+
</varlistentry>
18801893
</variablelist>
18811894
Note that there are other conflict scenarios, such as exclusion constraint
18821895
violations. Currently, we do not provide additional details for them in the
@@ -1935,8 +1948,8 @@ DETAIL: <replaceable class="parameter">detailed_explanation</replaceable>.
19351948
<para>
19361949
The <literal>Key</literal> section includes the key values of the local
19371950
tuple that violated a unique constraint for
1938-
<literal>insert_exists</literal> or <literal>update_exists</literal>
1939-
conflicts.
1951+
<literal>insert_exists</literal>, <literal>update_exists</literal> or
1952+
<literal>multiple_unique_conflicts</literal> conflicts.
19401953
</para>
19411954
</listitem>
19421955
<listitem>
@@ -1945,8 +1958,8 @@ DETAIL: <replaceable class="parameter">detailed_explanation</replaceable>.
19451958
tuple if its origin differs from the remote tuple for
19461959
<literal>update_origin_differs</literal> or <literal>delete_origin_differs</literal>
19471960
conflicts, or if the key value conflicts with the remote tuple for
1948-
<literal>insert_exists</literal> or <literal>update_exists</literal>
1949-
conflicts.
1961+
<literal>insert_exists</literal>, <literal>update_exists</literal> or
1962+
<literal>multiple_unique_conflicts</literal> conflicts.
19501963
</para>
19511964
</listitem>
19521965
<listitem>
@@ -1982,6 +1995,16 @@ DETAIL: <replaceable class="parameter">detailed_explanation</replaceable>.
19821995
The large column values are truncated to 64 bytes.
19831996
</para>
19841997
</listitem>
1998+
<listitem>
1999+
<para>
2000+
Note that in case of <literal>multiple_unique_conflicts</literal> conflict,
2001+
multiple <replaceable class="parameter">detailed_explanation</replaceable>
2002+
and <replaceable class="parameter">detail_values</replaceable> lines
2003+
will be generated, each detailing the conflict information associated
2004+
with distinct unique
2005+
constraints.
2006+
</para>
2007+
</listitem>
19852008
</itemizedlist>
19862009
</listitem>
19872010
</varlistentry>

‎doc/src/sgml/monitoring.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2250,6 +2250,18 @@ description | Waiting for a newly initialized WAL file to reach durable storage
22502250
</para></entry>
22512251
</row>
22522252

2253+
<row>
2254+
<entry role="catalog_table_entry"><para role="column_definition">
2255+
<structfield>confl_multiple_unique_conflicts</structfield> <type>bigint</type>
2256+
</para>
2257+
<para>
2258+
Number of times a row insertion or an updated row values violated multiple
2259+
<literal>NOT DEFERRABLE</literal> unique constraints during the
2260+
application of changes. See <xref linkend="conflict-multiple-unique-conflicts"/>
2261+
for details about this conflict.
2262+
</para></entry>
2263+
</row>
2264+
22532265
<row>
22542266
<entry role="catalog_table_entry"><para role="column_definition">
22552267
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1384,6 +1384,7 @@ CREATE VIEW pg_stat_subscription_stats AS
13841384
ss.confl_update_missing,
13851385
ss.confl_delete_origin_differs,
13861386
ss.confl_delete_missing,
1387+
ss.confl_multiple_unique_conflicts,
13871388
ss.stats_reset
13881389
FROM pg_subscription as s,
13891390
pg_stat_get_subscription_stats(s.oid) as ss;

‎src/backend/executor/execReplication.c

Lines changed: 19 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -493,25 +493,33 @@ CheckAndReportConflict(ResultRelInfo *resultRelInfo, EState *estate,
493493
ConflictType type, List *recheckIndexes,
494494
TupleTableSlot *searchslot, TupleTableSlot *remoteslot)
495495
{
496-
/* Check all the unique indexes for a conflict */
496+
List *conflicttuples = NIL;
497+
TupleTableSlot *conflictslot;
498+
499+
/* Check all the unique indexes for conflicts */
497500
foreach_oid(uniqueidx, resultRelInfo->ri_onConflictArbiterIndexes)
498501
{
499-
TupleTableSlot *conflictslot;
500-
501502
if (list_member_oid(recheckIndexes, uniqueidx) &&
502503
FindConflictTuple(resultRelInfo, estate, uniqueidx, remoteslot,
503504
&conflictslot))
504505
{
505-
RepOriginId origin;
506-
TimestampTz committs;
507-
TransactionId xmin;
508-
509-
GetTupleTransactionInfo(conflictslot, &xmin, &origin, &committs);
510-
ReportApplyConflict(estate, resultRelInfo, ERROR, type,
511-
searchslot, conflictslot, remoteslot,
512-
uniqueidx, xmin, origin, committs);
506+
ConflictTupleInfo *conflicttuple = palloc0_object(ConflictTupleInfo);
507+
508+
conflicttuple->slot = conflictslot;
509+
conflicttuple->indexoid = uniqueidx;
510+
511+
GetTupleTransactionInfo(conflictslot, &conflicttuple->xmin,
512+
&conflicttuple->origin, &conflicttuple->ts);
513+
514+
conflicttuples = lappend(conflicttuples, conflicttuple);
513515
}
514516
}
517+
518+
/* Report the conflict, if found */
519+
if (conflicttuples)
520+
ReportApplyConflict(estate, resultRelInfo, ERROR,
521+
list_length(conflicttuples) > 1 ? CT_MULTIPLE_UNIQUE_CONFLICTS : type,
522+
searchslot, remoteslot, conflicttuples);
515523
}
516524

517525
/*

‎src/backend/replication/logical/conflict.c

Lines changed: 39 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -29,19 +29,20 @@ static const char *const ConflictTypeNames[] = {
2929
[CT_UPDATE_EXISTS] = "update_exists",
3030
[CT_UPDATE_MISSING] = "update_missing",
3131
[CT_DELETE_ORIGIN_DIFFERS] = "delete_origin_differs",
32-
[CT_DELETE_MISSING] = "delete_missing"
32+
[CT_DELETE_MISSING] = "delete_missing",
33+
[CT_MULTIPLE_UNIQUE_CONFLICTS] = "multiple_unique_conflicts"
3334
};
3435

3536
static int errcode_apply_conflict(ConflictType type);
36-
static int errdetail_apply_conflict(EState *estate,
37+
static void errdetail_apply_conflict(EState *estate,
3738
ResultRelInfo *relinfo,
3839
ConflictType type,
3940
TupleTableSlot *searchslot,
4041
TupleTableSlot *localslot,
4142
TupleTableSlot *remoteslot,
4243
Oid indexoid, TransactionId localxmin,
4344
RepOriginId localorigin,
44-
TimestampTz localts);
45+
TimestampTz localts, StringInfo err_msg);
4546
static char *build_tuple_value_details(EState *estate, ResultRelInfo *relinfo,
4647
ConflictType type,
4748
TupleTableSlot *searchslot,
@@ -90,30 +91,33 @@ GetTupleTransactionInfo(TupleTableSlot *localslot, TransactionId *xmin,
9091
* 'searchslot' should contain the tuple used to search the local tuple to be
9192
* updated or deleted.
9293
*
93-
* 'localslot' should contain the existing local tuple, if any, that conflicts
94-
* with the remote tuple. 'localxmin', 'localorigin', and 'localts' provide the
95-
* transaction information related to this existing local tuple.
96-
*
9794
* 'remoteslot' should contain the remote new tuple, if any.
9895
*
99-
* The 'indexoid' represents the OID of the unique index that triggered the
100-
* constraint violation error. We use this to report the key values for
101-
* conflicting tuple.
96+
* conflicttuples is a list of local tuples that caused the conflict and the
97+
* conflict related information. See ConflictTupleInfo.
10298
*
103-
* The caller must ensure that the index with the OID 'indexoid' is locked so
104-
* that we can fetch and display the conflicting key value.
99+
* The caller must ensure that all the indexes passed in ConflictTupleInfo are
100+
* locked so that we can fetch and display the conflicting key values.
105101
*/
106102
void
107103
ReportApplyConflict(EState *estate, ResultRelInfo *relinfo, int elevel,
108104
ConflictType type, TupleTableSlot *searchslot,
109-
TupleTableSlot *localslot, TupleTableSlot *remoteslot,
110-
Oid indexoid, TransactionId localxmin,
111-
RepOriginId localorigin, TimestampTz localts)
105+
TupleTableSlot *remoteslot, List *conflicttuples)
112106
{
113107
Relation localrel = relinfo->ri_RelationDesc;
108+
StringInfoData err_detail;
109+
110+
initStringInfo(&err_detail);
114111

115-
Assert(!OidIsValid(indexoid) ||
116-
CheckRelationOidLockedByMe(indexoid, RowExclusiveLock, true));
112+
/* Form errdetail message by combining conflicting tuples information. */
113+
foreach_ptr(ConflictTupleInfo, conflicttuple, conflicttuples)
114+
errdetail_apply_conflict(estate, relinfo, type, searchslot,
115+
conflicttuple->slot, remoteslot,
116+
conflicttuple->indexoid,
117+
conflicttuple->xmin,
118+
conflicttuple->origin,
119+
conflicttuple->ts,
120+
&err_detail);
117121

118122
pgstat_report_subscription_conflict(MySubscription->oid, type);
119123

@@ -123,9 +127,7 @@ ReportApplyConflict(EState *estate, ResultRelInfo *relinfo, int elevel,
123127
get_namespace_name(RelationGetNamespace(localrel)),
124128
RelationGetRelationName(localrel),
125129
ConflictTypeNames[type]),
126-
errdetail_apply_conflict(estate, relinfo, type, searchslot,
127-
localslot, remoteslot, indexoid,
128-
localxmin, localorigin, localts));
130+
errdetail_internal("%s", err_detail.data));
129131
}
130132

131133
/*
@@ -169,6 +171,7 @@ errcode_apply_conflict(ConflictType type)
169171
{
170172
case CT_INSERT_EXISTS:
171173
case CT_UPDATE_EXISTS:
174+
case CT_MULTIPLE_UNIQUE_CONFLICTS:
172175
return errcode(ERRCODE_UNIQUE_VIOLATION);
173176
case CT_UPDATE_ORIGIN_DIFFERS:
174177
case CT_UPDATE_MISSING:
@@ -191,12 +194,13 @@ errcode_apply_conflict(ConflictType type)
191194
* replica identity columns, if any. The remote old tuple is excluded as its
192195
* information is covered in the replica identity columns.
193196
*/
194-
static int
197+
static void
195198
errdetail_apply_conflict(EState *estate, ResultRelInfo *relinfo,
196199
ConflictType type, TupleTableSlot *searchslot,
197200
TupleTableSlot *localslot, TupleTableSlot *remoteslot,
198201
Oid indexoid, TransactionId localxmin,
199-
RepOriginId localorigin, TimestampTz localts)
202+
RepOriginId localorigin, TimestampTz localts,
203+
StringInfo err_msg)
200204
{
201205
StringInfoData err_detail;
202206
char *val_desc;
@@ -209,7 +213,9 @@ errdetail_apply_conflict(EState *estate, ResultRelInfo *relinfo,
209213
{
210214
case CT_INSERT_EXISTS:
211215
case CT_UPDATE_EXISTS:
212-
Assert(OidIsValid(indexoid));
216+
case CT_MULTIPLE_UNIQUE_CONFLICTS:
217+
Assert(OidIsValid(indexoid) &&
218+
CheckRelationOidLockedByMe(indexoid, RowExclusiveLock, true));
213219

214220
if (localts)
215221
{
@@ -291,7 +297,14 @@ errdetail_apply_conflict(EState *estate, ResultRelInfo *relinfo,
291297
if (val_desc)
292298
appendStringInfo(&err_detail, "\n%s", val_desc);
293299

294-
return errdetail_internal("%s", err_detail.data);
300+
/*
301+
* Insert a blank line to visually separate the new detail line from the
302+
* existing ones.
303+
*/
304+
if (err_msg->len > 0)
305+
appendStringInfoChar(err_msg, '\n');
306+
307+
appendStringInfo(err_msg, "%s", err_detail.data);
295308
}
296309

297310
/*
@@ -323,7 +336,8 @@ build_tuple_value_details(EState *estate, ResultRelInfo *relinfo,
323336
* Report the conflicting key values in the case of a unique constraint
324337
* violation.
325338
*/
326-
if (type == CT_INSERT_EXISTS || type == CT_UPDATE_EXISTS)
339+
if (type == CT_INSERT_EXISTS || type == CT_UPDATE_EXISTS ||
340+
type == CT_MULTIPLE_UNIQUE_CONFLICTS)
327341
{
328342
Assert(OidIsValid(indexoid) && localslot);
329343

0 commit comments

Comments
 (0)