Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit6c2b5ed

Browse files
author
Amit Kapila
committed
Collect statistics about conflicts in logical replication.
This commit adds columns in view pg_stat_subscription_stats to show thenumber of times a particular conflict type has occurred during theapplication of logical replication changes. The following columns areadded:confl_insert_exists: Number of times a row insertion violated a NOT DEFERRABLE unique constraint.confl_update_origin_differs: Number of times an update was performed on a row that was previously modified by another origin.confl_update_exists: Number of times that the updated value of a row violates a NOT DEFERRABLE unique constraint.confl_update_missing: Number of times that the tuple to be updated is missing.confl_delete_origin_differs: Number of times a delete was performed on a row that was previously modified by another origin.confl_delete_missing: Number of times that the tuple to be deleted is missing.The update_origin_differs and delete_origin_differs conflicts can bedetected only when track_commit_timestamp is enabled.Author: Hou ZhijieReviewed-by: Shveta Malik, Peter Smith, Anit KapilaDiscussion:https://postgr.es/m/OS0PR01MB57160A07BD575773045FC214948F2@OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parent9626068 commit6c2b5ed

File tree

12 files changed

+204
-40
lines changed

12 files changed

+204
-40
lines changed

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

Lines changed: 9 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -1582,10 +1582,11 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
15821582
</para>
15831583

15841584
<para>
1585-
Additional logging is triggered in the following <firstterm>conflict</firstterm>
1586-
cases:
1585+
Additional logging is triggered, and the conflict statistics are collected (displayed in the
1586+
<link linkend="monitoring-pg-stat-subscription-stats"><structname>pg_stat_subscription_stats</structname></link> view)
1587+
in the following <firstterm>conflict</firstterm> cases:
15871588
<variablelist>
1588-
<varlistentry>
1589+
<varlistentry id="conflict-insert-exists" xreflabel="insert_exists">
15891590
<term><literal>insert_exists</literal></term>
15901591
<listitem>
15911592
<para>
@@ -1598,7 +1599,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
15981599
</para>
15991600
</listitem>
16001601
</varlistentry>
1601-
<varlistentry>
1602+
<varlistentry id="conflict-update-origin-differs" xreflabel="update_origin_differs">
16021603
<term><literal>update_origin_differs</literal></term>
16031604
<listitem>
16041605
<para>
@@ -1610,7 +1611,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
16101611
</para>
16111612
</listitem>
16121613
</varlistentry>
1613-
<varlistentry>
1614+
<varlistentry id="conflict-update-exists" xreflabel="update_exists">
16141615
<term><literal>update_exists</literal></term>
16151616
<listitem>
16161617
<para>
@@ -1627,7 +1628,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
16271628
</para>
16281629
</listitem>
16291630
</varlistentry>
1630-
<varlistentry>
1631+
<varlistentry id="conflict-update-missing" xreflabel="update_missing">
16311632
<term><literal>update_missing</literal></term>
16321633
<listitem>
16331634
<para>
@@ -1636,7 +1637,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
16361637
</para>
16371638
</listitem>
16381639
</varlistentry>
1639-
<varlistentry>
1640+
<varlistentry id="conflict-delete-origin-differs" xreflabel="delete_origin_differs">
16401641
<term><literal>delete_origin_differs</literal></term>
16411642
<listitem>
16421643
<para>
@@ -1648,7 +1649,7 @@ test_sub=# SELECT * FROM t1 ORDER BY id;
16481649
</para>
16491650
</listitem>
16501651
</varlistentry>
1651-
<varlistentry>
1652+
<varlistentry id="conflict-delete-missing" xreflabel="delete_missing">
16521653
<term><literal>delete_missing</literal></term>
16531654
<listitem>
16541655
<para>

‎doc/src/sgml/monitoring.sgml

Lines changed: 75 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -507,7 +507,7 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser
507507

508508
<row>
509509
<entry><structname>pg_stat_subscription_stats</structname><indexterm><primary>pg_stat_subscription_stats</primary></indexterm></entry>
510-
<entry>One row per subscription, showing statistics about errors.
510+
<entry>One row per subscription, showing statistics about errors and conflicts.
511511
See <link linkend="monitoring-pg-stat-subscription-stats">
512512
<structname>pg_stat_subscription_stats</structname></link> for details.
513513
</entry>
@@ -2157,7 +2157,10 @@ description | Waiting for a newly initialized WAL file to reach durable storage
21572157
<structfield>apply_error_count</structfield> <type>bigint</type>
21582158
</para>
21592159
<para>
2160-
Number of times an error occurred while applying changes
2160+
Number of times an error occurred while applying changes. Note that any
2161+
conflict resulting in an apply error will be counted in both
2162+
<literal>apply_error_count</literal> and the corresponding conflict
2163+
count (e.g., <literal>confl_*</literal>).
21612164
</para></entry>
21622165
</row>
21632166

@@ -2171,6 +2174,76 @@ description | Waiting for a newly initialized WAL file to reach durable storage
21712174
</para></entry>
21722175
</row>
21732176

2177+
<row>
2178+
<entry role="catalog_table_entry"><para role="column_definition">
2179+
<structfield>confl_insert_exists</structfield> <type>bigint</type>
2180+
</para>
2181+
<para>
2182+
Number of times a row insertion violated a
2183+
<literal>NOT DEFERRABLE</literal> unique constraint during the
2184+
application of changes. See <xref linkend="conflict-insert-exists"/>
2185+
for details about this conflict.
2186+
</para></entry>
2187+
</row>
2188+
2189+
<row>
2190+
<entry role="catalog_table_entry"><para role="column_definition">
2191+
<structfield>confl_update_origin_differs</structfield> <type>bigint</type>
2192+
</para>
2193+
<para>
2194+
Number of times an update was applied to a row that had been previously
2195+
modified by another source during the application of changes. See
2196+
<xref linkend="conflict-update-origin-differs"/> for details about this
2197+
conflict.
2198+
</para></entry>
2199+
</row>
2200+
2201+
<row>
2202+
<entry role="catalog_table_entry"><para role="column_definition">
2203+
<structfield>confl_update_exists</structfield> <type>bigint</type>
2204+
</para>
2205+
<para>
2206+
Number of times that an updated row value violated a
2207+
<literal>NOT DEFERRABLE</literal> unique constraint during the
2208+
application of changes. See <xref linkend="conflict-update-exists"/>
2209+
for details about this conflict.
2210+
</para></entry>
2211+
</row>
2212+
2213+
<row>
2214+
<entry role="catalog_table_entry"><para role="column_definition">
2215+
<structfield>confl_update_missing</structfield> <type>bigint</type>
2216+
</para>
2217+
<para>
2218+
Number of times the tuple to be updated was not found during the
2219+
application of changes. See <xref linkend="conflict-update-missing"/>
2220+
for details about this conflict.
2221+
</para></entry>
2222+
</row>
2223+
2224+
<row>
2225+
<entry role="catalog_table_entry"><para role="column_definition">
2226+
<structfield>confl_delete_origin_differs</structfield> <type>bigint</type>
2227+
</para>
2228+
<para>
2229+
Number of times a delete operation was applied to row that had been
2230+
previously modified by another source during the application of changes.
2231+
See <xref linkend="conflict-delete-origin-differs"/> for details about
2232+
this conflict.
2233+
</para></entry>
2234+
</row>
2235+
2236+
<row>
2237+
<entry role="catalog_table_entry"><para role="column_definition">
2238+
<structfield>confl_delete_missing</structfield> <type>bigint</type>
2239+
</para>
2240+
<para>
2241+
Number of times the tuple to be deleted was not found during the application
2242+
of changes. See <xref linkend="conflict-delete-missing"/> for details
2243+
about this conflict.
2244+
</para></entry>
2245+
</row>
2246+
21742247
<row>
21752248
<entry role="catalog_table_entry"><para role="column_definition">
21762249
<structfield>stats_reset</structfield> <type>timestamp with time zone</type>

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1365,6 +1365,12 @@ CREATE VIEW pg_stat_subscription_stats AS
13651365
s.subname,
13661366
ss.apply_error_count,
13671367
ss.sync_error_count,
1368+
ss.confl_insert_exists,
1369+
ss.confl_update_origin_differs,
1370+
ss.confl_update_exists,
1371+
ss.confl_update_missing,
1372+
ss.confl_delete_origin_differs,
1373+
ss.confl_delete_missing,
13681374
ss.stats_reset
13691375
FROM pg_subscriptionas s,
13701376
pg_stat_get_subscription_stats(s.oid)as ss;

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

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -17,8 +17,9 @@
1717
#include"access/commit_ts.h"
1818
#include"access/tableam.h"
1919
#include"executor/executor.h"
20+
#include"pgstat.h"
2021
#include"replication/conflict.h"
21-
#include"replication/logicalrelation.h"
22+
#include"replication/worker_internal.h"
2223
#include"storage/lmgr.h"
2324
#include"utils/lsyscache.h"
2425

@@ -114,6 +115,8 @@ ReportApplyConflict(EState *estate, ResultRelInfo *relinfo, int elevel,
114115
Assert(!OidIsValid(indexoid)||
115116
CheckRelationOidLockedByMe(indexoid,RowExclusiveLock, true));
116117

118+
pgstat_report_subscription_conflict(MySubscription->oid,type);
119+
117120
ereport(elevel,
118121
errcode_apply_conflict(type),
119122
errmsg("conflict detected on relation \"%s.%s\": conflict=%s",

‎src/backend/utils/activity/pgstat_subscription.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,21 @@ pgstat_report_subscription_error(Oid subid, bool is_apply_error)
3939
pending->sync_error_count++;
4040
}
4141

42+
/*
43+
* Report a subscription conflict.
44+
*/
45+
void
46+
pgstat_report_subscription_conflict(Oidsubid,ConflictTypetype)
47+
{
48+
PgStat_EntryRef*entry_ref;
49+
PgStat_BackendSubEntry*pending;
50+
51+
entry_ref=pgstat_prep_pending_entry(PGSTAT_KIND_SUBSCRIPTION,
52+
InvalidOid,subid,NULL);
53+
pending=entry_ref->pending;
54+
pending->conflict_count[type]++;
55+
}
56+
4257
/*
4358
* Report creating the subscription.
4459
*/
@@ -101,6 +116,8 @@ pgstat_subscription_flush_cb(PgStat_EntryRef *entry_ref, bool nowait)
101116
#defineSUB_ACC(fld) shsubent->stats.fld += localent->fld
102117
SUB_ACC(apply_error_count);
103118
SUB_ACC(sync_error_count);
119+
for (inti=0;i<CONFLICT_NUM_TYPES;i++)
120+
SUB_ACC(conflict_count[i]);
104121
#undef SUB_ACC
105122

106123
pgstat_unlock_entry(entry_ref);

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 26 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1966,13 +1966,14 @@ pg_stat_get_replication_slot(PG_FUNCTION_ARGS)
19661966
Datum
19671967
pg_stat_get_subscription_stats(PG_FUNCTION_ARGS)
19681968
{
1969-
#definePG_STAT_GET_SUBSCRIPTION_STATS_COLS4
1969+
#definePG_STAT_GET_SUBSCRIPTION_STATS_COLS10
19701970
Oidsubid=PG_GETARG_OID(0);
19711971
TupleDesctupdesc;
19721972
Datumvalues[PG_STAT_GET_SUBSCRIPTION_STATS_COLS]= {0};
19731973
boolnulls[PG_STAT_GET_SUBSCRIPTION_STATS_COLS]= {0};
19741974
PgStat_StatSubEntry*subentry;
19751975
PgStat_StatSubEntryallzero;
1976+
inti=0;
19761977

19771978
/* Get subscription stats */
19781979
subentry=pgstat_fetch_stat_subscription(subid);
@@ -1985,7 +1986,19 @@ pg_stat_get_subscription_stats(PG_FUNCTION_ARGS)
19851986
INT8OID,-1,0);
19861987
TupleDescInitEntry(tupdesc, (AttrNumber)3,"sync_error_count",
19871988
INT8OID,-1,0);
1988-
TupleDescInitEntry(tupdesc, (AttrNumber)4,"stats_reset",
1989+
TupleDescInitEntry(tupdesc, (AttrNumber)4,"confl_insert_exists",
1990+
INT8OID,-1,0);
1991+
TupleDescInitEntry(tupdesc, (AttrNumber)5,"confl_update_origin_differs",
1992+
INT8OID,-1,0);
1993+
TupleDescInitEntry(tupdesc, (AttrNumber)6,"confl_update_exists",
1994+
INT8OID,-1,0);
1995+
TupleDescInitEntry(tupdesc, (AttrNumber)7,"confl_update_missing",
1996+
INT8OID,-1,0);
1997+
TupleDescInitEntry(tupdesc, (AttrNumber)8,"confl_delete_origin_differs",
1998+
INT8OID,-1,0);
1999+
TupleDescInitEntry(tupdesc, (AttrNumber)9,"confl_delete_missing",
2000+
INT8OID,-1,0);
2001+
TupleDescInitEntry(tupdesc, (AttrNumber)10,"stats_reset",
19892002
TIMESTAMPTZOID,-1,0);
19902003
BlessTupleDesc(tupdesc);
19912004

@@ -1997,19 +2010,25 @@ pg_stat_get_subscription_stats(PG_FUNCTION_ARGS)
19972010
}
19982011

19992012
/* subid */
2000-
values[0]=ObjectIdGetDatum(subid);
2013+
values[i++]=ObjectIdGetDatum(subid);
20012014

20022015
/* apply_error_count */
2003-
values[1]=Int64GetDatum(subentry->apply_error_count);
2016+
values[i++]=Int64GetDatum(subentry->apply_error_count);
20042017

20052018
/* sync_error_count */
2006-
values[2]=Int64GetDatum(subentry->sync_error_count);
2019+
values[i++]=Int64GetDatum(subentry->sync_error_count);
2020+
2021+
/* conflict count */
2022+
for (intnconflict=0;nconflict<CONFLICT_NUM_TYPES;nconflict++)
2023+
values[i++]=Int64GetDatum(subentry->conflict_count[nconflict]);
20072024

20082025
/* stats_reset */
20092026
if (subentry->stat_reset_timestamp==0)
2010-
nulls[3]= true;
2027+
nulls[i]= true;
20112028
else
2012-
values[3]=TimestampTzGetDatum(subentry->stat_reset_timestamp);
2029+
values[i]=TimestampTzGetDatum(subentry->stat_reset_timestamp);
2030+
2031+
Assert(i+1==PG_STAT_GET_SUBSCRIPTION_STATS_COLS);
20132032

20142033
/* Returns the record as Datum */
20152034
PG_RETURN_DATUM(HeapTupleGetDatum(heap_form_tuple(tupdesc,values,nulls)));

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,6 @@
5757
*/
5858

5959
/*yyyymmddN */
60-
#defineCATALOG_VERSION_NO202408301
60+
#defineCATALOG_VERSION_NO202409041
6161

6262
#endif

‎src/include/catalog/pg_proc.dat

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -5538,9 +5538,9 @@
55385538
{ oid => '6231', descr => 'statistics: information about subscription stats',
55395539
proname => 'pg_stat_get_subscription_stats', provolatile => 's',
55405540
proparallel => 'r', prorettype => 'record', proargtypes => 'oid',
5541-
proallargtypes => '{oid,oid,int8,int8,timestamptz}',
5542-
proargmodes => '{i,o,o,o,o}',
5543-
proargnames => '{subid,subid,apply_error_count,sync_error_count,stats_reset}',
5541+
proallargtypes => '{oid,oid,int8,int8,int8,int8,int8,int8,int8,int8,timestamptz}',
5542+
proargmodes => '{i,o,o,o,o,o,o,o,o,o,o}',
5543+
proargnames => '{subid,subid,apply_error_count,sync_error_count,confl_insert_exists,confl_update_origin_differs,confl_update_exists,confl_update_missing,confl_delete_origin_differs,confl_delete_missing,stats_reset}',
55445544
prosrc => 'pg_stat_get_subscription_stats' },
55455545
{ oid => '6118', descr => 'statistics: information about subscription',
55465546
proname => 'pg_stat_get_subscription', prorows => '10', proisstrict => 'f',

‎src/include/pgstat.h

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -15,6 +15,7 @@
1515
#include"datatype/timestamp.h"
1616
#include"portability/instr_time.h"
1717
#include"postmaster/pgarch.h"/* for MAX_XFN_CHARS */
18+
#include"replication/conflict.h"
1819
#include"utils/backend_progress.h"/* for backward compatibility */
1920
#include"utils/backend_status.h"/* for backward compatibility */
2021
#include"utils/relcache.h"
@@ -165,6 +166,7 @@ typedef struct PgStat_BackendSubEntry
165166
{
166167
PgStat_Counterapply_error_count;
167168
PgStat_Countersync_error_count;
169+
PgStat_Counterconflict_count[CONFLICT_NUM_TYPES];
168170
}PgStat_BackendSubEntry;
169171

170172
/* ----------
@@ -423,6 +425,7 @@ typedef struct PgStat_StatSubEntry
423425
{
424426
PgStat_Counterapply_error_count;
425427
PgStat_Countersync_error_count;
428+
PgStat_Counterconflict_count[CONFLICT_NUM_TYPES];
426429
TimestampTzstat_reset_timestamp;
427430
}PgStat_StatSubEntry;
428431

@@ -725,6 +728,7 @@ extern PgStat_SLRUStats *pgstat_fetch_slru(void);
725728
*/
726729

727730
externvoidpgstat_report_subscription_error(Oidsubid,boolis_apply_error);
731+
externvoidpgstat_report_subscription_conflict(Oidsubid,ConflictTypetype);
728732
externvoidpgstat_create_subscription(Oidsubid);
729733
externvoidpgstat_drop_subscription(Oidsubid);
730734
externPgStat_StatSubEntry*pgstat_fetch_stat_subscription(Oidsubid);

‎src/include/replication/conflict.h

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,12 @@
1414

1515
/*
1616
* Conflict types that could occur while applying remote changes.
17+
*
18+
* This enum is used in statistics collection (see
19+
* PgStat_StatSubEntry::conflict_count and
20+
* PgStat_BackendSubEntry::conflict_count) as well, therefore, when adding new
21+
* values or reordering existing ones, ensure to review and potentially adjust
22+
* the corresponding statistics collection codes.
1723
*/
1824
typedefenum
1925
{
@@ -42,6 +48,8 @@ typedef enum
4248
*/
4349
}ConflictType;
4450

51+
#defineCONFLICT_NUM_TYPES (CT_DELETE_MISSING + 1)
52+
4553
externboolGetTupleTransactionInfo(TupleTableSlot*localslot,
4654
TransactionId*xmin,
4755
RepOriginId*localorigin,

‎src/test/regress/expected/rules.out

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2139,9 +2139,15 @@ pg_stat_subscription_stats| SELECT ss.subid,
21392139
s.subname,
21402140
ss.apply_error_count,
21412141
ss.sync_error_count,
2142+
ss.confl_insert_exists,
2143+
ss.confl_update_origin_differs,
2144+
ss.confl_update_exists,
2145+
ss.confl_update_missing,
2146+
ss.confl_delete_origin_differs,
2147+
ss.confl_delete_missing,
21422148
ss.stats_reset
21432149
FROM pg_subscription s,
2144-
LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, sync_error_count, stats_reset);
2150+
LATERAL pg_stat_get_subscription_stats(s.oid) ss(subid, apply_error_count, sync_error_count,confl_insert_exists, confl_update_origin_differs, confl_update_exists, confl_update_missing, confl_delete_origin_differs, confl_delete_missing,stats_reset);
21452151
pg_stat_sys_indexes| SELECT relid,
21462152
indexrelid,
21472153
schemaname,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp