public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 1/5] Fix comments for WITH OIDs, removed at 578b22971
41+ messages / 8 participants
[nested] [flat]
* [PATCH v5 1/5] Fix comments for WITH OIDs, removed at 578b22971
@ 2020-04-29 18:13 Justin Pryzby <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Justin Pryzby @ 2020-04-29 18:13 UTC (permalink / raw)
Previously mentioned here:
https://www.postgresql.org/message-id/[email protected]
---
src/backend/access/common/tupdesc.c | 4 +---
src/backend/access/transam/varsup.c | 3 +--
src/backend/commands/tablecmds.c | 5 ++---
3 files changed, 4 insertions(+), 8 deletions(-)
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index 1e743d7d86..ce84e22cbd 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -786,9 +786,7 @@ TupleDescInitEntryCollation(TupleDesc desc,
*
* Given a relation schema (list of ColumnDef nodes), build a TupleDesc.
*
- * Note: the default assumption is no OIDs; caller may modify the returned
- * TupleDesc if it wants OIDs. Also, tdtypeid will need to be filled in
- * later on.
+ * tdtypeid will need to be filled in later on.
*/
TupleDesc
BuildDescForRelation(List *schema)
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index e14b53bf9e..8328b8050a 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -527,8 +527,7 @@ GetNewObjectId(void)
* The first time through this routine after normal postmaster start, the
* counter will be forced up to FirstNormalObjectId. This mechanism
* leaves the OIDs between FirstBootstrapObjectId and FirstNormalObjectId
- * available for automatic assignment during initdb, while ensuring they
- * will never conflict with user-assigned OIDs.
+ * available for automatic assignment during initdb.
*/
if (ShmemVariableCache->nextOid < ((Oid) FirstNormalObjectId))
{
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 2ab02e01a0..35945bce73 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4829,7 +4829,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
continue;
/*
- * If we change column data types or add/remove OIDs, the operation
+ * If we change column data types, the operation
* has to be propagated to tables that use this table's rowtype as a
* column type. tab->newvals will also be non-NULL in the case where
* we're adding a column with a default. We choose to forbid that
@@ -4853,8 +4853,7 @@ ATRewriteTables(AlterTableStmt *parsetree, List **wqueue, LOCKMODE lockmode,
/*
* We only need to rewrite the table if at least one column needs to
- * be recomputed, we are adding/removing the OID column, or we are
- * changing its persistence.
+ * be recomputed, or we are changing its persistence.
*
* There are two reasons for requiring a rewrite when changing
* persistence: on one hand, we need to ensure that the buffers
--
2.17.0
--8TaQrIeukR7mmbKf
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v5-0002-Remove-mention-of-double-timestamps.patch"
^ permalink raw reply [nested|flat] 41+ messages in thread
* [PATCH v4 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs.
@ 2022-08-13 06:07 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Nathan Bossart @ 2022-08-13 06:07 UTC (permalink / raw)
Presently, these functions use the index of the backend's entry in
localBackendStatusTable as the backend ID. While this might bear
some resemblance to the backend ID of the backend's PGPROC struct,
it can quickly diverge as sessions connect and disconnect. This
change modifies the pg_stat_get_backend_*() suite of functions to
use the PGPROC backend IDs instead. While we could instead
introduce a new function for retrieving PGPROC backend IDs,
presenting two sets of backend IDs to users seems likely to cause
even more confusion than what already exists.
This is primarily useful for discovering the session that owns a
resource named with its PGPROC backend ID. For example, temporary
schema names include the PGPROC backend ID, and it might be
necessary to identify the session that owns a temporary table that
is putting the cluster in danger of transaction ID wraparound.
---
doc/src/sgml/monitoring.sgml | 12 ++++---
src/backend/utils/activity/backend_status.c | 40 +++++++++++++++++++--
src/backend/utils/adt/pgstatfuncs.c | 11 +++---
src/include/utils/backend_status.h | 8 +++++
4 files changed, 59 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..4ca17e9f6f 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5488,10 +5488,13 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
information. In such cases, an older set of per-backend statistics
access functions can be used; these are shown in <xref
linkend="monitoring-stats-backend-funcs-table"/>.
- These access functions use a backend ID number, which ranges from one
- to the number of currently active backends.
+ These access functions use the session's backend ID number, which is a small
+ integer that is distinct from the backend ID of any concurrent session,
+ although an ID can be recycled as soon as the session exits. The backend ID
+ is used, among other things, to identify the session's temporary schema if
+ it has one.
The function <function>pg_stat_get_backend_idset</function> provides a
- convenient way to generate one row for each active backend for
+ convenient way to list all the active backends' ID numbers for
invoking these functions. For example, to show the <acronym>PID</acronym>s and
current queries of all backends:
@@ -5526,8 +5529,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<returnvalue>setof integer</returnvalue>
</para>
<para>
- Returns the set of currently active backend ID numbers (from 1 to the
- number of active backends).
+ Returns the set of currently active backend ID numbers.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index c7ed1e6d7a..159d022070 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -849,6 +849,7 @@ pgstat_read_current_status(void)
BackendIdGetTransactionIds(i,
&localentry->backend_xid,
&localentry->backend_xmin);
+ localentry->backend_id = i;
localentry++;
localappname += NAMEDATALEN;
@@ -1045,6 +1046,22 @@ pgstat_get_my_query_id(void)
return MyBEEntry->st_query_id;
}
+/* ----------
+ * cmp_lbestatus
+ *
+ * Comparison function for bsearch() on an array of LocalPgBackendStatus. The
+ * backend_id field is used to compare the arguments.
+ *
+ * ----------
+ */
+static int
+cmp_lbestatus(const void *a, const void *b)
+{
+ LocalPgBackendStatus *lbestatus1 = (LocalPgBackendStatus *) a;
+ LocalPgBackendStatus *lbestatus2 = (LocalPgBackendStatus *) b;
+
+ return lbestatus1->backend_id - lbestatus2->backend_id;
+}
/* ----------
* pgstat_fetch_stat_beentry() -
@@ -1052,6 +1069,10 @@ pgstat_get_my_query_id(void)
* Support function for the SQL-callable pgstat* functions. Returns
* our local copy of the current-activity entry for one backend.
*
+ * Unlike pgstat_fetch_stat_local_beentry(), the beid argument refers to the
+ * backend ID stored in the backend's PGPROC struct instead of its index in
+ * the localBackendStatusTable.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
@@ -1059,12 +1080,23 @@ pgstat_get_my_query_id(void)
PgBackendStatus *
pgstat_fetch_stat_beentry(int beid)
{
+ LocalPgBackendStatus key;
+ LocalPgBackendStatus *ret;
+
pgstat_read_current_status();
- if (beid < 1 || beid > localNumBackends)
+ if (beid < 1 || beid > NumBackendStatSlots)
return NULL;
- return &localBackendStatusTable[beid - 1].backendStatus;
+ key.backend_id = beid;
+ ret = (LocalPgBackendStatus *) bsearch(&key, localBackendStatusTable,
+ localNumBackends,
+ sizeof(LocalPgBackendStatus),
+ cmp_lbestatus);
+ if (ret)
+ return &ret->backendStatus;
+
+ return NULL;
}
@@ -1074,6 +1106,10 @@ pgstat_fetch_stat_beentry(int beid)
* Like pgstat_fetch_stat_beentry() but with locally computed additions (like
* xid and xmin values of the backend)
*
+ * Unlike pgstat_fetch_stat_beentry(), the beid argument refers to the index
+ * in the localBackendStatusTable instead of the backend ID stored in the
+ * backend's PGPROC struct.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index be15b4b2e5..56ca18b6d0 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -415,7 +415,6 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int *fctx;
- int32 result;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -436,12 +435,13 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
fctx = funcctx->user_fctx;
fctx[0] += 1;
- result = fctx[0];
- if (result <= fctx[1])
+ if (fctx[0] <= fctx[1])
{
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(fctx[0]);
+
/* do when there is more left to send */
- SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(localStatus->backend_id));
}
else
{
@@ -1180,7 +1180,8 @@ pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
result = 0;
for (beid = 1; beid <= tot_backends; beid++)
{
- PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(beid);
+ PgBackendStatus *beentry = &localStatus->backendStatus;
if (beentry && beentry->st_databaseid == dbid)
result++;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7403bca25e..f7c7c6e671 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -13,6 +13,7 @@
#include "datatype/timestamp.h"
#include "libpq/pqcomm.h"
#include "miscadmin.h" /* for BackendType */
+#include "storage/backendid.h"
#include "utils/backend_progress.h"
@@ -258,6 +259,13 @@ typedef struct LocalPgBackendStatus
* not.
*/
TransactionId backend_xmin;
+
+ /*
+ * The backend ID. For auxiliary processes, this will be set to a value
+ * greater than MaxBackends (since auxiliary processes do not have proper
+ * backend IDs).
+ */
+ BackendId backend_id;
} LocalPgBackendStatus;
--
2.25.1
--tKW2IUtsqtDRztdT--
^ permalink raw reply [nested|flat] 41+ messages in thread
* [PATCH v3 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs.
@ 2022-08-13 06:07 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Nathan Bossart @ 2022-08-13 06:07 UTC (permalink / raw)
Presently, these functions use the index of the backend's entry in
localBackendStatusTable as the backend ID. While this might bear
some resemblance to the backend ID of the backend's PGPROC struct,
it can quickly diverge as sessions connect and disconnect. This
change modifies the pg_stat_get_backend_*() suite of functions to
use the PGPROC backend IDs instead. While we could instead
introduce a new function for retrieving PGPROC backend IDs,
presenting two sets of backend IDs to users seems likely to cause
even more confusion than what already exists.
This is primarily useful for discovering the session that owns a
resource named with its PGPROC backend ID. For example, temporary
schema names include the PGPROC backend ID, and it might be
necessary to identify the session that owns a temporary table that
is putting the cluster in danger of transaction ID wraparound.
Author: Nathan Bossart
---
doc/src/sgml/monitoring.sgml | 10 +++---
src/backend/utils/activity/backend_status.c | 40 +++++++++++++++++++--
src/backend/utils/adt/pgstatfuncs.c | 11 +++---
src/include/utils/backend_status.h | 8 +++++
4 files changed, 57 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..47f2883576 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5488,10 +5488,11 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
information. In such cases, an older set of per-backend statistics
access functions can be used; these are shown in <xref
linkend="monitoring-stats-backend-funcs-table"/>.
- These access functions use a backend ID number, which ranges from one
- to the number of currently active backends.
+ These access functions use the process's backend ID number, which is an
+ internal identifier for the backend that is independent from its
+ <acronym>PID</acronym>.
The function <function>pg_stat_get_backend_idset</function> provides a
- convenient way to generate one row for each active backend for
+ convenient way to list all the active backends' ID numbers for
invoking these functions. For example, to show the <acronym>PID</acronym>s and
current queries of all backends:
@@ -5526,8 +5527,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<returnvalue>setof integer</returnvalue>
</para>
<para>
- Returns the set of currently active backend ID numbers (from 1 to the
- number of active backends).
+ Returns the set of currently active backend ID numbers.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index c7ed1e6d7a..159d022070 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -849,6 +849,7 @@ pgstat_read_current_status(void)
BackendIdGetTransactionIds(i,
&localentry->backend_xid,
&localentry->backend_xmin);
+ localentry->backend_id = i;
localentry++;
localappname += NAMEDATALEN;
@@ -1045,6 +1046,22 @@ pgstat_get_my_query_id(void)
return MyBEEntry->st_query_id;
}
+/* ----------
+ * cmp_lbestatus
+ *
+ * Comparison function for bsearch() on an array of LocalPgBackendStatus. The
+ * backend_id field is used to compare the arguments.
+ *
+ * ----------
+ */
+static int
+cmp_lbestatus(const void *a, const void *b)
+{
+ LocalPgBackendStatus *lbestatus1 = (LocalPgBackendStatus *) a;
+ LocalPgBackendStatus *lbestatus2 = (LocalPgBackendStatus *) b;
+
+ return lbestatus1->backend_id - lbestatus2->backend_id;
+}
/* ----------
* pgstat_fetch_stat_beentry() -
@@ -1052,6 +1069,10 @@ pgstat_get_my_query_id(void)
* Support function for the SQL-callable pgstat* functions. Returns
* our local copy of the current-activity entry for one backend.
*
+ * Unlike pgstat_fetch_stat_local_beentry(), the beid argument refers to the
+ * backend ID stored in the backend's PGPROC struct instead of its index in
+ * the localBackendStatusTable.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
@@ -1059,12 +1080,23 @@ pgstat_get_my_query_id(void)
PgBackendStatus *
pgstat_fetch_stat_beentry(int beid)
{
+ LocalPgBackendStatus key;
+ LocalPgBackendStatus *ret;
+
pgstat_read_current_status();
- if (beid < 1 || beid > localNumBackends)
+ if (beid < 1 || beid > NumBackendStatSlots)
return NULL;
- return &localBackendStatusTable[beid - 1].backendStatus;
+ key.backend_id = beid;
+ ret = (LocalPgBackendStatus *) bsearch(&key, localBackendStatusTable,
+ localNumBackends,
+ sizeof(LocalPgBackendStatus),
+ cmp_lbestatus);
+ if (ret)
+ return &ret->backendStatus;
+
+ return NULL;
}
@@ -1074,6 +1106,10 @@ pgstat_fetch_stat_beentry(int beid)
* Like pgstat_fetch_stat_beentry() but with locally computed additions (like
* xid and xmin values of the backend)
*
+ * Unlike pgstat_fetch_stat_beentry(), the beid argument refers to the index
+ * in the localBackendStatusTable instead of the backend ID stored in the
+ * backend's PGPROC struct.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index be15b4b2e5..56ca18b6d0 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -415,7 +415,6 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int *fctx;
- int32 result;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -436,12 +435,13 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
fctx = funcctx->user_fctx;
fctx[0] += 1;
- result = fctx[0];
- if (result <= fctx[1])
+ if (fctx[0] <= fctx[1])
{
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(fctx[0]);
+
/* do when there is more left to send */
- SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(localStatus->backend_id));
}
else
{
@@ -1180,7 +1180,8 @@ pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
result = 0;
for (beid = 1; beid <= tot_backends; beid++)
{
- PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(beid);
+ PgBackendStatus *beentry = &localStatus->backendStatus;
if (beentry && beentry->st_databaseid == dbid)
result++;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7403bca25e..f7c7c6e671 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -13,6 +13,7 @@
#include "datatype/timestamp.h"
#include "libpq/pqcomm.h"
#include "miscadmin.h" /* for BackendType */
+#include "storage/backendid.h"
#include "utils/backend_progress.h"
@@ -258,6 +259,13 @@ typedef struct LocalPgBackendStatus
* not.
*/
TransactionId backend_xmin;
+
+ /*
+ * The backend ID. For auxiliary processes, this will be set to a value
+ * greater than MaxBackends (since auxiliary processes do not have proper
+ * backend IDs).
+ */
+ BackendId backend_id;
} LocalPgBackendStatus;
--
2.25.1
--lrZ03NoBR/3+SXJZ--
^ permalink raw reply [nested|flat] 41+ messages in thread
* [PATCH v2 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs.
@ 2022-08-13 06:07 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Nathan Bossart @ 2022-08-13 06:07 UTC (permalink / raw)
Presently, these functions use the index of the backend's entry in
localBackendStatusTable as the backend ID. While this might bear
some resemblance to the backend ID of the backend's PGPROC struct,
it can quickly diverge as sessions connect and disconnect. This
change modifies the pg_stat_get_backend_*() suite of functions to
use the PGPROC backend IDs instead. While we could instead
introduce a new function for retrieving PGPROC backend IDs,
presenting two sets of backend IDs to users seems likely to cause
even more confusion than what already exists.
This is primarily useful for discovering the session that owns a
resource named with its PGPROC backend ID. For example, temporary
schema names include the PGPROC backend ID, and it might be
necessary to identify the session that owns a temporary table that
is putting the cluster in danger of transaction ID wraparound.
Author: Nathan Bossart
---
doc/src/sgml/monitoring.sgml | 8 ++---
src/backend/utils/activity/backend_status.c | 40 +++++++++++++++++++--
src/backend/utils/adt/pgstatfuncs.c | 11 +++---
src/include/utils/backend_status.h | 8 +++++
4 files changed, 55 insertions(+), 12 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..ecd0410229 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5488,10 +5488,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
information. In such cases, an older set of per-backend statistics
access functions can be used; these are shown in <xref
linkend="monitoring-stats-backend-funcs-table"/>.
- These access functions use a backend ID number, which ranges from one
- to the number of currently active backends.
+ These access functions use the process's backend ID number.
The function <function>pg_stat_get_backend_idset</function> provides a
- convenient way to generate one row for each active backend for
+ convenient way to list all the active backends' ID numbers for
invoking these functions. For example, to show the <acronym>PID</acronym>s and
current queries of all backends:
@@ -5526,8 +5525,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<returnvalue>setof integer</returnvalue>
</para>
<para>
- Returns the set of currently active backend ID numbers (from 1 to the
- number of active backends).
+ Returns the set of currently active backend ID numbers.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index c7ed1e6d7a..159d022070 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -849,6 +849,7 @@ pgstat_read_current_status(void)
BackendIdGetTransactionIds(i,
&localentry->backend_xid,
&localentry->backend_xmin);
+ localentry->backend_id = i;
localentry++;
localappname += NAMEDATALEN;
@@ -1045,6 +1046,22 @@ pgstat_get_my_query_id(void)
return MyBEEntry->st_query_id;
}
+/* ----------
+ * cmp_lbestatus
+ *
+ * Comparison function for bsearch() on an array of LocalPgBackendStatus. The
+ * backend_id field is used to compare the arguments.
+ *
+ * ----------
+ */
+static int
+cmp_lbestatus(const void *a, const void *b)
+{
+ LocalPgBackendStatus *lbestatus1 = (LocalPgBackendStatus *) a;
+ LocalPgBackendStatus *lbestatus2 = (LocalPgBackendStatus *) b;
+
+ return lbestatus1->backend_id - lbestatus2->backend_id;
+}
/* ----------
* pgstat_fetch_stat_beentry() -
@@ -1052,6 +1069,10 @@ pgstat_get_my_query_id(void)
* Support function for the SQL-callable pgstat* functions. Returns
* our local copy of the current-activity entry for one backend.
*
+ * Unlike pgstat_fetch_stat_local_beentry(), the beid argument refers to the
+ * backend ID stored in the backend's PGPROC struct instead of its index in
+ * the localBackendStatusTable.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
@@ -1059,12 +1080,23 @@ pgstat_get_my_query_id(void)
PgBackendStatus *
pgstat_fetch_stat_beentry(int beid)
{
+ LocalPgBackendStatus key;
+ LocalPgBackendStatus *ret;
+
pgstat_read_current_status();
- if (beid < 1 || beid > localNumBackends)
+ if (beid < 1 || beid > NumBackendStatSlots)
return NULL;
- return &localBackendStatusTable[beid - 1].backendStatus;
+ key.backend_id = beid;
+ ret = (LocalPgBackendStatus *) bsearch(&key, localBackendStatusTable,
+ localNumBackends,
+ sizeof(LocalPgBackendStatus),
+ cmp_lbestatus);
+ if (ret)
+ return &ret->backendStatus;
+
+ return NULL;
}
@@ -1074,6 +1106,10 @@ pgstat_fetch_stat_beentry(int beid)
* Like pgstat_fetch_stat_beentry() but with locally computed additions (like
* xid and xmin values of the backend)
*
+ * Unlike pgstat_fetch_stat_beentry(), the beid argument refers to the index
+ * in the localBackendStatusTable instead of the backend ID stored in the
+ * backend's PGPROC struct.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index d9e2a79382..981bd2b372 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -415,7 +415,6 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
{
FuncCallContext *funcctx;
int *fctx;
- int32 result;
/* stuff done only on the first call of the function */
if (SRF_IS_FIRSTCALL())
@@ -436,12 +435,13 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
fctx = funcctx->user_fctx;
fctx[0] += 1;
- result = fctx[0];
- if (result <= fctx[1])
+ if (fctx[0] <= fctx[1])
{
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(fctx[0]);
+
/* do when there is more left to send */
- SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(localStatus->backend_id));
}
else
{
@@ -1187,7 +1187,8 @@ pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
result = 0;
for (beid = 1; beid <= tot_backends; beid++)
{
- PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(beid);
+ PgBackendStatus *beentry = &localStatus->backendStatus;
if (beentry && beentry->st_databaseid == dbid)
result++;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7403bca25e..f7c7c6e671 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -13,6 +13,7 @@
#include "datatype/timestamp.h"
#include "libpq/pqcomm.h"
#include "miscadmin.h" /* for BackendType */
+#include "storage/backendid.h"
#include "utils/backend_progress.h"
@@ -258,6 +259,13 @@ typedef struct LocalPgBackendStatus
* not.
*/
TransactionId backend_xmin;
+
+ /*
+ * The backend ID. For auxiliary processes, this will be set to a value
+ * greater than MaxBackends (since auxiliary processes do not have proper
+ * backend IDs).
+ */
+ BackendId backend_id;
} LocalPgBackendStatus;
--
2.25.1
--xHFwDpU9dbj6ez1V--
^ permalink raw reply [nested|flat] 41+ messages in thread
* [PATCH v1 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs.
@ 2022-08-13 06:07 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Nathan Bossart @ 2022-08-13 06:07 UTC (permalink / raw)
Presently, these functions use the index of the backend's entry in
localBackendStatusTable as the backend ID. While this might bear
some resemblance to the backend ID of the backend's PGPROC struct,
it can quickly diverge as sessions connect and disconnect. This
change modifies the pg_stat_get_backend_*() suite of functions to
use the PGPROC backend IDs instead. While we could instead
introduce a new function for retrieving PGPROC backend IDs,
presenting two sets of backend IDs to users seems likely to cause
even more confusion than what already exists.
This is primarily useful for discovering the session that owns a
resource named with its PGPROC backend ID. For example, temporary
schema names include the PGPROC backend ID, and it might be
necessary to identify the session that owns a temporary table that
is putting the cluster in danger of transaction ID wraparound.
Author: Nathan Bossart
---
doc/src/sgml/monitoring.sgml | 8 ++---
src/backend/utils/activity/backend_status.c | 40 +++++++++++++++++++--
src/backend/utils/adt/pgstatfuncs.c | 7 ++--
src/include/utils/backend_status.h | 8 +++++
4 files changed, 54 insertions(+), 9 deletions(-)
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1d9509a2f6..ecd0410229 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -5488,10 +5488,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
information. In such cases, an older set of per-backend statistics
access functions can be used; these are shown in <xref
linkend="monitoring-stats-backend-funcs-table"/>.
- These access functions use a backend ID number, which ranges from one
- to the number of currently active backends.
+ These access functions use the process's backend ID number.
The function <function>pg_stat_get_backend_idset</function> provides a
- convenient way to generate one row for each active backend for
+ convenient way to list all the active backends' ID numbers for
invoking these functions. For example, to show the <acronym>PID</acronym>s and
current queries of all backends:
@@ -5526,8 +5525,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
<returnvalue>setof integer</returnvalue>
</para>
<para>
- Returns the set of currently active backend ID numbers (from 1 to the
- number of active backends).
+ Returns the set of currently active backend ID numbers.
</para></entry>
</row>
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index c7ed1e6d7a..159d022070 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -849,6 +849,7 @@ pgstat_read_current_status(void)
BackendIdGetTransactionIds(i,
&localentry->backend_xid,
&localentry->backend_xmin);
+ localentry->backend_id = i;
localentry++;
localappname += NAMEDATALEN;
@@ -1045,6 +1046,22 @@ pgstat_get_my_query_id(void)
return MyBEEntry->st_query_id;
}
+/* ----------
+ * cmp_lbestatus
+ *
+ * Comparison function for bsearch() on an array of LocalPgBackendStatus. The
+ * backend_id field is used to compare the arguments.
+ *
+ * ----------
+ */
+static int
+cmp_lbestatus(const void *a, const void *b)
+{
+ LocalPgBackendStatus *lbestatus1 = (LocalPgBackendStatus *) a;
+ LocalPgBackendStatus *lbestatus2 = (LocalPgBackendStatus *) b;
+
+ return lbestatus1->backend_id - lbestatus2->backend_id;
+}
/* ----------
* pgstat_fetch_stat_beentry() -
@@ -1052,6 +1069,10 @@ pgstat_get_my_query_id(void)
* Support function for the SQL-callable pgstat* functions. Returns
* our local copy of the current-activity entry for one backend.
*
+ * Unlike pgstat_fetch_stat_local_beentry(), the beid argument refers to the
+ * backend ID stored in the backend's PGPROC struct instead of its index in
+ * the localBackendStatusTable.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
@@ -1059,12 +1080,23 @@ pgstat_get_my_query_id(void)
PgBackendStatus *
pgstat_fetch_stat_beentry(int beid)
{
+ LocalPgBackendStatus key;
+ LocalPgBackendStatus *ret;
+
pgstat_read_current_status();
- if (beid < 1 || beid > localNumBackends)
+ if (beid < 1 || beid > NumBackendStatSlots)
return NULL;
- return &localBackendStatusTable[beid - 1].backendStatus;
+ key.backend_id = beid;
+ ret = (LocalPgBackendStatus *) bsearch(&key, localBackendStatusTable,
+ localNumBackends,
+ sizeof(LocalPgBackendStatus),
+ cmp_lbestatus);
+ if (ret)
+ return &ret->backendStatus;
+
+ return NULL;
}
@@ -1074,6 +1106,10 @@ pgstat_fetch_stat_beentry(int beid)
* Like pgstat_fetch_stat_beentry() but with locally computed additions (like
* xid and xmin values of the backend)
*
+ * Unlike pgstat_fetch_stat_beentry(), the beid argument refers to the index
+ * in the localBackendStatusTable instead of the backend ID stored in the
+ * backend's PGPROC struct.
+ *
* NB: caller is responsible for a check if the user is permitted to see
* this info (especially the querystring).
* ----------
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index d9e2a79382..7f06cba958 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -440,8 +440,10 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS)
if (result <= fctx[1])
{
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(fctx[0]);
+
/* do when there is more left to send */
- SRF_RETURN_NEXT(funcctx, Int32GetDatum(result));
+ SRF_RETURN_NEXT(funcctx, Int32GetDatum(localStatus->backend_id));
}
else
{
@@ -1187,7 +1189,8 @@ pg_stat_get_db_numbackends(PG_FUNCTION_ARGS)
result = 0;
for (beid = 1; beid <= tot_backends; beid++)
{
- PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid);
+ LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(beid);
+ PgBackendStatus *beentry = &localStatus->backendStatus;
if (beentry && beentry->st_databaseid == dbid)
result++;
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7403bca25e..f7c7c6e671 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -13,6 +13,7 @@
#include "datatype/timestamp.h"
#include "libpq/pqcomm.h"
#include "miscadmin.h" /* for BackendType */
+#include "storage/backendid.h"
#include "utils/backend_progress.h"
@@ -258,6 +259,13 @@ typedef struct LocalPgBackendStatus
* not.
*/
TransactionId backend_xmin;
+
+ /*
+ * The backend ID. For auxiliary processes, this will be set to a value
+ * greater than MaxBackends (since auxiliary processes do not have proper
+ * backend IDs).
+ */
+ BackendId backend_id;
} LocalPgBackendStatus;
--
2.25.1
--YiEDa0DAkWCtVeE4--
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
@ 2025-03-04 06:52 vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-09 03:29 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 41+ messages in thread
From: vignesh C @ 2025-03-04 06:52 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Mon, 3 Mar 2025 at 16:41, Amit Kapila <[email protected]> wrote:
>
> On Mon, Mar 3, 2025 at 2:30 PM vignesh C <[email protected]> wrote:
> >
> > On Tue, 25 Feb 2025 at 15:32, vignesh C <[email protected]> wrote:
> > >
> > > The attached script has the script that was used for testing. Here the
> > > NUM_RECORDS count should be changed accordingly for each of the tests
> > > and while running the test with the patch change uncomment the drop
> > > publication command.
> >
> > I have done further analysis on the test and changed the test to
> > compare it better with HEAD. The execution time is in milliseconds.
> > Brach/records | 100 | 1000 | 10000 | 100000 | 1000000
> > Head | 10.43 | 15.86 | 64.44 | 550.56 | 8991.04
> > Patch | 11.35 | 17.26 | 73.50 | 640.21 | 10104.72
> > % diff | -8.82 | -8.85 | -14.08 | -16.28 | -12.38
> >
> > There is a performance degradation in the range of 8.8 to 16.2 percent.
> >
>
> - /* Validate the entry */
> - if (!entry->replicate_valid)
> + /*
> + * If the publication is invalid, check for updates.
> + * This optimization ensures that the next block, which queries the system
> + * tables and builds the relation entry, runs only if a new publication was
> + * created.
> + */
> + if (!publications_valid && data->publications)
> + {
> + bool skipped_pub = false;
> + List *publications;
> +
> + publications = LoadPublications(data->publication_names, &skipped_pub);
>
> The publications_valid flag indicates whether the publications cache
> is valid or not; the flag is set to false for any invalidation in the
> pg_publication catalog. I wonder that instead of using the same flag
> what if we use a separate publications_skipped flag? If that works,
> you don't even need to change the current location where we
> LoadPublications.
There is almost negligible dip with the above suggested way, the test
results for the same is given below(execution time is in milli
seconds):
Brach/records | 100 | 1000 | 10000 | 100000 | 1000000
Head | 10.25 | 15.85 | 65.53 | 569.15 | 9194.19
Patch | 10.25 | 15.84 | 65.91 | 571.75 | 9208.66
% diff | 0.00 | 0.06 | -0.58 | -0.46 | -0.16
There is a performance dip in the range of 0 to 0.58 percent.
The attached patch has the changes for the same. The test script used
is also attached.
Regards,
Vignesh
Attachments:
[application/x-patch] v5-0001-Fix-logical-replication-breakage-after.patch (4.4K, ../../CALDaNm2Xkm1M-ik2RLJZ9rMhW2zW2GRLL6ePyZJbXcAjOVwzXg@mail.gmail.com/2-v5-0001-Fix-logical-replication-breakage-after.patch)
download | inline diff:
From 8f2bca5afc03f3e34b2a0955415a702cbe6aef79 Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Mon, 3 Mar 2025 11:54:27 +0530
Subject: [PATCH v5] Fix logical replication breakage after ALTER SUBSCRIPTION
... SET PUBLICATION
Altering a subscription with `ALTER SUBSCRIPTION ... SET PUBLICATION`
could cause logical replication to break under certain conditions. When
the apply worker restarts after executing SET PUBLICATION, it continues
using the existing replication slot and origin. If the replication origin
was not updated before the restart, the WAL start location could point to
a position prior to the existence of the specified publication, leading to
persistent start of apply worker and reporting errors.
This patch skips loading the publication if the publication does not exist
and loads the publication and updates the relation entry when the publication
gets created.
Discussion: https://www.postgresql.org/message-id/flat/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAA4eK1+T-ETXeRM4DHWzGxBpKafLCp__5bPA_QZfFQp7-0wj4Q@mail.gmail.com
---
src/backend/replication/pgoutput/pgoutput.c | 41 +++++++++++++++++----
1 file changed, 33 insertions(+), 8 deletions(-)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 7d464f656aa..46793efe2b5 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -81,8 +81,9 @@ static void pgoutput_stream_prepare_txn(LogicalDecodingContext *ctx,
ReorderBufferTXN *txn, XLogRecPtr prepare_lsn);
static bool publications_valid;
+static bool publications_updated;
-static List *LoadPublications(List *pubnames);
+static List *LoadPublications(List *pubnames, bool *skipped);
static void publication_invalidation_cb(Datum arg, int cacheid,
uint32 hashvalue);
static void send_repl_origin(LogicalDecodingContext *ctx,
@@ -1762,9 +1763,13 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
/*
* Load publications from the list of publication names.
+ *
+ * Here, we just skip the publications that don't exist yet. 'skipped'
+ * will be true if we find any publication from the given list that doesn't
+ * exist.
*/
static List *
-LoadPublications(List *pubnames)
+LoadPublications(List *pubnames, bool *skipped)
{
List *result = NIL;
ListCell *lc;
@@ -1772,9 +1777,15 @@ LoadPublications(List *pubnames)
foreach(lc, pubnames)
{
char *pubname = (char *) lfirst(lc);
- Publication *pub = GetPublicationByName(pubname, false);
+ Publication *pub = GetPublicationByName(pubname, true);
- result = lappend(result, pub);
+ if (pub)
+ result = lappend(result, pub);
+ else
+ {
+ elog(DEBUG1, "skipped loading publication: %s", pubname);
+ *skipped = true;
+ }
}
return result;
@@ -1789,6 +1800,7 @@ static void
publication_invalidation_cb(Datum arg, int cacheid, uint32 hashvalue)
{
publications_valid = false;
+ publications_updated = true;
/*
* Also invalidate per-relation cache so that next time the filtering info
@@ -2053,8 +2065,12 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->attrmap = NULL;
}
- /* Validate the entry */
- if (!entry->replicate_valid)
+ /*
+ * Validate the entry only if the entry is not valid or in case the
+ * publications have been updated.
+ */
+ if (!entry->replicate_valid ||
+ (!publications_valid && publications_updated))
{
Oid schemaId = get_rel_namespace(relid);
List *pubids = GetRelationPublications(relid);
@@ -2071,16 +2087,25 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
bool am_partition = get_rel_relispartition(relid);
char relkind = get_rel_relkind(relid);
List *rel_publications = NIL;
+ bool skipped_pub = false;
/* Reload publications if needed before use. */
if (!publications_valid)
{
+ publications_updated = false;
+
MemoryContextReset(data->pubctx);
oldctx = MemoryContextSwitchTo(data->pubctx);
- data->publications = LoadPublications(data->publication_names);
+ data->publications = LoadPublications(data->publication_names, &skipped_pub);
MemoryContextSwitchTo(oldctx);
- publications_valid = true;
+
+ /*
+ * We don't consider the publications to be valid till we have
+ * information of all the publications.
+ */
+ if (!skipped_pub)
+ publications_valid = true;
}
/*
--
2.43.0
[text/x-sh] test_pg_recvlogical.sh (1.3K, ../../CALDaNm2Xkm1M-ik2RLJZ9rMhW2zW2GRLL6ePyZJbXcAjOVwzXg@mail.gmail.com/3-test_pg_recvlogical.sh)
download | inline:
#!/bin/bash
#####
SLOT_NAME=test
PLUGIN_NAME=pgoutput
NUM_RECORDS=100000
LOOP=10
#####
for i in `seq 1 $LOOP`
do
# Cleanup previous result
pg_ctl stop -D data
rm -rf data logfile
# Initialize an instance
initdb -D data -U postgres -c wal_level=logical
# Start the instance
pg_ctl -D data -l logfile start
# Create a table
psql -U postgres -c "CREATE TABLE foo (id int);"
psql -U postgres -c "CREATE publication pub1 for table foo;"
psql -U postgres -c "CREATE publication pub2 for table foo;"
#psql -U postgres -c "drop publication pub1;"
# Create a replication slot
psql -U postgres -c "SELECT * FROM pg_create_logical_replication_slot('$SLOT_NAME', '$PLUGIN_NAME')"
# Insert tuples (this transaction will be decoded)
psql -U postgres -c "INSERT INTO foo VALUES (generate_series(1, $NUM_RECORDS))"
# Confirm current WAL location
WAL_POS=$(psql -qtAX -U postgres -c "SELECT * FROM pg_current_wal_lsn()")
t1=$(($(date +%s%N)/1000))
echo $t1 > run_${i}.dat
# Run pg_recvlogical till the current WAL location
time pg_recvlogical -d postgres -U postgres --start -S $SLOT_NAME -E $WAL_POS -f - -o publication_names='pub1,pub2' -o proto_version=4 ) &>> run_${i}.dat
t2=$(($(date +%s%N)/1000))
echo $t2 >> run_${i}.dat
t3=$((t2-t1))
echo "execution time=$t3" >> run_${i}.dat
done
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-04 13:24 ` vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
1 sibling, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-03-04 13:24 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, 4 Mar 2025 at 12:22, vignesh C <[email protected]> wrote:
>
> On Mon, 3 Mar 2025 at 16:41, Amit Kapila <[email protected]> wrote:
> >
> > On Mon, Mar 3, 2025 at 2:30 PM vignesh C <[email protected]> wrote:
> > >
> > > On Tue, 25 Feb 2025 at 15:32, vignesh C <[email protected]> wrote:
> > > >
> > > > The attached script has the script that was used for testing. Here the
> > > > NUM_RECORDS count should be changed accordingly for each of the tests
> > > > and while running the test with the patch change uncomment the drop
> > > > publication command.
> > >
> > > I have done further analysis on the test and changed the test to
> > > compare it better with HEAD. The execution time is in milliseconds.
> > > Brach/records | 100 | 1000 | 10000 | 100000 | 1000000
> > > Head | 10.43 | 15.86 | 64.44 | 550.56 | 8991.04
> > > Patch | 11.35 | 17.26 | 73.50 | 640.21 | 10104.72
> > > % diff | -8.82 | -8.85 | -14.08 | -16.28 | -12.38
> > >
> > > There is a performance degradation in the range of 8.8 to 16.2 percent.
> > >
> >
> > - /* Validate the entry */
> > - if (!entry->replicate_valid)
> > + /*
> > + * If the publication is invalid, check for updates.
> > + * This optimization ensures that the next block, which queries the system
> > + * tables and builds the relation entry, runs only if a new publication was
> > + * created.
> > + */
> > + if (!publications_valid && data->publications)
> > + {
> > + bool skipped_pub = false;
> > + List *publications;
> > +
> > + publications = LoadPublications(data->publication_names, &skipped_pub);
> >
> > The publications_valid flag indicates whether the publications cache
> > is valid or not; the flag is set to false for any invalidation in the
> > pg_publication catalog. I wonder that instead of using the same flag
> > what if we use a separate publications_skipped flag? If that works,
> > you don't even need to change the current location where we
> > LoadPublications.
>
> There is almost negligible dip with the above suggested way, the test
> results for the same is given below(execution time is in milli
> seconds):
> Brach/records | 100 | 1000 | 10000 | 100000 | 1000000
> Head | 10.25 | 15.85 | 65.53 | 569.15 | 9194.19
> Patch | 10.25 | 15.84 | 65.91 | 571.75 | 9208.66
> % diff | 0.00 | 0.06 | -0.58 | -0.46 | -0.16
>
> There is a performance dip in the range of 0 to 0.58 percent.
> The attached patch has the changes for the same. The test script used
> is also attached.
On further thinking, I felt the use of publications_updated variable
is not required we can use publications_valid itself which will be set
if the publication system table is invalidated. Here is a patch for
the same.
Regards,
Vignesh
Attachments:
[text/x-patch] v6-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch (2.6K, ../../CALDaNm3Ub=T1c78kDF3y5Wcna3WrzoiEeVL8_atbSFSiVhj8FQ@mail.gmail.com/2-v6-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch)
download | inline diff:
From 856403e4943954267b0e9b4fa96ccc6955838210 Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Mon, 3 Mar 2025 11:54:27 +0530
Subject: [PATCH v6] Fix logical replication breakage after ALTER SUBSCRIPTION
... SET PUBLICATION
Altering a subscription with `ALTER SUBSCRIPTION ... SET PUBLICATION`
could cause logical replication to break under certain conditions. When
the apply worker restarts after executing SET PUBLICATION, it continues
using the existing replication slot and origin. If the replication origin
was not updated before the restart, the WAL start location could point to
a position prior to the existence of the specified publication, leading to
persistent start of apply worker and reporting errors.
This patch skips loading the publication if the publication does not exist
and loads the publication and updates the relation entry when the publication
gets created.
Discussion: https://www.postgresql.org/message-id/flat/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAA4eK1+T-ETXeRM4DHWzGxBpKafLCp__5bPA_QZfFQp7-0wj4Q@mail.gmail.com
---
src/backend/replication/pgoutput/pgoutput.c | 16 ++++++++++++----
1 file changed, 12 insertions(+), 4 deletions(-)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 7d464f656aa..4cfd2b02023 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1762,6 +1762,8 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
/*
* Load publications from the list of publication names.
+ *
+ * Here, we just skip the publications that don't exist yet.
*/
static List *
LoadPublications(List *pubnames)
@@ -1772,9 +1774,12 @@ LoadPublications(List *pubnames)
foreach(lc, pubnames)
{
char *pubname = (char *) lfirst(lc);
- Publication *pub = GetPublicationByName(pubname, false);
+ Publication *pub = GetPublicationByName(pubname, true);
- result = lappend(result, pub);
+ if (pub)
+ result = lappend(result, pub);
+ else
+ elog(WARNING, "skipped loading publication: %s", pubname);
}
return result;
@@ -2053,8 +2058,11 @@ get_rel_sync_entry(PGOutputData *data, Relation relation)
entry->attrmap = NULL;
}
- /* Validate the entry */
- if (!entry->replicate_valid)
+ /*
+ * Validate the entry if (a) the entry is not valid, or (b) there is a
+ * change in the publications.
+ */
+ if (!entry->replicate_valid || !publications_valid)
{
Oid schemaId = get_rel_namespace(relid);
List *pubids = GetRelationPublications(relid);
--
2.43.0
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-10 04:02 ` Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
0 siblings, 2 replies; 41+ messages in thread
From: Amit Kapila @ 2025-03-10 04:02 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
>
> On further thinking, I felt the use of publications_updated variable
> is not required we can use publications_valid itself which will be set
> if the publication system table is invalidated. Here is a patch for
> the same.
>
The patch relies on the fact that whenever a publication's data is
invalidated, it will also invalidate all the RelSyncEntires as per
publication_invalidation_cb. But note that we are discussing removing
that inefficiency in the thread [1]. So, we should try to rebuild the
entry when we have skipped the required publication previously.
Apart from this, please consider updating the docs, as mentioned in my
response to Sawada-San's email.
BTW, I am planning to commit this only on HEAD as this is a behavior
change. Please let me know if you guys think otherwise.
[1] - https://www.postgresql.org/message-id/OSCPR01MB14966C09AA201EFFA706576A7F5C92%40OSCPR01MB14966.jpnpr...
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-03-10 04:44 ` Dilip Kumar <[email protected]>
2025-03-10 05:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
1 sibling, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-10 04:44 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Mar 10, 2025 at 9:33 AM Amit Kapila <[email protected]> wrote:
> On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
> >
> > On further thinking, I felt the use of publications_updated variable
> > is not required we can use publications_valid itself which will be set
> > if the publication system table is invalidated. Here is a patch for
> > the same.
> >
>
> The patch relies on the fact that whenever a publication's data is
> invalidated, it will also invalidate all the RelSyncEntires as per
> publication_invalidation_cb. But note that we are discussing removing
> that inefficiency in the thread [1]. So, we should try to rebuild the
> entry when we have skipped the required publication previously.
>
> Apart from this, please consider updating the docs, as mentioned in my
> response to Sawada-San's email.
>
I'm not sure I fully understand it, but based on your previous email and
the initial email from Vignesh, if IIUC, the issue occurs when a
publication is created after a certain LSN. When ALTER SUBSCRIPTION ... SET
PUBLICATION is executed, the subscriber workers restart and request the
changes based on restart_lsn, which is at an earlier LSN in the WAL than
the LSN at which the publication was created. This leads to an error, and
we are addressing this behavior as part of the fix by skipping the changes
which are between the restart_lsn of subscriber and the lsn at which
publication is created and this behavior looks fine.
BTW, I am planning to commit this only on HEAD as this is a behavior
> change. Please let me know if you guys think otherwise.
>
Somehow this looks like a bug fix which should be backported no? Am I
missing something?
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-10 05:24 ` Amit Kapila <[email protected]>
2025-03-11 04:17 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-03-10 05:24 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Mar 10, 2025 at 10:15 AM Dilip Kumar <[email protected]> wrote:
>
> On Mon, Mar 10, 2025 at 9:33 AM Amit Kapila <[email protected]> wrote:
>>
>> On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
>> >
>> > On further thinking, I felt the use of publications_updated variable
>> > is not required we can use publications_valid itself which will be set
>> > if the publication system table is invalidated. Here is a patch for
>> > the same.
>> >
>>
>> The patch relies on the fact that whenever a publication's data is
>> invalidated, it will also invalidate all the RelSyncEntires as per
>> publication_invalidation_cb. But note that we are discussing removing
>> that inefficiency in the thread [1]. So, we should try to rebuild the
>> entry when we have skipped the required publication previously.
>>
>> Apart from this, please consider updating the docs, as mentioned in my
>> response to Sawada-San's email.
>
>
> I'm not sure I fully understand it, but based on your previous email and the initial email from Vignesh, if IIUC, the issue occurs when a publication is created after a certain LSN. When ALTER SUBSCRIPTION ... SET PUBLICATION is executed, the subscriber workers restart and request the changes based on restart_lsn, which is at an earlier LSN in the WAL than the LSN at which the publication was created. This leads to an error, and we are addressing this behavior as part of the fix by skipping the changes which are between the restart_lsn of subscriber and the lsn at which publication is created and this behavior looks fine.
>
Yes, your understanding is correct, but note that as such, the patch
is simply skipping the missing publication. The skipped changes are
because those were on the table that is not part of any publication
w.r.t historic snapshot we have at the point of time.
>> BTW, I am planning to commit this only on HEAD as this is a behavior
>> change. Please let me know if you guys think otherwise.
>
>
> Somehow this looks like a bug fix which should be backported no? Am I missing something?
>
We can consider this a bug-fix and backpatch it, but I am afraid that
because this is a behavior change, some users may not like it. Also, I
don't remember seeing public reports for this behavior; that is
probably because it is hard to hit. FYI, we found this via BF
failures. So, I thought it would be better to get this field tested
via HEAD only at this point in time.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-10 05:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-03-11 04:17 ` Dilip Kumar <[email protected]>
2025-03-12 10:34 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-11 04:17 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Mon, Mar 10, 2025 at 10:54 AM Amit Kapila <[email protected]> wrote:
>
> On Mon, Mar 10, 2025 at 10:15 AM Dilip Kumar <[email protected]> wrote:
> >
> > On Mon, Mar 10, 2025 at 9:33 AM Amit Kapila <[email protected]> wrote:
> >>
> >> On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
> >> >
> >> > On further thinking, I felt the use of publications_updated variable
> >> > is not required we can use publications_valid itself which will be set
> >> > if the publication system table is invalidated. Here is a patch for
> >> > the same.
> >> >
> >>
> >> The patch relies on the fact that whenever a publication's data is
> >> invalidated, it will also invalidate all the RelSyncEntires as per
> >> publication_invalidation_cb. But note that we are discussing removing
> >> that inefficiency in the thread [1]. So, we should try to rebuild the
> >> entry when we have skipped the required publication previously.
> >>
> >> Apart from this, please consider updating the docs, as mentioned in my
> >> response to Sawada-San's email.
> >
> >
> > I'm not sure I fully understand it, but based on your previous email and the initial email from Vignesh, if IIUC, the issue occurs when a publication is created after a certain LSN. When ALTER SUBSCRIPTION ... SET PUBLICATION is executed, the subscriber workers restart and request the changes based on restart_lsn, which is at an earlier LSN in the WAL than the LSN at which the publication was created. This leads to an error, and we are addressing this behavior as part of the fix by skipping the changes which are between the restart_lsn of subscriber and the lsn at which publication is created and this behavior looks fine.
> >
>
> Yes, your understanding is correct, but note that as such, the patch
> is simply skipping the missing publication. The skipped changes are
> because those were on the table that is not part of any publication
> w.r.t historic snapshot we have at the point of time.
So, it will skip loading the missing publication up to the LSN where
the publication is created and then load it from there, correct? Do we
have a test case for this? I couldn't find one in the latest patch or
in the email thread to demonstrate this behavior.
> >> BTW, I am planning to commit this only on HEAD as this is a behavior
> >> change. Please let me know if you guys think otherwise.
> >
> >
> > Somehow this looks like a bug fix which should be backported no? Am I missing something?
> >
>
> We can consider this a bug-fix and backpatch it, but I am afraid that
> because this is a behavior change, some users may not like it. Also, I
> don't remember seeing public reports for this behavior; that is
> probably because it is hard to hit. FYI, we found this via BF
> failures. So, I thought it would be better to get this field tested
> via HEAD only at this point in time.
At the moment, I don't have a strong opinion on this. Since no one has
encountered or reported this issue, it might be the case that it's not
affecting anyone, and we could simply backpatch without causing any
dissatisfaction. However, I'm fine with whatever others decide.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-10 05:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 04:17 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-12 10:34 ` Amit Kapila <[email protected]>
2025-03-12 21:51 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-03-12 10:34 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Mar 11, 2025 at 9:48 AM Dilip Kumar <[email protected]> wrote:
>
> On Mon, Mar 10, 2025 at 10:54 AM Amit Kapila <[email protected]> wrote:
> >
>
> > >> BTW, I am planning to commit this only on HEAD as this is a behavior
> > >> change. Please let me know if you guys think otherwise.
> > >
> > >
> > > Somehow this looks like a bug fix which should be backported no? Am I missing something?
> > >
> >
> > We can consider this a bug-fix and backpatch it, but I am afraid that
> > because this is a behavior change, some users may not like it. Also, I
> > don't remember seeing public reports for this behavior; that is
> > probably because it is hard to hit. FYI, we found this via BF
> > failures. So, I thought it would be better to get this field tested
> > via HEAD only at this point in time.
>
> At the moment, I don't have a strong opinion on this. Since no one has
> encountered or reported this issue, it might be the case that it's not
> affecting anyone, and we could simply backpatch without causing any
> dissatisfaction. However, I'm fine with whatever others decide.
>
Sawada-San, others, do you have an opinion on whether to backpatch this change?
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-10 05:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 04:17 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-12 10:34 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-03-12 21:51 ` Masahiko Sawada <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Masahiko Sawada @ 2025-03-12 21:51 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Dilip Kumar <[email protected]>; vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, Mar 12, 2025 at 3:34 AM Amit Kapila <[email protected]> wrote:
>
> On Tue, Mar 11, 2025 at 9:48 AM Dilip Kumar <[email protected]> wrote:
> >
> > On Mon, Mar 10, 2025 at 10:54 AM Amit Kapila <[email protected]> wrote:
> > >
> >
> > > >> BTW, I am planning to commit this only on HEAD as this is a behavior
> > > >> change. Please let me know if you guys think otherwise.
> > > >
> > > >
> > > > Somehow this looks like a bug fix which should be backported no? Am I missing something?
> > > >
> > >
> > > We can consider this a bug-fix and backpatch it, but I am afraid that
> > > because this is a behavior change, some users may not like it. Also, I
> > > don't remember seeing public reports for this behavior; that is
> > > probably because it is hard to hit. FYI, we found this via BF
> > > failures. So, I thought it would be better to get this field tested
> > > via HEAD only at this point in time.
> >
> > At the moment, I don't have a strong opinion on this. Since no one has
> > encountered or reported this issue, it might be the case that it's not
> > affecting anyone, and we could simply backpatch without causing any
> > dissatisfaction. However, I'm fine with whatever others decide.
> >
>
> Sawada-San, others, do you have an opinion on whether to backpatch this change?
I'm also afraid of backpatching it so I guess it would be better to
push it to only HEAD. I think if users have encountered and we see
reported the issue we can consider backpatching again. If regression
tests on backbranches continue to fail intermittently, probably we can
consider adding waits as the patch Osumi-san proposed[1]?
Regards,
[1] https://www.postgresql.org/message-id/TYCPR01MB83737A68CD5D554EA82BD7B9EDD39%40TYCPR01MB8373.jpnprd0...
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-03-11 10:30 ` vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
1 sibling, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-03-11 10:30 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
On Mon, 10 Mar 2025 at 09:33, Amit Kapila <[email protected]> wrote:
>
> On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
> >
> > On further thinking, I felt the use of publications_updated variable
> > is not required we can use publications_valid itself which will be set
> > if the publication system table is invalidated. Here is a patch for
> > the same.
> >
>
> The patch relies on the fact that whenever a publication's data is
> invalidated, it will also invalidate all the RelSyncEntires as per
> publication_invalidation_cb. But note that we are discussing removing
> that inefficiency in the thread [1]. So, we should try to rebuild the
> entry when we have skipped the required publication previously.
>
> Apart from this, please consider updating the docs, as mentioned in my
> response to Sawada-San's email.
The create subscription documentation already has "We allow
non-existent publications to be specified so that users can add those
later. This means pg_subscription can have non-existent publications."
and should be enough at [1]. Let me know if we need to add more
documentation.
Apart from this I have changed the log level that logs "skipped
loading publication" to WARNING as we log a warning "WARNING:
publications "pub2", "pub3" do not exist on the publisher" in case of
CREATE SUBSCRIPTION and looked similar to this. I can change it to a
different log level in case you feel this is not the right level.
Also I have added a test case for dilip's comment from [2].
The attached v7 version patch has the changes for the same.
[1] - https://www.postgresql.org/docs/devel/sql-createsubscription.html
[2] - https://www.postgresql.org/message-id/CAFiTN-tgUR6QLSs3UHK7gx4VP7cURGNkufA_xkrQLw9eCnbGQw%40mail.gma...
Regards,
Vignesh
Attachments:
[application/octet-stream] v7-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch (5.0K, ../../CALDaNm0T4uP0BTQHAzsmw3EtCoq=-y4Ryww9PZTrqiZeyA80XQ@mail.gmail.com/2-v7-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch)
download | inline diff:
From 1b838710fbbe16d412c5eff8a38d1aeb5590dd23 Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Mon, 3 Mar 2025 11:54:27 +0530
Subject: [PATCH v7] Fix logical replication breakage after ALTER SUBSCRIPTION
... SET PUBLICATION
Altering a subscription with `ALTER SUBSCRIPTION ... SET PUBLICATION`
could cause logical replication to break under certain conditions. When
the apply worker restarts after executing SET PUBLICATION, it continues
using the existing replication slot and origin. If the replication origin
was not updated before the restart, the WAL start location could point to
a position prior to the existence of the specified publication, leading to
persistent start of apply worker and reporting errors.
This patch skips loading the publication if the publication does not exist
and loads the publication and updates the relation entry when the publication
gets created.
Discussion: https://www.postgresql.org/message-id/flat/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAA4eK1+T-ETXeRM4DHWzGxBpKafLCp__5bPA_QZfFQp7-0wj4Q@mail.gmail.com
---
src/backend/replication/pgoutput/pgoutput.c | 26 ++++++++++++++++--
src/test/subscription/t/024_add_drop_pub.pl | 29 ++++++++++++++++++++-
2 files changed, 52 insertions(+), 3 deletions(-)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 9063af6e1df..991aa6f7282 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1762,6 +1762,8 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
/*
* Load publications from the list of publication names.
+ *
+ * Here, we just skip the publications that don't exist yet.
*/
static List *
LoadPublications(List *pubnames)
@@ -1772,9 +1774,29 @@ LoadPublications(List *pubnames)
foreach(lc, pubnames)
{
char *pubname = (char *) lfirst(lc);
- Publication *pub = GetPublicationByName(pubname, false);
+ Publication *pub = GetPublicationByName(pubname, true);
- result = lappend(result, pub);
+ if (pub)
+ result = lappend(result, pub);
+ else
+ {
+ /*
+ * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
+ * apply worker continues using the existing replication slot and
+ * origin after restarting. If the replication origin is not
+ * updated before the restart, the WAL start location may point to
+ * a position before the specified publication exists, causing
+ * persistent apply worker restarts and errors.
+ *
+ * This ensures that the publication is skipped if it does not
+ * exist and is loaded when the corresponding WAL record is
+ * encountered.
+ */
+ ereport(WARNING,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("skipped loading publication: %s", pubname),
+ errhint("If the publication already exists, ignore it as it will be loaded upon reaching the corresponding WAL record; otherwise, create it."));
+ }
}
return result;
diff --git a/src/test/subscription/t/024_add_drop_pub.pl b/src/test/subscription/t/024_add_drop_pub.pl
index 4428a3413db..dfdc9835e0c 100644
--- a/src/test/subscription/t/024_add_drop_pub.pl
+++ b/src/test/subscription/t/024_add_drop_pub.pl
@@ -1,7 +1,9 @@
# Copyright (c) 2021-2025, PostgreSQL Global Development Group
-# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION
+# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION and
+# ensures that dropping a publication associated with a subscription does not
+# disrupt existing logical replication.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
@@ -80,6 +82,31 @@ $result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_1");
is($result, qq(20|1|10), 'check initial data is copied to subscriber');
+# Ensure that dropping a publication associated with a subscription does not
+# disrupt existing logical replication. Instead, it should log a warning
+# while allowing replication to continue. Additionally, verify that replication
+# of the dropped publication resumes once the publication is recreated.
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_1;");
+
+my $offset = -s $node_publisher->logfile;
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_1 values(50)");
+
+# Verify that a warning is logged.
+$node_publisher->wait_for_log(
+ qr/WARNING: ( [A-Z0-9]+:)? skipped loading publication: tap_pub_1/, $offset);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_1 FOR TABLE tab_1");
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_1 values(11)");
+
+# Verify that the insert operation gets replicated to subscriber after
+# re-createion.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM tab_1");
+is($result, qq(21|1|11), 'check that the incremental data is replicated after the dropped publication is re-created');
+
# shutdown
$node_subscriber->stop('fast');
$node_publisher->stop('fast');
--
2.43.0
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-12 10:45 ` Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-12 10:45 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Mar 11, 2025 at 4:01 PM vignesh C <[email protected]> wrote:
>
> On Mon, 10 Mar 2025 at 09:33, Amit Kapila <[email protected]> wrote:
> >
> > On Tue, Mar 4, 2025 at 6:54 PM vignesh C <[email protected]> wrote:
> > >
> > > On further thinking, I felt the use of publications_updated variable
> > > is not required we can use publications_valid itself which will be set
> > > if the publication system table is invalidated. Here is a patch for
> > > the same.
> > >
> >
> > The patch relies on the fact that whenever a publication's data is
> > invalidated, it will also invalidate all the RelSyncEntires as per
> > publication_invalidation_cb. But note that we are discussing removing
> > that inefficiency in the thread [1]. So, we should try to rebuild the
> > entry when we have skipped the required publication previously.
> >
> > Apart from this, please consider updating the docs, as mentioned in my
> > response to Sawada-San's email.
>
> The create subscription documentation already has "We allow
> non-existent publications to be specified so that users can add those
> later. This means pg_subscription can have non-existent publications."
> and should be enough at [1]. Let me know if we need to add more
> documentation.
>
> Apart from this I have changed the log level that logs "skipped
> loading publication" to WARNING as we log a warning "WARNING:
> publications "pub2", "pub3" do not exist on the publisher" in case of
> CREATE SUBSCRIPTION and looked similar to this. I can change it to a
> different log level in case you feel this is not the right level.
>
> Also I have added a test case for dilip's comment from [2].
> The attached v7 version patch has the changes for the same.
>
Thanks, Vignesh, for adding the test. I believe you've tested the
effect of DROP PUBLICATION. However, I think we should also test the
behavior of ALTER SUBSCRIPTION...SET PUBLICATION before creating the
PUBLICATION, and then create the PUBLICATION at a later stage.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-13 02:08 ` vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-03-13 02:08 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, 12 Mar 2025 at 16:15, Dilip Kumar <[email protected]> wrote:
>
> Thanks, Vignesh, for adding the test. I believe you've tested the
> effect of DROP PUBLICATION. However, I think we should also test the
> behavior of ALTER SUBSCRIPTION...SET PUBLICATION before creating the
> PUBLICATION, and then create the PUBLICATION at a later stage.
I felt having only one test case for this is enough, I have removed
the DROP PUBLICATION test and added the SET PUBLICATION test. The
attached v8 version patch has the changes for the same.
Regards,
Vignesh
Attachments:
[text/x-patch] v8-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch (5.5K, ../../CALDaNm3HYP5WJLV9cKWEXuGgWYSLFOs-=u0qf5OD4Zyb87=6gw@mail.gmail.com/2-v8-0001-Fix-logical-replication-breakage-after-ALTER-SUBS.patch)
download | inline diff:
From c2f806ce866e3b0dfcebcd639683627a83e4e35a Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Mon, 3 Mar 2025 11:54:27 +0530
Subject: [PATCH v8] Fix logical replication breakage after ALTER SUBSCRIPTION
... SET PUBLICATION
Altering a subscription with `ALTER SUBSCRIPTION ... SET PUBLICATION`
could cause logical replication to break under certain conditions. When
the apply worker restarts after executing SET PUBLICATION, it continues
using the existing replication slot and origin. If the replication origin
was not updated before the restart, the WAL start location could point to
a position prior to the existence of the specified publication, leading to
persistent start of apply worker and reporting errors.
This patch skips loading the publication if the publication does not exist
and loads the publication and updates the relation entry when the publication
gets created.
Discussion: https://www.postgresql.org/message-id/flat/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAA4eK1+T-ETXeRM4DHWzGxBpKafLCp__5bPA_QZfFQp7-0wj4Q@mail.gmail.com
---
src/backend/replication/pgoutput/pgoutput.c | 26 +++++++++++-
src/test/subscription/t/024_add_drop_pub.pl | 44 ++++++++++++++++++++-
2 files changed, 67 insertions(+), 3 deletions(-)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index 9063af6e1df..991aa6f7282 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1762,6 +1762,8 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
/*
* Load publications from the list of publication names.
+ *
+ * Here, we just skip the publications that don't exist yet.
*/
static List *
LoadPublications(List *pubnames)
@@ -1772,9 +1774,29 @@ LoadPublications(List *pubnames)
foreach(lc, pubnames)
{
char *pubname = (char *) lfirst(lc);
- Publication *pub = GetPublicationByName(pubname, false);
+ Publication *pub = GetPublicationByName(pubname, true);
- result = lappend(result, pub);
+ if (pub)
+ result = lappend(result, pub);
+ else
+ {
+ /*
+ * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
+ * apply worker continues using the existing replication slot and
+ * origin after restarting. If the replication origin is not
+ * updated before the restart, the WAL start location may point to
+ * a position before the specified publication exists, causing
+ * persistent apply worker restarts and errors.
+ *
+ * This ensures that the publication is skipped if it does not
+ * exist and is loaded when the corresponding WAL record is
+ * encountered.
+ */
+ ereport(WARNING,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("skipped loading publication: %s", pubname),
+ errhint("If the publication already exists, ignore it as it will be loaded upon reaching the corresponding WAL record; otherwise, create it."));
+ }
}
return result;
diff --git a/src/test/subscription/t/024_add_drop_pub.pl b/src/test/subscription/t/024_add_drop_pub.pl
index 4428a3413db..4eb17f45ec3 100644
--- a/src/test/subscription/t/024_add_drop_pub.pl
+++ b/src/test/subscription/t/024_add_drop_pub.pl
@@ -1,7 +1,9 @@
# Copyright (c) 2021-2025, PostgreSQL Global Development Group
-# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION
+# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION and
+# ensures that dropping a publication associated with a subscription does not
+# disrupt existing logical replication.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
@@ -80,6 +82,46 @@ $result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_1");
is($result, qq(20|1|10), 'check initial data is copied to subscriber');
+# Ensure that setting a missing publication to the subscription does not
+# disrupt existing logical replication. Instead, it should log a warning
+# while allowing replication to continue. Additionally, verify that replication
+# resumes after the missing publication is created for the publication table.
+
+# Create table on publisher and subscriber
+$node_publisher->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
+
+# Set the subscription with a missing publication
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_3");
+
+my $offset = -s $node_publisher->logfile;
+
+$node_publisher->safe_psql('postgres',"INSERT INTO tab_3 values(1)");
+
+# Verify that a warning is logged.
+$node_publisher->wait_for_log(
+ qr/WARNING: ( [A-Z0-9]+:)? skipped loading publication: tap_pub_3/, $offset);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_3 FOR TABLE tab_3");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION");
+
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_3 values(2)");
+
+$node_publisher->wait_for_catchup('tap_sub');
+
+# Verify that the insert operation gets replicated to subscriber after
+# publication is created.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM tab_3");
+is($result, qq(1
+2), 'check that the incremental data is replicated after the publication is created');
+
# shutdown
$node_subscriber->stop('fast');
$node_publisher->stop('fast');
--
2.43.0
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-13 03:48 ` Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-13 03:48 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Mar 13, 2025 at 7:38 AM vignesh C <[email protected]> wrote:
>
> On Wed, 12 Mar 2025 at 16:15, Dilip Kumar <[email protected]> wrote:
> >
> > Thanks, Vignesh, for adding the test. I believe you've tested the
> > effect of DROP PUBLICATION. However, I think we should also test the
> > behavior of ALTER SUBSCRIPTION...SET PUBLICATION before creating the
> > PUBLICATION, and then create the PUBLICATION at a later stage.
>
> I felt having only one test case for this is enough, I have removed
> the DROP PUBLICATION test and added the SET PUBLICATION test. The
> attached v8 version patch has the changes for the same.
Thanks looks good to me.
While looking at the patch, I have a few comments/questions
+ if (pub)
+ result = lappend(result, pub);
+ else
+ {
+ /*
+ * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
+ * apply worker continues using the existing replication slot and
+ * origin after restarting. If the replication origin is not
+ * updated before the restart, the WAL start location may point to
+ * a position before the specified publication exists, causing
+ * persistent apply worker restarts and errors.
+ *
+ * This ensures that the publication is skipped if it does not
+ * exist and is loaded when the corresponding WAL record is
+ * encountered.
+ */
+ ereport(WARNING,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("skipped loading publication: %s", pubname),
+ errhint("If the publication already exists, ignore it as it will be
loaded upon reaching the corresponding WAL record; otherwise, create
it."));
+ }
This comment focuses on a specific use case regarding the problem with
'ALTER SUBSCRIPTION ... SET PUBLICATION,' but in reality, we are
addressing a more general case where the user is trying to SET
PUBLICATION or even CREATE SUBSCRIPTION, and some publications are
missing. Wouldn't it be better to rephrase the comment?
2. + errhint("If the publication already exists, ignore it as it will
be loaded upon reaching the corresponding WAL record; otherwise,
create it."));
Is this hint correct? This is a question rather than a comment: When
we reach a particular WAL where the publication was created, will the
publication automatically load, or does the user need to REFRESH the
publications?
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-13 05:19 ` vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-03-13 05:19 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, 13 Mar 2025 at 09:18, Dilip Kumar <[email protected]> wrote:
>
> Thanks looks good to me.
>
> While looking at the patch, I have a few comments/questions
>
> + if (pub)
> + result = lappend(result, pub);
> + else
> + {
> + /*
> + * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
> + * apply worker continues using the existing replication slot and
> + * origin after restarting. If the replication origin is not
> + * updated before the restart, the WAL start location may point to
> + * a position before the specified publication exists, causing
> + * persistent apply worker restarts and errors.
> + *
> + * This ensures that the publication is skipped if it does not
> + * exist and is loaded when the corresponding WAL record is
> + * encountered.
> + */
> + ereport(WARNING,
> + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> + errmsg("skipped loading publication: %s", pubname),
> + errhint("If the publication already exists, ignore it as it will be
> loaded upon reaching the corresponding WAL record; otherwise, create
> it."));
> + }
>
> This comment focuses on a specific use case regarding the problem with
> 'ALTER SUBSCRIPTION ... SET PUBLICATION,' but in reality, we are
> addressing a more general case where the user is trying to SET
> PUBLICATION or even CREATE SUBSCRIPTION, and some publications are
> missing. Wouldn't it be better to rephrase the comment?
How about a comment something like below:
/*
* In 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
* 'CREATE SUBSCRIPTION', if the replication origin is not updated
* before the worker exits, the WAL start location might point to a
* position before the publication's WAL record. This can lead to
* persistent apply worker restarts and errors.
*
* Additionally, dropping a subscription's publication should not
* disrupt logical replication.
*
* This ensures that a missing publication is skipped and loaded
* when its corresponding WAL record is encountered.
*/
ereport(WARNING,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("skipped loading publication: %s", pubname),
errhint("If the publication is missing, create and refresh it.
Otherwise, wait for the slot to reach the WAL record, then refresh"));
> 2. + errhint("If the publication already exists, ignore it as it will
> be loaded upon reaching the corresponding WAL record; otherwise,
> create it."));
>
> Is this hint correct? This is a question rather than a comment: When
> we reach a particular WAL where the publication was created, will the
> publication automatically load, or does the user need to REFRESH the
> publications?
Users need to refresh the publication in case the relation is not
already added to pg_subscription_rel and apply incremental changes.
How about an error hint like:
"If the publication is missing, create and refresh it. Otherwise, wait
for the slot to reach the WAL record for the created publication, then
refresh"
Regards,
Vignesh
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-13 06:13 ` Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-13 06:13 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Mar 13, 2025 at 10:49 AM vignesh C <[email protected]> wrote:
>
> On Thu, 13 Mar 2025 at 09:18, Dilip Kumar <[email protected]> wrote:
> >
> > Thanks looks good to me.
> >
> > While looking at the patch, I have a few comments/questions
> >
> > + if (pub)
> > + result = lappend(result, pub);
> > + else
> > + {
> > + /*
> > + * When executing 'ALTER SUBSCRIPTION ... SET PUBLICATION', the
> > + * apply worker continues using the existing replication slot and
> > + * origin after restarting. If the replication origin is not
> > + * updated before the restart, the WAL start location may point to
> > + * a position before the specified publication exists, causing
> > + * persistent apply worker restarts and errors.
> > + *
> > + * This ensures that the publication is skipped if it does not
> > + * exist and is loaded when the corresponding WAL record is
> > + * encountered.
> > + */
> > + ereport(WARNING,
> > + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> > + errmsg("skipped loading publication: %s", pubname),
> > + errhint("If the publication already exists, ignore it as it will be
> > loaded upon reaching the corresponding WAL record; otherwise, create
> > it."));
> > + }
> >
> > This comment focuses on a specific use case regarding the problem with
> > 'ALTER SUBSCRIPTION ... SET PUBLICATION,' but in reality, we are
> > addressing a more general case where the user is trying to SET
> > PUBLICATION or even CREATE SUBSCRIPTION, and some publications are
> > missing. Wouldn't it be better to rephrase the comment?
>
> How about a comment something like below:
> /*
> * In 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
> * 'CREATE SUBSCRIPTION', if the replication origin is not updated
> * before the worker exits, the WAL start location might point to a
> * position before the publication's WAL record. This can lead to
> * persistent apply worker restarts and errors.
> *
> * Additionally, dropping a subscription's publication should not
> * disrupt logical replication.
> *
> * This ensures that a missing publication is skipped and loaded
> * when its corresponding WAL record is encountered.
> */
Looks fine, shall we add the missing publication point as well
something like below
/*
* In operations like 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
* 'CREATE SUBSCRIPTION', if the specified publication does not exist or
* if the replication origin is not updated before the worker exits,
* the WAL start location may point to a position prior to the publication's
* WAL record. This can cause persistent restarts and errors
* in the apply worker.
*
> * Additionally, dropping a subscription's publication should not
> * disrupt logical replication.
*
> * This ensures that a missing publication is skipped and loaded
> * when its corresponding WAL record is encountered.
*/
> ereport(WARNING,
> errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> errmsg("skipped loading publication: %s", pubname),
> errhint("If the publication is missing, create and refresh it.
> Otherwise, wait for the slot to reach the WAL record, then refresh"));
>
> > 2. + errhint("If the publication already exists, ignore it as it will
> > be loaded upon reaching the corresponding WAL record; otherwise,
> > create it."));
> >
> > Is this hint correct? This is a question rather than a comment: When
> > we reach a particular WAL where the publication was created, will the
> > publication automatically load, or does the user need to REFRESH the
> > publications?
>
> Users need to refresh the publication in case the relation is not
> already added to pg_subscription_rel and apply incremental changes.
> How about an error hint like:
> "If the publication is missing, create and refresh it. Otherwise, wait
> for the slot to reach the WAL record for the created publication, then
> refresh"
+1
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-13 09:50 ` Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-03-13 09:50 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Mar 13, 2025 at 11:43 AM Dilip Kumar <[email protected]> wrote:
>
> Looks fine, shall we add the missing publication point as well
> something like below
>
> /*
> * In operations like 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
> * 'CREATE SUBSCRIPTION', if the specified publication does not exist or
> * if the replication origin is not updated before the worker exits,
> * the WAL start location may point to a position prior to the publication's
> * WAL record. This can cause persistent restarts and errors
> * in the apply worker.
> *
I think that is too much related to pub-sub model, and ideally,
pgoutput should not care about it. I have written a comment
considering somebody using pgoutput decoding module via APIs.
> > * Additionally, dropping a subscription's publication should not
> > * disrupt logical replication.
> *
> > * This ensures that a missing publication is skipped and loaded
> > * when its corresponding WAL record is encountered.
> */
>
>
>
> > ereport(WARNING,
> > errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> > errmsg("skipped loading publication: %s", pubname),
> > errhint("If the publication is missing, create and refresh it.
> > Otherwise, wait for the slot to reach the WAL record, then refresh"));
> >
> > > 2. + errhint("If the publication already exists, ignore it as it will
> > > be loaded upon reaching the corresponding WAL record; otherwise,
> > > create it."));
> > >
> > > Is this hint correct? This is a question rather than a comment: When
> > > we reach a particular WAL where the publication was created, will the
> > > publication automatically load, or does the user need to REFRESH the
> > > publications?
> >
> > Users need to refresh the publication in case the relation is not
> > already added to pg_subscription_rel and apply incremental changes.
> > How about an error hint like:
> > "If the publication is missing, create and refresh it. Otherwise, wait
> > for the slot to reach the WAL record for the created publication, then
> > refresh"
>
I have tried to split this information into errdetail and errhint in
the attached. See and let me know what you think of the same.
--
With Regards,
Amit Kapila.
Attachments:
[application/octet-stream] v9-0001-Fix-ALTER-SUBSCRIPTION-.-SET-PUBLICATION-.-comman.patch (5.6K, ../../CAA4eK1L-4c2+v7NZB4jWDwtN-VT+UKAm9Vp2dzO2Tyg0HTJvbQ@mail.gmail.com/2-v9-0001-Fix-ALTER-SUBSCRIPTION-.-SET-PUBLICATION-.-comman.patch)
download | inline diff:
From de767940fe6f33bb8e12975428b827cfd183dcd5 Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Mon, 3 Mar 2025 11:54:27 +0530
Subject: [PATCH v9] Fix ALTER SUBSCRIPTION ... SET PUBLICATION ... command.
The problem is that ALTER SUBSCRIPTION ... SET PUBLICATION ... will lead
to restarting of apply worker and after the restart, the apply worker will
use the existing slot and replication origin corresponding to the
subscription. Now, it is possible that before the restart, the origin has
not been updated, and the WAL start location points to a location before
where PUBLICATION pointed to by SET PUBLICATION doesn't exist, and that
can lead to an error like: "ERROR: publication "pub1" does not exist".
Once this error occurs, apply worker will never be able to proceed and
will always return the same error.
We decided to skip loading the publication if the publication does not
exist. The publication is loaded later and updates the relation entry when
the publication gets created.
We decided not to backpatch this as this is a behaviour change, and we don't
see field reports. This problem has been found by intermittent buildfarm
failures.
Author: vignesh C <[email protected]>
Reviewed-by: Dilip Kumar <[email protected]>
Reviewed-by: Amit Kapila <[email protected]>
Reviewed-by: Masahiko Sawada <[email protected]>
Discussion: https://postgr.es/m/flat/CALDaNm0-n8FGAorM%2BbTxkzn%2BAOUyx5%3DL_XmnvOP6T24%2B-NcBKg%40mail.gmail.com
Discussion: https://postgr.es/m/CAA4eK1+T-ETXeRM4DHWzGxBpKafLCp__5bPA_QZfFQp7-0wj4Q@mail.gmail.com
---
src/backend/replication/pgoutput/pgoutput.c | 16 +++++++-
src/test/subscription/t/024_add_drop_pub.pl | 44 ++++++++++++++++++++-
2 files changed, 57 insertions(+), 3 deletions(-)
diff --git a/src/backend/replication/pgoutput/pgoutput.c b/src/backend/replication/pgoutput/pgoutput.c
index ed806c54300..8357bf8b4c0 100644
--- a/src/backend/replication/pgoutput/pgoutput.c
+++ b/src/backend/replication/pgoutput/pgoutput.c
@@ -1764,6 +1764,11 @@ pgoutput_shutdown(LogicalDecodingContext *ctx)
/*
* Load publications from the list of publication names.
+ *
+ * Here, we skip the publications that don't exist yet. This will allow us
+ * to silently continue the replication in the absence of a missing publication.
+ * This is required because we allow the users to create publications after they
+ * have specified the required publications at the time of replication start.
*/
static List *
LoadPublications(List *pubnames)
@@ -1774,9 +1779,16 @@ LoadPublications(List *pubnames)
foreach(lc, pubnames)
{
char *pubname = (char *) lfirst(lc);
- Publication *pub = GetPublicationByName(pubname, false);
+ Publication *pub = GetPublicationByName(pubname, true);
- result = lappend(result, pub);
+ if (pub)
+ result = lappend(result, pub);
+ else
+ ereport(WARNING,
+ errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("skipped loading publication: %s", pubname),
+ errdetail("The publication does not exist at this point in the WAL."),
+ errhint("Create the publication if it does not exist."));
}
return result;
diff --git a/src/test/subscription/t/024_add_drop_pub.pl b/src/test/subscription/t/024_add_drop_pub.pl
index 4428a3413db..b594941c7cb 100644
--- a/src/test/subscription/t/024_add_drop_pub.pl
+++ b/src/test/subscription/t/024_add_drop_pub.pl
@@ -1,7 +1,9 @@
# Copyright (c) 2021-2025, PostgreSQL Global Development Group
-# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION
+# This test checks behaviour of ALTER SUBSCRIPTION ... ADD/DROP PUBLICATION and
+# ensures that creating a publication associated with a subscription at a later
+# point of time does not break logical replication.
use strict;
use warnings FATAL => 'all';
use PostgreSQL::Test::Cluster;
@@ -80,6 +82,46 @@ $result = $node_subscriber->safe_psql('postgres',
"SELECT count(*), min(a), max(a) FROM tab_1");
is($result, qq(20|1|10), 'check initial data is copied to subscriber');
+# Ensure that setting a missing publication to the subscription does not
+# disrupt existing logical replication. Instead, it should log a warning
+# while allowing replication to continue. Additionally, verify that replication
+# resumes after the missing publication is created for the publication table.
+
+# Create table on publisher and subscriber
+$node_publisher->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
+
+# Set the subscription with a missing publication
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_3");
+
+my $offset = -s $node_publisher->logfile;
+
+$node_publisher->safe_psql('postgres',"INSERT INTO tab_3 values(1)");
+
+# Verify that a warning is logged.
+$node_publisher->wait_for_log(
+ qr/WARNING: ( [A-Z0-9]+:)? skipped loading publication: tap_pub_3/, $offset);
+
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_3 FOR TABLE tab_3");
+
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub REFRESH PUBLICATION");
+
+$node_subscriber->wait_for_subscription_sync($node_publisher, 'tap_sub');
+
+$node_publisher->safe_psql('postgres', "INSERT INTO tab_3 values(2)");
+
+$node_publisher->wait_for_catchup('tap_sub');
+
+# Verify that the insert operation gets replicated to subscriber after
+# publication is created.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT * FROM tab_3");
+is($result, qq(1
+2), 'check that the incremental data is replicated after the publication is created');
+
# shutdown
$node_subscriber->stop('fast');
$node_publisher->stop('fast');
--
2.28.0.windows.1
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-03-13 13:56 ` Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Dilip Kumar @ 2025-03-13 13:56 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Mar 13, 2025 at 3:20 PM Amit Kapila <[email protected]> wrote:
>
> On Thu, Mar 13, 2025 at 11:43 AM Dilip Kumar <[email protected]> wrote:
> >
> > Looks fine, shall we add the missing publication point as well
> > something like below
> >
> > /*
> > * In operations like 'ALTER SUBSCRIPTION ... ADD/SET PUBLICATION' and
> > * 'CREATE SUBSCRIPTION', if the specified publication does not exist or
> > * if the replication origin is not updated before the worker exits,
> > * the WAL start location may point to a position prior to the publication's
> > * WAL record. This can cause persistent restarts and errors
> > * in the apply worker.
> > *
>
> I think that is too much related to pub-sub model, and ideally,
> pgoutput should not care about it. I have written a comment
> considering somebody using pgoutput decoding module via APIs.
I agree, here we just need to talk about skipping the missing
publication, not different scenarios where that can happen. This
comments look much better.
> > > * Additionally, dropping a subscription's publication should not
> > > * disrupt logical replication.
> > *
> > > * This ensures that a missing publication is skipped and loaded
> > > * when its corresponding WAL record is encountered.
> > */
> >
> >
> >
> > > ereport(WARNING,
> > > errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
> > > errmsg("skipped loading publication: %s", pubname),
> > > errhint("If the publication is missing, create and refresh it.
> > > Otherwise, wait for the slot to reach the WAL record, then refresh"));
> > >
> > > > 2. + errhint("If the publication already exists, ignore it as it will
> > > > be loaded upon reaching the corresponding WAL record; otherwise,
> > > > create it."));
> > > >
> > > > Is this hint correct? This is a question rather than a comment: When
> > > > we reach a particular WAL where the publication was created, will the
> > > > publication automatically load, or does the user need to REFRESH the
> > > > publications?
> > >
> > > Users need to refresh the publication in case the relation is not
> > > already added to pg_subscription_rel and apply incremental changes.
> > > How about an error hint like:
> > > "If the publication is missing, create and refresh it. Otherwise, wait
> > > for the slot to reach the WAL record for the created publication, then
> > > refresh"
> >
>
> I have tried to split this information into errdetail and errhint in
> the attached. See and let me know what you think of the same.
Yes, the errhint also makes sense to me.
--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
@ 2025-03-14 08:58 ` Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-03-14 08:58 UTC (permalink / raw)
To: Dilip Kumar <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Thu, Mar 13, 2025 at 7:26 PM Dilip Kumar <[email protected]> wrote:
>
> On Thu, Mar 13, 2025 at 3:20 PM Amit Kapila <[email protected]> wrote:
> >
> >
> > I think that is too much related to pub-sub model, and ideally,
> > pgoutput should not care about it. I have written a comment
> > considering somebody using pgoutput decoding module via APIs.
>
Thanks, Dilip and Sawada-San, for the inputs, and Vignesh for the
patch. I have pushed the change.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-04-30 16:31 ` vignesh C <[email protected]>
2025-05-02 03:43 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
0 siblings, 3 replies; 41+ messages in thread
From: vignesh C @ 2025-04-30 16:31 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: Tom Lane <[email protected]>; Dilip Kumar <[email protected]>; PostgreSQL Hackers <[email protected]>
On Wed, 30 Apr 2025 at 17:41, Amit Kapila <[email protected]> wrote:
>
> On Wed, Apr 30, 2025 at 11:22 AM Tom Lane <[email protected]> wrote:
> >
> > Xuneng Zhou pointed out on Discord that the test case added by
> > 7c99dc587 has caused repeated failures in CI --- though oddly,
> > it's not failed in the buildfarm so far as I can find. The
> > failures look like
> >
> > timed out waiting for match: (?^:WARNING: ( [A-Z0-9]+:)? skipped loading publication: tap_pub_3) at /tmp/cirrus-ci-build/src/test/subscription/t/024_add_drop_pub.pl line 103.
> >
>
> I analyzed the relevant publisher-side CI Logs [1]:
> ...
> 2025-04-19 08:24:14.096 UTC [21961][client backend]
> [024_add_drop_pub.pl][7/4:0] LOG: statement: INSERT INTO tab_3
> values(1)
> 2025-04-19 08:24:14.098 UTC [21961][client backend]
> [024_add_drop_pub.pl][:0] LOG: disconnection: session time:
> 0:00:00.003 user=postgres database=postgres host=[local]
> 2025-04-19 08:24:14.108 UTC [21797][walsender] [tap_sub][30/0:0] LOG:
> released logical replication slot "tap_sub"
> 2025-04-19 08:24:14.108 UTC [21797][walsender] [tap_sub][:0] LOG:
> disconnection: session time: 0:00:00.329 user=postgres
> database=postgres host=[local]
> 2025-04-19 08:24:14.127 UTC [21979][not initialized] [[unknown]][:0]
> LOG: connection received: host=[local]
> 2025-04-19 08:24:14.128 UTC [21979][walsender] [[unknown]][23/5:0]
> LOG: connection authenticated: user="postgres" method=trust
> (/tmp/cirrus-ci-build/build/testrun/subscription/024_add_drop_pub/data/t_024_add_drop_pub_publisher_data/pgdata/pg_hba.conf:117)
> 2025-04-19 08:24:14.128 UTC [21979][walsender] [[unknown]][23/5:0]
> LOG: replication connection authorized: user=postgres
> application_name=tap_sub
> 2025-04-19 08:24:14.129 UTC [21979][walsender] [tap_sub][23/6:0] LOG:
> statement: SELECT pg_catalog.set_config('search_path', '', false);
> 2025-04-19 08:24:14.130 UTC [21979][walsender] [tap_sub][23/0:0] LOG:
> received replication command: IDENTIFY_SYSTEM
> 2025-04-19 08:24:14.130 UTC [21979][walsender] [tap_sub][23/0:0]
> STATEMENT: IDENTIFY_SYSTEM
> 2025-04-19 08:24:14.131 UTC [21979][walsender] [tap_sub][23/0:0] LOG:
> received replication command: START_REPLICATION SLOT "tap_sub" LOGICAL
> 0/0 (proto_version '4', streaming 'parallel', origin 'any',
> publication_names '"tap_pub_
> ...
>
> This shows that walsender restarts after the "INSERT INTO tab_3
> values(1)" is processed by the previous walsender ("released logical
> replication slot "tap_sub"" is after "INSERT INTO tab_3 values(1)").
> So, it is possible that the old apply worker has sent the confirmation
> of WAL received location after the Insert (due to keep_alive message
> handling). So, after the restart, the new walsender will start
> processing WAL after the INSERT and wait for the skipped message LOG
> timed out.
>
> Considering the above theory is correct, after "ALTER SUBSCRIPTION
> tap_sub SET PUBLICATION tap_pub_3", we should wait for the new
> walsender to restart. We are already doing the same for a similar case
> in 001_rep_changes.pl (See "# check that change of connection string
> and/or publication list causes restart of subscription workers. We
> check the state along with application_name to ensure that the
> walsender is (re)started.).
>
> Unfortunately, I will be away for the rest of the week. In the
> meantime, if you or someone else is able to reproduce and fix it, then
> good; otherwise, I'll take care of it after I return.
I agree with your analysis. I was able to reproduce the issue by
delaying the invalidation of the subscription until the walsender
finished decoding the INSERT operation following the ALTER
SUBSCRIPTION through a debugger and using the lsn from the pg_waldump
of the INSERT after the ALTER SUBSCRIPTION. In this scenario, the
confirmed_flush_lsn ends up pointing to a location after the INSERT.
When the invalidation is eventually received and the apply
worker/walsender is restarted, the restarted walsender begins decoding
from that LSN—after the INSERT—which means the "skipped loading
publication" warning is never triggered, causing the test to fail.
Attached is a patch that ensures the walsender process is properly
restarted after ALTER SUBSCRIPTION, preventing this race condition.
Regards,
Vignesh
Attachments:
[text/x-patch] 0001-Fix-race-condition-after-ALTER-SUBSCRIPTION-SET-PUBL.patch (2.0K, ../../CALDaNm3TH3J8fwj+NcdjdN8DZCdrnmm1kzBsHBW2nkN+h6up3A@mail.gmail.com/2-0001-Fix-race-condition-after-ALTER-SUBSCRIPTION-SET-PUBL.patch)
download | inline diff:
From 26c8efbf59a456f4ad8a87b504180449efe1cd69 Mon Sep 17 00:00:00 2001
From: Vignesh <[email protected]>
Date: Wed, 30 Apr 2025 21:38:33 +0530
Subject: [PATCH] Fix race condition after ALTER SUBSCRIPTION SET PUBLICATION
Previously, after executing ALTER SUBSCRIPTION tap_sub SET PUBLICATION, we
did not wait for the new walsender process to restart. As a result, an INSERT
executed immediately after the ALTER could be decoded and the confirmed flush
lsn is advanced. This could cause replication to resume from a point after the
INSERT. In such cases, we miss the expected warning about the missing
publication.
To fix this, we now ensure that the walsender has restarted before continuing
after ALTER SUBSCRIPTION.
---
src/test/subscription/t/024_add_drop_pub.pl | 11 +++++++++++
1 file changed, 11 insertions(+)
diff --git a/src/test/subscription/t/024_add_drop_pub.pl b/src/test/subscription/t/024_add_drop_pub.pl
index b594941c7cb..e995d8b3839 100644
--- a/src/test/subscription/t/024_add_drop_pub.pl
+++ b/src/test/subscription/t/024_add_drop_pub.pl
@@ -91,10 +91,21 @@ is($result, qq(20|1|10), 'check initial data is copied to subscriber');
$node_publisher->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
$node_subscriber->safe_psql('postgres', "CREATE TABLE tab_3 (a int)");
+my $oldpid = $node_publisher->safe_psql('postgres',
+ "SELECT pid FROM pg_stat_replication WHERE application_name = 'tap_sub' AND state = 'streaming';"
+);
+
# Set the subscription with a missing publication
$node_subscriber->safe_psql('postgres',
"ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_3");
+# Wait for the walsender to restart after altering the subscription
+$node_publisher->poll_query_until('postgres',
+ "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = 'tap_sub' AND state = 'streaming';"
+ )
+ or die
+ "Timed out while waiting for apply worker to restart after altering the subscription";
+
my $offset = -s $node_publisher->logfile;
$node_publisher->safe_psql('postgres',"INSERT INTO tab_3 values(1)");
--
2.43.0
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-02 03:43 ` Xuneng Zhou <[email protected]>
2 siblings, 0 replies; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-02 03:43 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: [email protected]; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]
+1, I was unable to reproduce this with lldb, not sure my way is
appropriate or not.
>
> Can you be a little more specific about how you reproduced this?
> I tried inserting sleep() calls in various likely-looking spots
> and could not get a failure that way.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-02 03:53 ` vignesh C <[email protected]>
2025-05-04 13:14 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2 siblings, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-05-02 03:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, 2 May 2025 at 06:30, Tom Lane <[email protected]> wrote:
>
> vignesh C <[email protected]> writes:
> > I agree with your analysis. I was able to reproduce the issue by
> > delaying the invalidation of the subscription until the walsender
> > finished decoding the INSERT operation following the ALTER
> > SUBSCRIPTION through a debugger and using the lsn from the pg_waldump
> > of the INSERT after the ALTER SUBSCRIPTION.
>
> Can you be a little more specific about how you reproduced this?
> I tried inserting sleep() calls in various likely-looking spots
> and could not get a failure that way.
Test Steps:
1) Set up logical replication:
Create a publication on the publisher
Create a subscription on the subscriber
2) Create the following table on the publisher:
CREATE TABLE tab_3 (a int);
3) Create the same table on the subscriber:
CREATE TABLE tab_3 (a int);
4) On the subscriber, alter the subscription to refer to a
non-existent publication:
ALTER SUBSCRIPTION sub1 SET PUBLICATION tap_pub_3;
5) Insert data on the publisher:
INSERT INTO tab_3 VALUES (1);
As expected, the publisher logs the following warning in normal case:
2025-05-02 08:56:45.350 IST [516197] WARNING: skipped loading
publication: tap_pub_3
2025-05-02 08:56:45.350 IST [516197] DETAIL: The publication does
not exist at this point in the WAL.
2025-05-02 08:56:45.350 IST [516197] HINT: Create the publication
if it does not exist.
To simulate a delay in subscription invalidation, I modified the
maybe_reread_subscription() function as follows:
diff --git a/src/backend/replication/logical/worker.c
b/src/backend/replication/logical/worker.c
index 4151a4b2a96..0831784aca3 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -3970,6 +3970,10 @@ maybe_reread_subscription(void)
MemoryContext oldctx;
Subscription *newsub;
bool started_tx = false;
+ bool test = true;
+
+ if (test)
+ return;
This change delays the subscription invalidation logic, preventing the
apply worker from detecting the subscription change immediately.
With the patch applied, repeat steps 1–5.
Using pg_waldump, identify the LSN of the insert:
rmgr: Heap len (rec/tot): 59/ 59, tx: 756, lsn:
0/01711848, prev 0/01711810, desc: INSERT+INIT off: 1
rmgr: Transaction len (rec/tot): 46/ 46, tx: 756, lsn:
0/01711888, prev 0/01711848, desc: COMMIT 2025-05-02 09:06:09.400926
IST
Check the confirmed flush LSN from the walsender via gdb by attaching
it to the walsender process
(gdb) p *MyReplicationSlot
...
confirmed_flush = 24241928
(gdb) p /x 24241928
$4 = 0x171e708
Now attach to the apply worker, set a breakpoint at
maybe_reread_subscription, and continue execution. Once control
reaches the function, set test = false. Now it will identify that
subscription is invalidated and restart the apply worker.
As the walsender has already confirmed_flush position after the
insert, causing the newly started apply worker to miss the inserted
row entirely. This leads to the CI failure. This issue can arise when
the walsender advances more quickly than the apply worker is able to
detect and react to the subscription change.
I could not find a simpler way to reproduce this.
Regards,
Vignesh
Attachments:
[text/x-patch] skip_subscription_invalidation.patch (498B, ../../CALDaNm2Q_pfwiCkaV920iXEbh4D=5MmD_tNQm_GRGX6-MsLxoQ@mail.gmail.com/2-skip_subscription_invalidation.patch)
download | inline diff:
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 4151a4b2a96..0831784aca3 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -3970,6 +3970,10 @@ maybe_reread_subscription(void)
MemoryContext oldctx;
Subscription *newsub;
bool started_tx = false;
+ bool test = true;
+
+ if (test)
+ return;
/* When cache state is valid there is nothing to do here. */
if (MySubscriptionValid)
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-04 13:14 ` vignesh C <[email protected]>
2025-05-11 15:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: vignesh C @ 2025-05-04 13:14 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; PostgreSQL Hackers <[email protected]>
On Fri, 2 May 2025 at 09:23, vignesh C <[email protected]> wrote:
>
> On Fri, 2 May 2025 at 06:30, Tom Lane <[email protected]> wrote:
> >
> > vignesh C <[email protected]> writes:
> > > I agree with your analysis. I was able to reproduce the issue by
> > > delaying the invalidation of the subscription until the walsender
> > > finished decoding the INSERT operation following the ALTER
> > > SUBSCRIPTION through a debugger and using the lsn from the pg_waldump
> > > of the INSERT after the ALTER SUBSCRIPTION.
> >
> > Can you be a little more specific about how you reproduced this?
> > I tried inserting sleep() calls in various likely-looking spots
> > and could not get a failure that way.
>
> Test Steps:
> 1) Set up logical replication:
> Create a publication on the publisher
> Create a subscription on the subscriber
> 2) Create the following table on the publisher:
> CREATE TABLE tab_3 (a int);
> 3) Create the same table on the subscriber:
> CREATE TABLE tab_3 (a int);
> 4) On the subscriber, alter the subscription to refer to a
> non-existent publication:
> ALTER SUBSCRIPTION sub1 SET PUBLICATION tap_pub_3;
> 5) Insert data on the publisher:
> INSERT INTO tab_3 VALUES (1);
>
> As expected, the publisher logs the following warning in normal case:
> 2025-05-02 08:56:45.350 IST [516197] WARNING: skipped loading
> publication: tap_pub_3
> 2025-05-02 08:56:45.350 IST [516197] DETAIL: The publication does
> not exist at this point in the WAL.
> 2025-05-02 08:56:45.350 IST [516197] HINT: Create the publication
> if it does not exist.
>
> To simulate a delay in subscription invalidation, I modified the
> maybe_reread_subscription() function as follows:
> diff --git a/src/backend/replication/logical/worker.c
> b/src/backend/replication/logical/worker.c
> index 4151a4b2a96..0831784aca3 100644
> --- a/src/backend/replication/logical/worker.c
> +++ b/src/backend/replication/logical/worker.c
> @@ -3970,6 +3970,10 @@ maybe_reread_subscription(void)
> MemoryContext oldctx;
> Subscription *newsub;
> bool started_tx = false;
> + bool test = true;
> +
> + if (test)
> + return;
>
> This change delays the subscription invalidation logic, preventing the
> apply worker from detecting the subscription change immediately.
>
> With the patch applied, repeat steps 1–5.
> Using pg_waldump, identify the LSN of the insert:
> rmgr: Heap len (rec/tot): 59/ 59, tx: 756, lsn:
> 0/01711848, prev 0/01711810, desc: INSERT+INIT off: 1
> rmgr: Transaction len (rec/tot): 46/ 46, tx: 756, lsn:
> 0/01711888, prev 0/01711848, desc: COMMIT 2025-05-02 09:06:09.400926
> IST
>
> Check the confirmed flush LSN from the walsender via gdb by attaching
> it to the walsender process
> (gdb) p *MyReplicationSlot
> ...
> confirmed_flush = 24241928
> (gdb) p /x 24241928
> $4 = 0x171e708
>
> Now attach to the apply worker, set a breakpoint at
> maybe_reread_subscription, and continue execution. Once control
> reaches the function, set test = false. Now it will identify that
> subscription is invalidated and restart the apply worker.
>
> As the walsender has already confirmed_flush position after the
> insert, causing the newly started apply worker to miss the inserted
> row entirely. This leads to the CI failure. This issue can arise when
> the walsender advances more quickly than the apply worker is able to
> detect and react to the subscription change.
>
> I could not find a simpler way to reproduce this.
A simpler way to consistently reproduce the issue is to add a 1-second
sleep in the LogicalRepApplyLoop function, just before the call to
WaitLatchOrSocket. This reproduces the test failure consistently for
me. The failure reason is the same as in [1].
[1] - https://www.postgresql.org/message-id/CALDaNm2Q_pfwiCkaV920iXEbh4D%3D5MmD_tNQm_GRGX6-MsLxoQ%40mail.g...
Regards,
Vignesh
Attachments:
[text/x-patch] ci_failure_reproduce.patch (2.2K, ../../CALDaNm27gUnMG5-gdBLnWH_+4G+EZ_78MA2h8fbGPm9o5LjySA@mail.gmail.com/2-ci_failure_reproduce.patch)
download | inline diff:
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 4151a4b2a96..d0056f5655c 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -3702,6 +3702,7 @@ LogicalRepApplyLoop(XLogRecPtr last_received)
if (last_received < end_lsn)
last_received = end_lsn;
+ elog(LOG, "Send feedback from 1");
send_feedback(last_received, reply_requested, false);
UpdateWorkerStats(last_received, timestamp, true);
}
@@ -3714,6 +3715,7 @@ LogicalRepApplyLoop(XLogRecPtr last_received)
}
}
+ elog(LOG, "Send feedback from 2");
/* confirm all writes so far */
send_feedback(last_received, false, false);
@@ -3739,6 +3741,8 @@ LogicalRepApplyLoop(XLogRecPtr last_received)
if (endofstream)
break;
+ sleep(1);
+
/*
* Wait for more data or latch. If we have unflushed transactions,
* wake up after WalWriterDelay to see if they've been flushed yet (in
@@ -3812,6 +3816,7 @@ LogicalRepApplyLoop(XLogRecPtr last_received)
}
}
+ elog(LOG, "Send feedback from 3");
send_feedback(last_received, requestReply, requestReply);
/*
@@ -3910,7 +3915,7 @@ send_feedback(XLogRecPtr recvpos, bool force, bool requestReply)
pq_sendint64(reply_message, now); /* sendTime */
pq_sendbyte(reply_message, requestReply); /* replyRequested */
- elog(DEBUG2, "sending feedback (force %d) to recv %X/%X, write %X/%X, flush %X/%X",
+ elog(LOG, "sending feedback (force %d) to recv %X/%X, write %X/%X, flush %X/%X",
force,
LSN_FORMAT_ARGS(recvpos),
LSN_FORMAT_ARGS(writepos),
diff --git a/src/backend/replication/walsender.c b/src/backend/replication/walsender.c
index 9fa8beb6103..9896a8d74d5 100644
--- a/src/backend/replication/walsender.c
+++ b/src/backend/replication/walsender.c
@@ -4062,7 +4062,8 @@ pg_stat_get_wal_senders(PG_FUNCTION_ARGS)
static void
WalSndKeepalive(bool requestReply, XLogRecPtr writePtr)
{
- elog(DEBUG2, "sending replication keepalive");
+ elog(LOG, "sending replication keepalive - writePtr %X/%X",
+ LSN_FORMAT_ARGS(writePtr));
/* construct the message... */
resetStringInfo(&output_message);
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-04 13:14 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-11 15:19 ` Xuneng Zhou <[email protected]>
2025-05-12 05:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-11 15:19 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
Hi, I was able to reproduce the failure by adding a 1-second sleep in the
LogicalRepApplyLoop function
However, I noticed that the tests under src/test/subscription run
significantly slower— is this normal?
Also, it looks like the patch mentioned in this thread addresses the issue:
https://www.postgresql.org/message-id/CALDaNm2Q_pfwiCkaV920iXEbh4D%3D5MmD_tNQm_GRGX6-MsLxoQ%40mail.g...
>
> A simpler way to consistently reproduce the issue is to add a 1-second
> sleep in the LogicalRepApplyLoop function, just before the call to
> WaitLatchOrSocket. This reproduces the test failure consistently for
> me. The failure reason is the same as in [1].
>
> [1] -
> https://www.postgresql.org/message-id/CALDaNm2Q_pfwiCkaV920iXEbh4D%3D5MmD_tNQm_GRGX6-MsLxoQ%40mail.g...
>
> Regards,
> Vignesh
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-04 13:14 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-11 15:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
@ 2025-05-12 05:41 ` Amit Kapila <[email protected]>
2025-05-12 09:01 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-05-12 05:41 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
On Sun, May 11, 2025 at 8:49 PM Xuneng Zhou <[email protected]> wrote:
>
> Hi, I was able to reproduce the failure by adding a 1-second sleep in the LogicalRepApplyLoop function
> However, I noticed that the tests under src/test/subscription run significantly slower— is this normal?
>
Yes, because you made apply slower by adding a sleep.
> Also, it looks like the patch mentioned in this thread addresses the issue:
> https://www.postgresql.org/message-id/CALDaNm2Q_pfwiCkaV920iXEbh4D%3D5MmD_tNQm_GRGX6-MsLxoQ%40mail.g...
>>
So you are okay with a test-only fix?
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-04 13:14 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-11 15:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-12 05:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-05-12 09:01 ` Xuneng Zhou <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-12 09:01 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
If the presumed theory regarding the cause of the issue is correct — as
outlined in this email
<https://www.postgresql.org/message-id/CALDaNm2Pmbc-7KM3nRgZcq1EBhbdvWJSTie-st57oGuKP4O44w%40mail.gma...;
— and no data replication occurs in this scenario
<https://www.postgresql.org/message-id/CAA4eK1Jz20hnPRDtPDo2BbSt0Xf8u2zY4Tc84R0OAQN8M%3D9iCQ%40mail.g...;
, then the proposed fix seems ok to me.
But I don’t have the expertise to fully assess the trade-offs between
enforcing strict ordering across nodes and maintaining the current behavior.
>
> > Also, it looks like the patch mentioned in this thread addresses the
> issue:
> >
> https://www.postgresql.org/message-id/CALDaNm2Q_pfwiCkaV920iXEbh4D%3D5MmD_tNQm_GRGX6-MsLxoQ%40mail.g...
> >>
>
> So you are okay with a test-only fix?
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-02 04:41 ` Xuneng Zhou <[email protected]>
2025-05-02 04:46 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2 siblings, 2 replies; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-02 04:41 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]; [email protected]
Hi,
Is this an expected behavior?
A race between subscriber LSN feedback and publisher subscription change
processing allows the walsender to restart decoding past relevant WAL
records, bypassing the updated subscription rules for those records.
On Wed, 30 Apr 2025 at 17:41, Amit Kapila <[email protected]> wrote:
> >
> > On Wed, Apr 30, 2025 at 11:22 AM Tom Lane <[email protected]> wrote:
> > >
> > > Xuneng Zhou pointed out on Discord that the test case added by
> > > 7c99dc587 has caused repeated failures in CI --- though oddly,
> > > it's not failed in the buildfarm so far as I can find. The
> > > failures look like
> > >
> > > timed out waiting for match: (?^:WARNING: ( [A-Z0-9]+:)? skipped
> loading publication: tap_pub_3) at
> /tmp/cirrus-ci-build/src/test/subscription/t/024_add_drop_pub.pl line 103.
> > >
> >
> > I analyzed the relevant publisher-side CI Logs [1]:
> > ...
> > 2025-04-19 08:24:14.096 UTC [21961][client backend]
> > [024_add_drop_pub.pl][7/4:0] LOG: statement: INSERT INTO tab_3
> > values(1)
> > 2025-04-19 08:24:14.098 UTC [21961][client backend]
> > [024_add_drop_pub.pl][:0] LOG: disconnection: session time:
> > 0:00:00.003 user=postgres database=postgres host=[local]
> > 2025-04-19 08:24:14.108 UTC [21797][walsender] [tap_sub][30/0:0] LOG:
> > released logical replication slot "tap_sub"
> > 2025-04-19 08:24:14.108 UTC [21797][walsender] [tap_sub][:0] LOG:
> > disconnection: session time: 0:00:00.329 user=postgres
> > database=postgres host=[local]
> > 2025-04-19 08:24:14.127 UTC [21979][not initialized] [[unknown]][:0]
> > LOG: connection received: host=[local]
> > 2025-04-19 08:24:14.128 UTC [21979][walsender] [[unknown]][23/5:0]
> > LOG: connection authenticated: user="postgres" method=trust
> >
> (/tmp/cirrus-ci-build/build/testrun/subscription/024_add_drop_pub/data/t_024_add_drop_pub_publisher_data/pgdata/pg_hba.conf:117)
> > 2025-04-19 08:24:14.128 UTC [21979][walsender] [[unknown]][23/5:0]
> > LOG: replication connection authorized: user=postgres
> > application_name=tap_sub
> > 2025-04-19 08:24:14.129 UTC [21979][walsender] [tap_sub][23/6:0] LOG:
> > statement: SELECT pg_catalog.set_config('search_path', '', false);
> > 2025-04-19 08:24:14.130 UTC [21979][walsender] [tap_sub][23/0:0] LOG:
> > received replication command: IDENTIFY_SYSTEM
> > 2025-04-19 08:24:14.130 UTC [21979][walsender] [tap_sub][23/0:0]
> > STATEMENT: IDENTIFY_SYSTEM
> > 2025-04-19 08:24:14.131 UTC [21979][walsender] [tap_sub][23/0:0] LOG:
> > received replication command: START_REPLICATION SLOT "tap_sub" LOGICAL
> > 0/0 (proto_version '4', streaming 'parallel', origin 'any',
> > publication_names '"tap_pub_
> > ...
> >
> > This shows that walsender restarts after the "INSERT INTO tab_3
> > values(1)" is processed by the previous walsender ("released logical
> > replication slot "tap_sub"" is after "INSERT INTO tab_3 values(1)").
> > So, it is possible that the old apply worker has sent the confirmation
> > of WAL received location after the Insert (due to keep_alive message
> > handling). So, after the restart, the new walsender will start
> > processing WAL after the INSERT and wait for the skipped message LOG
> > timed out.
> >
> > Considering the above theory is correct, after "ALTER SUBSCRIPTION
> > tap_sub SET PUBLICATION tap_pub_3", we should wait for the new
> > walsender to restart. We are already doing the same for a similar case
> > in 001_rep_changes.pl (See "# check that change of connection string
> > and/or publication list causes restart of subscription workers. We
> > check the state along with application_name to ensure that the
> > walsender is (re)started.).
> >
> > Unfortunately, I will be away for the rest of the week. In the
> > meantime, if you or someone else is able to reproduce and fix it, then
> > good; otherwise, I'll take care of it after I return.
>
> I agree with your analysis. I was able to reproduce the issue by
> delaying the invalidation of the subscription until the walsender
> finished decoding the INSERT operation following the ALTER
> SUBSCRIPTION through a debugger and using the lsn from the pg_waldump
> of the INSERT after the ALTER SUBSCRIPTION. In this scenario, the
> confirmed_flush_lsn ends up pointing to a location after the INSERT.
> When the invalidation is eventually received and the apply
> worker/walsender is restarted, the restarted walsender begins decoding
> from that LSN—after the INSERT—which means the "skipped loading
> publication" warning is never triggered, causing the test to fail.
>
> Attached is a patch that ensures the walsender process is properly
> restarted after ALTER SUBSCRIPTION, preventing this race condition.
>
> Regards,
> Vignesh
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
@ 2025-05-02 04:46 ` Tom Lane <[email protected]>
1 sibling, 0 replies; 41+ messages in thread
From: Tom Lane @ 2025-05-02 04:46 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: vignesh C <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]
Xuneng Zhou <[email protected]> writes:
> Is this an expected behavior?
I'm wondering that too. I don't see how the repro method Vignesh
describes could correspond to a simple timing issue. It smells
like there's a bug here somewhere.
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
@ 2025-05-02 05:22 ` vignesh C <[email protected]>
2025-05-02 10:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
1 sibling, 2 replies; 41+ messages in thread
From: vignesh C @ 2025-05-02 05:22 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]; [email protected]
On Fri, 2 May 2025 at 10:11, Xuneng Zhou <[email protected]> wrote:
>
> Hi,
> Is this an expected behavior?
>
> A race between subscriber LSN feedback and publisher subscription change processing allows the walsender to restart decoding past relevant WAL records, bypassing the updated subscription rules for those records.
We have three processes involved in this scenario:
A walsender process on the publisher, responsible for decoding and
sending WAL changes.
An apply worker process on the subscriber, which applies the changes.
A session executing the ALTER SUBSCRIPTION command.
Due to the asynchronous nature of these processes, the ALTER
SUBSCRIPTION command may not be immediately observed by the apply
worker. Meanwhile, the walsender may process and decode an INSERT
statement.
If the insert targets a table (e.g., tab_3) that does not belong to
the current publication (pub1), the walsender silently skips
replicating the record and advances its decoding position. This
position is sent in a keepalive message to the subscriber, and since
there are no pending transactions to flush, the apply worker reports
it as the latest received LSN.
Later, when the apply worker eventually detects the subscription
change, it restarts—but by then, the insert has already been skipped
and is no longer eligible for replay, as the table was not part of the
publication (pub1) at the time of decoding.
This race condition arises because the three processes run
independently and may progress at different speeds due to CPU
scheduling or system load.
Thoughts?
Regards,
Vignesh
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-02 10:44 ` Xuneng Zhou <[email protected]>
1 sibling, 0 replies; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-02 10:44 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]; [email protected]
Yeh, tks for your clarification. I have a basic understanding of it now. I
mean is this considered a bug or design defect in the codebase? If so,
should we prevent it from occuring in general, not just for this specific
test.
vignesh C <[email protected]>
>
> We have three processes involved in this scenario:
> A walsender process on the publisher, responsible for decoding and
> sending WAL changes.
> An apply worker process on the subscriber, which applies the changes.
> A session executing the ALTER SUBSCRIPTION command.
>
> Due to the asynchronous nature of these processes, the ALTER
> SUBSCRIPTION command may not be immediately observed by the apply
> worker. Meanwhile, the walsender may process and decode an INSERT
> statement.
> If the insert targets a table (e.g., tab_3) that does not belong to
> the current publication (pub1), the walsender silently skips
> replicating the record and advances its decoding position. This
> position is sent in a keepalive message to the subscriber, and since
> there are no pending transactions to flush, the apply worker reports
> it as the latest received LSN.
> Later, when the apply worker eventually detects the subscription
> change, it restarts—but by then, the insert has already been skipped
> and is no longer eligible for replay, as the table was not part of the
> publication (pub1) at the time of decoding.
> This race condition arises because the three processes run
> independently and may progress at different speeds due to CPU
> scheduling or system load.
> Thoughts?
>
> Regards,
> Vignesh
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-05-02 16:54 ` Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
1 sibling, 1 reply; 41+ messages in thread
From: Tom Lane @ 2025-05-02 16:54 UTC (permalink / raw)
To: vignesh C <[email protected]>; +Cc: Xuneng Zhou <[email protected]>; Amit Kapila <[email protected]>; Dilip Kumar <[email protected]>; [email protected]
vignesh C <[email protected]> writes:
> Due to the asynchronous nature of these processes, the ALTER
> SUBSCRIPTION command may not be immediately observed by the apply
> worker. Meanwhile, the walsender may process and decode an INSERT
> statement.
> If the insert targets a table (e.g., tab_3) that does not belong to
> the current publication (pub1), the walsender silently skips
> replicating the record and advances its decoding position. This
> position is sent in a keepalive message to the subscriber, and since
> there are no pending transactions to flush, the apply worker reports
> it as the latest received LSN.
So this theory presumes that the apply worker receives and reacts to
the keepalive message, yet it has not observed a relevant
subscriber-side catalog update that surely committed before the
keepalive was generated. It's fairly hard to see how that is okay,
because it's at least adjacent to something that must be considered a
bug: applying transmitted data without having observed DDL updates to
the target table. Why is the processing of keepalives laxer than the
processing of data messages?
regards, tom lane
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
@ 2025-05-05 06:18 ` Amit Kapila <[email protected]>
2025-05-06 10:03 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-05-05 06:18 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: vignesh C <[email protected]>; Xuneng Zhou <[email protected]>; Dilip Kumar <[email protected]>; [email protected]
On Fri, May 2, 2025 at 10:24 PM Tom Lane <[email protected]> wrote:
>
> vignesh C <[email protected]> writes:
> > Due to the asynchronous nature of these processes, the ALTER
> > SUBSCRIPTION command may not be immediately observed by the apply
> > worker. Meanwhile, the walsender may process and decode an INSERT
> > statement.
> > If the insert targets a table (e.g., tab_3) that does not belong to
> > the current publication (pub1), the walsender silently skips
> > replicating the record and advances its decoding position. This
> > position is sent in a keepalive message to the subscriber, and since
> > there are no pending transactions to flush, the apply worker reports
> > it as the latest received LSN.
>
> So this theory presumes that the apply worker receives and reacts to
> the keepalive message, yet it has not observed a relevant
> subscriber-side catalog update that surely committed before the
> keepalive was generated. It's fairly hard to see how that is okay,
> because it's at least adjacent to something that must be considered a
> bug: applying transmitted data without having observed DDL updates to
> the target table. Why is the processing of keepalives laxer than the
> processing of data messages?
>
Valid question, as of now, we don't have a specific rule about
ordering the processing of keepalives or invalidation messages. The
effect of invalidation messages is realized by calling
maybe_reread_subscription at three different times after accepting
invalidation message, (a) after starting a transaction in
begin_replication_step, (b) in the commit message handling if there is
no data modification happened in that transaction, and (c) when we
don't get any transactions for a while
The (a) ensures we consume any target table change before applying a
new transaction. The other two places ensure that we keep consuming
invalidation messages from time to time.
Now, we can consume invalidation messages during keepalive message
handling and or at some other places, to ensure that we never process
any remote message before consuming an invalidation message. However,
it is not clear to if this is a must kind of thing. We can provide
strict guarantees for ordering of messages from any one of the
servers, but providing it across nodes doesn't sound to be a
must-criterion.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-05-06 10:03 ` Xuneng Zhou <[email protected]>
2025-05-06 11:47 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Xuneng Zhou @ 2025-05-06 10:03 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
Hi,
A clear benefit of addressing this in code is to ensure that the user sees
the log message, which can be valuable for trouble-shooting—even under race
conditions.
ereport(WARNING,
errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
errmsg("skipped loading
publication: %s", pubname),
errdetail("The publication does not
exist at this point in the WAL."),
errhint("Create the publication if
it does not exist."));
The performance impact appears low, assuming the
AcceptInvalidationMessages and maybe_reread_subscription check are
introduced only in the code path that handles keepalive messages requiring
a reply.
>
> > vignesh C <[email protected]> writes:
> > > Due to the asynchronous nature of these processes, the ALTER
> > > SUBSCRIPTION command may not be immediately observed by the apply
> > > worker. Meanwhile, the walsender may process and decode an INSERT
> > > statement.
> > > If the insert targets a table (e.g., tab_3) that does not belong to
> > > the current publication (pub1), the walsender silently skips
> > > replicating the record and advances its decoding position. This
> > > position is sent in a keepalive message to the subscriber, and since
> > > there are no pending transactions to flush, the apply worker reports
> > > it as the latest received LSN.
> >
> > So this theory presumes that the apply worker receives and reacts to
> > the keepalive message, yet it has not observed a relevant
> > subscriber-side catalog update that surely committed before the
> > keepalive was generated. It's fairly hard to see how that is okay,
> > because it's at least adjacent to something that must be considered a
> > bug: applying transmitted data without having observed DDL updates to
> > the target table. Why is the processing of keepalives laxer than the
> > processing of data messages?
> >
>
> Valid question, as of now, we don't have a specific rule about
> ordering the processing of keepalives or invalidation messages. The
> effect of invalidation messages is realized by calling
> maybe_reread_subscription at three different times after accepting
> invalidation message, (a) after starting a transaction in
> begin_replication_step, (b) in the commit message handling if there is
> no data modification happened in that transaction, and (c) when we
> don't get any transactions for a while
>
> The (a) ensures we consume any target table change before applying a
> new transaction. The other two places ensure that we keep consuming
> invalidation messages from time to time.
>
> Now, we can consume invalidation messages during keepalive message
> handling and or at some other places, to ensure that we never process
> any remote message before consuming an invalidation message. However,
> it is not clear to if this is a must kind of thing. We can provide
> strict guarantees for ordering of messages from any one of the
> servers, but providing it across nodes doesn't sound to be a
> must-criterion.
>
> --
> With Regards,
> Amit Kapila.
>
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-06 10:03 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
@ 2025-05-06 11:47 ` Amit Kapila <[email protected]>
2025-05-10 11:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-05-06 11:47 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
On Tue, May 6, 2025 at 3:33 PM Xuneng Zhou <[email protected]> wrote:
>
> A clear benefit of addressing this in code is to ensure that the user sees the log message, which can be valuable for trouble-shooting—even under race conditions.
>
I don't think we can take that guarantee because if the Insert is
concurrent or slightly before the Alter Subscription command, then
there won't be a guarantee that users will see the skipped LOG
message.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-06 10:03 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-06 11:47 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-05-10 11:45 ` Amit Kapila <[email protected]>
2025-05-13 11:33 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
0 siblings, 1 reply; 41+ messages in thread
From: Amit Kapila @ 2025-05-10 11:45 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
On Tue, May 6, 2025 at 5:17 PM Amit Kapila <[email protected]> wrote:
>
> On Tue, May 6, 2025 at 3:33 PM Xuneng Zhou <[email protected]> wrote:
> >
> > A clear benefit of addressing this in code is to ensure that the user sees the log message, which can be valuable for trouble-shooting—even under race conditions.
> >
>
> I don't think we can take that guarantee because if the Insert is
> concurrent or slightly before the Alter Subscription command, then
> there won't be a guarantee that users will see the skipped LOG
> message.
>
I am planning to proceed with the test-fix proposed by Vignesh [1]
early next week unless we want to discuss more on this issue.
[1] - https://www.postgresql.org/message-id/CALDaNm3TH3J8fwj%2BNcdjdN8DZCdrnmm1kzBsHBW2nkN%2Bh6up3A%40mail...
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-06 10:03 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-06 11:47 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-10 11:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
@ 2025-05-13 11:33 ` Amit Kapila <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Amit Kapila @ 2025-05-13 11:33 UTC (permalink / raw)
To: Xuneng Zhou <[email protected]>; +Cc: vignesh C <[email protected]>; [email protected]; Dilip Kumar <[email protected]>; [email protected]
On Sat, May 10, 2025 at 5:15 PM Amit Kapila <[email protected]> wrote:
>
> I am planning to proceed with the test-fix proposed by Vignesh [1]
> early next week unless we want to discuss more on this issue.
>
Pushed.
> [1] - https://www.postgresql.org/message-id/CALDaNm3TH3J8fwj%2BNcdjdN8DZCdrnmm1kzBsHBW2nkN%2Bh6up3A%40mail...
>
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
@ 2025-03-09 03:29 ` Masahiko Sawada <[email protected]>
2025-03-10 03:33 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
1 sibling, 1 reply; 41+ messages in thread
From: Masahiko Sawada @ 2025-03-09 03:29 UTC (permalink / raw)
To: Amit Kapila <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Tue, Mar 4, 2025 at 9:04 PM Amit Kapila <[email protected]> wrote:
>
> On Tue, Mar 4, 2025 at 12:23 PM vignesh C <[email protected]> wrote:
> >
> > There is almost negligible dip with the above suggested way, the test
> > results for the same is given below(execution time is in milli
> > seconds):
> > Brach/records | 100 | 1000 | 10000 | 100000 | 1000000
> > Head | 10.25 | 15.85 | 65.53 | 569.15 | 9194.19
> > Patch | 10.25 | 15.84 | 65.91 | 571.75 | 9208.66
> > % diff | 0.00 | 0.06 | -0.58 | -0.46 | -0.16
> >
> > There is a performance dip in the range of 0 to 0.58 percent.
> > The attached patch has the changes for the same. The test script used
> > is also attached.
> >
>
> The patch still needs more review but the change has negligible
> performance impact. The next step is to get more opinions on whether
> we should add a new subscription option (say
> skip_not_existant_publication) for this work. See patch v1-0002-* in
> email [1]. The problem summary is explained in email [2] and in the
> commit message of the 0001 patch in this thread. But still, let me
> write briefly for the ease of others.
>
> The problem is that ALTER SUBSCRIPTION ... SET PUBLICATION ... will
> lead to restarting of apply worker, and after the restart, the apply
> worker will use the existing slot and replication origin corresponding
> to the subscription. Now, it is possible that before the restart, the
> origin has not been updated, and the WAL start location points to a
> location before where PUBLICATION pointed to by SET PUBLICATION
> exists. This leads to an error: "ERROR: publication "pub1" does not
> exist". Once this error occurs, apply worker will never be able to
> proceed and will always return the same error. For users, this is a
> problem because they would have created a publication before executing
> ALTER SUBSCRIPTION ... SET PUBLICATION .. and now they have no way to
> proceed.
>
> The solution we came up with is to skip loading the publication if the
> publication does not exist. We load the publication later and update
> the relation entry when the publication gets created.
>
> The two main concerns with this idea, as shared in email [3], are
> performance implications of this change and the possibility of current
> behaviour expectations from the users.
>
> We came up with a solution where the performance impact is negligible,
> as shown in the tests [4]. For that, we won't try to reload the
> skipped/missing publication for each change but will attempt it only
> when any new publication is created/dropped for a valid relation entry
> in RelationSyncCache (maintained by pgoutput).
Thank you for summarizing the issue. That helps catch up a lot.
>
> The new option skip_not_existant_publication is to address the second
> concern "Imagine you have a subscriber using two publications p1 and
> p2, and someone comes around and drops p1 by mistake. With the
> proposed patch, the subscription will notice this, but it'll continue
> sending data ignoring the missing publication. Yes, it will continue
> working, but it's quite possible this breaks the subscriber and it's
> be better to fail and stop replicating.".
I think that in this particular situation the current behavior would
be likely to miss more changes than the patch'ed behavior case.
After the logical replication stops, the user would have to alter the
subscription to subscribe to only p1 by executing 'ALTER SUBSCRIPTION
... SET PUBLICATION p1' in order to resume the logical replication. In
any case, the publisher might be receiving further changes but the
subscriber would end up missing changes for tables associated with p2
generated while p2 doesn't exist. Even if the user re-creates the
publication p2 after that, it would be hard for users to re-alter the
subscription to get changes for tables associated with p1 and p2 from
the exact point of p2 being created. Therefore, the subscriber could
end up missing some changes that happened between 'CREATE PUBLICATION
p2' and 'ALTER SUBSCRIPTION ... SET PUBLICATION p1, p2'.
On the other hand, with the patch, the publication can send the
changes to tables associated with p1 and p2 as soon as it decodes the
WAL record of re-CREATE PUBLICATION p2.
>
> I see the point of adding such an option to avoid breaking the current
> applications (if there are any) that are relying on current behaviour.
> But OTOH, I am not sure if users expect us to fail explicitly in such
> scenarios.
On the side note, with the patch since we ignore missing publications
we will be able to create a subscription with whatever publications
regardless their existence like:
CREATE SUBSCRIPTION ... PUBLICATION pub1, pub2, pub3, pub4, pub5, ..., pub1000;
The walsender corresponding to such subscriber can stream changes as
soon as the listed publications are created on the publisher and
REFRESH PUBLICATION is executed.
>
> This is a long-standing behaviour for which we get reports from time
> to time, and once analyzing a failure, Tom also looked at it and
> agreed that we don't have much choice to avoid skipping non-existent
> publications [5]. But we never concluded as to whether skipping should
> be a default behavior or an optional one. So, we need more opinions on
> it.
I'm leaning toward making the skipping behavior a default as I could
not find a good benefit for the current behavior (i.e., stopping
logical replication due to missing publications).
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 41+ messages in thread
* Re: Add an option to skip loading missing publication to avoid logical replication failure
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-09 03:29 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Masahiko Sawada <[email protected]>
@ 2025-03-10 03:33 ` Amit Kapila <[email protected]>
0 siblings, 0 replies; 41+ messages in thread
From: Amit Kapila @ 2025-03-10 03:33 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: vignesh C <[email protected]>; PostgreSQL Hackers <[email protected]>
On Sun, Mar 9, 2025 at 9:00 AM Masahiko Sawada <[email protected]> wrote:
>
> On Tue, Mar 4, 2025 at 9:04 PM Amit Kapila <[email protected]> wrote:
>
> >
> > I see the point of adding such an option to avoid breaking the current
> > applications (if there are any) that are relying on current behaviour.
> > But OTOH, I am not sure if users expect us to fail explicitly in such
> > scenarios.
>
> On the side note, with the patch since we ignore missing publications
> we will be able to create a subscription with whatever publications
> regardless their existence like:
>
> CREATE SUBSCRIPTION ... PUBLICATION pub1, pub2, pub3, pub4, pub5, ..., pub1000;
>
> The walsender corresponding to such subscriber can stream changes as
> soon as the listed publications are created on the publisher and
> REFRESH PUBLICATION is executed.
>
Right, but OTOH, one can expect that the data should start replicating
as soon as one creates a publication on the publisher. However, the
data for tables that are part of the publication will start
replicating from the point when the decoding process will process the
WAL corresponding to Create Publication. I suggest to add something
for this in docs unless it is already explained.
> >
> > This is a long-standing behaviour for which we get reports from time
> > to time, and once analyzing a failure, Tom also looked at it and
> > agreed that we don't have much choice to avoid skipping non-existent
> > publications [5]. But we never concluded as to whether skipping should
> > be a default behavior or an optional one. So, we need more opinions on
> > it.
>
> I'm leaning toward making the skipping behavior a default as I could
> not find a good benefit for the current behavior (i.e., stopping
> logical replication due to missing publications).
>
Sounds reasonable. We can always add the option at a later point if
required. Thanks for your input. We can continue reviewing and
committing the current patch.
--
With Regards,
Amit Kapila.
^ permalink raw reply [nested|flat] 41+ messages in thread
end of thread, other threads:[~2025-05-13 11:33 UTC | newest]
Thread overview: 41+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-04-29 18:13 [PATCH v5 1/5] Fix comments for WITH OIDs, removed at 578b22971 Justin Pryzby <[email protected]>
2022-08-13 06:07 [PATCH v4 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs. Nathan Bossart <[email protected]>
2022-08-13 06:07 [PATCH v3 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs. Nathan Bossart <[email protected]>
2022-08-13 06:07 [PATCH v2 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs. Nathan Bossart <[email protected]>
2022-08-13 06:07 [PATCH v1 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs. Nathan Bossart <[email protected]>
2025-03-04 06:52 Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-04 13:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-10 04:02 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-10 04:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-10 05:24 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-11 04:17 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-12 10:34 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-12 21:51 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Masahiko Sawada <[email protected]>
2025-03-11 10:30 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-12 10:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 02:08 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 03:48 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 05:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-03-13 06:13 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-13 09:50 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-13 13:56 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Dilip Kumar <[email protected]>
2025-03-14 08:58 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-04-30 16:31 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 03:43 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 03:53 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-04 13:14 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-11 15:19 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-12 05:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-12 09:01 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 04:41 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 04:46 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-02 05:22 ` Re: Add an option to skip loading missing publication to avoid logical replication failure vignesh C <[email protected]>
2025-05-02 10:44 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-02 16:54 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Tom Lane <[email protected]>
2025-05-05 06:18 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-06 10:03 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Xuneng Zhou <[email protected]>
2025-05-06 11:47 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-10 11:45 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-05-13 11:33 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
2025-03-09 03:29 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Masahiko Sawada <[email protected]>
2025-03-10 03:33 ` Re: Add an option to skip loading missing publication to avoid logical replication failure Amit Kapila <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox