public inbox for [email protected]
help / color / mirror / Atom feedPROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
3+ messages / 2 participants
[nested] [flat]
* PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
@ 2026-03-28 15:11 Roberto Mello <[email protected]>
2026-04-01 23:25 ` Re: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Roberto Mello <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Roberto Mello @ 2026-03-28 15:11 UTC (permalink / raw)
To: pgsql-hackers
Hi all,
Tim McLaughlin reported BUG #19434.
When a publication's WHERE clause references columns that are not
covered by the table's replica identity, UPDATE and DELETE silently
succeed at the SQL level but fail with:
ERROR: cannot update table "t"
DETAIL: Column used in the publication WHERE expression is not part
of the replica identity.
This error fires at DML time inside CheckCmdReplicaIdentity(), which
means the DBA discovers the misconfiguration only when production
writes start failing, potentially long after the publication or replica
identity
was created, and creating a real potentially serious problem of
inadvertently disallowing writes in a production system.
I have a patch that adds DDL-time WARNINGs (reusing the existing
pub_rf_contains_invalid_column() function) so the misconfiguration is
reported
immediately, but I am wondering if this is the right approach. It's doable
and
not very invasive, but wouldn't really do away with the potential footgun.
It would be an incremental improvement over the current situation where
this happens silently
(in reporter's case it was a serious production issue).
The warnings would fire at:
- CREATE PUBLICATION / ALTER PUBLICATION ... SET TABLE / ADD TABLE
when the WHERE clause references non-identity columns
- ALTER PUBLICATION SET (publish = ...) when the publish set is
widened to include UPDATE or DELETE while existing row filters
reference non-identity columns
- ALTER TABLE ... REPLICA IDENTITY when the new identity no longer
covers columns used in an existing publication WHERE clause
The existing DML-time ERROR would be preserved as a safety net.
The patch would not change the WAL format or remove the underlying
restriction. A follow-up patch could extend ExtractReplicaIdentity()
to include WHERE-referenced columns in WAL, which would eliminate the
restriction entirely.
Question being if the incremental and less invasive WARNING approach is
the better one given time constraints, or if a more invasive but more
complete
approach is warranted.
Thoughts?
Roberto Mello
Snowflake
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
2026-03-28 15:11 PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Roberto Mello <[email protected]>
@ 2026-04-01 23:25 ` Roberto Mello <[email protected]>
2026-04-02 09:21 ` RE: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Zhijie Hou (Fujitsu) <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Roberto Mello @ 2026-04-01 23:25 UTC (permalink / raw)
To: pgsql-hackers
On Sat, Mar 28, 2026 at 9:11 AM Roberto Mello <[email protected]>
wrote:
> Hi all,
>
> Tim McLaughlin reported BUG #19434.
>
> When a publication's WHERE clause references columns that are not
> covered by the table's replica identity, UPDATE and DELETE silently
> succeed at the SQL level but fail with:
>
> ERROR: cannot update table "t"
> DETAIL: Column used in the publication WHERE expression is not part
> of the replica identity.
>
> This error fires at DML time inside CheckCmdReplicaIdentity(), which
> means the DBA discovers the misconfiguration only when production
> writes start failing, potentially long after the publication or replica
> identity
> was created, and creating a real potentially serious problem of
> inadvertently disallowing writes in a production system.
>
The attached patch adds DDL-time WARNINGs so the misconfiguration is
reported immediately. The warnings fire at:
- CREATE PUBLICATION / ALTER PUBLICATION ... SET TABLE / ADD TABLE
when the WHERE clause references non-identity columns
- ALTER PUBLICATION SET (publish = ...) when the publish set is
widened to include UPDATE or DELETE while existing row filters
reference non-identity columns
- ALTER TABLE ... REPLICA IDENTITY when the new identity no longer
covers columns used in an existing publication WHERE clause
The existing DML-time ERROR is preserved as a safety net.
Notes:
- The check reuses the existing pub_rf_contains_invalid_column()
function, which walks the WHERE expression tree and compares
referenced columns against the replica identity bitmap.
- For the publication DDL paths (CREATE/ALTER PUBLICATION ...
ADD/SET TABLE), a CommandCounterIncrement() is needed after
PublicationAddTables() so that the newly inserted
pg_publication_rel rows are visible to the syscache.
- For the ALTER PUBLICATION SET (publish = ...) path, the existing
CommandCounterIncrement() after CatalogTupleUpdate() already
makes the updated publish flags visible. The check iterates
the publication's tables via GetIncludedPublicationRelations().
- For the ALTER TABLE path, a CommandCounterIncrement() followed
by a fresh table_open() ensures the relcache reflects the new
replica identity before running the check.
- Partition handling uses the existing get_partition_ancestors()
and pubviaroot logic.
- Regression tests are updated to expect the new WARNINGs and
include new targeted test cases, covering both positive
(warning fires) and negative (INSERT-only, FULL identity) cases.
- Documentation updates in logical-replication.sgml.
Known limitations:
- ALTER PUBLICATION SET (publish_via_partition_root = ...) is not
checked. This is a narrow edge case involving partitioned tables
and is deferred to a follow-up.
- DROP INDEX on a replica-identity index is not checked due to
layering concerns (would require publication code in
catalog/index.c).
This patch does not change the WAL format or remove the underlying
restriction. A future patch could extend ExtractReplicaIdentity()
to include WHERE-referenced columns in WAL, which would eliminate the
restriction entirely.
This is a v2 that incorporates fixes, including documentation emphasis.
Roberto Mello
Snowflake
Attachments:
[application/octet-stream] v2-0001-Emit-WARNING-when-publication-row-filter-columns.patch (25.4K, 3-v2-0001-Emit-WARNING-when-publication-row-filter-columns.patch)
download | inline diff:
From c551c7f424ab713a7a4275eec9980110977e41b9 Mon Sep 17 00:00:00 2001
From: Roberto Mello <[email protected]>
Date: Fri, 27 Mar 2026 14:29:15 -0600
Subject: [PATCH v2] Emit WARNING when publication row filter columns miss
replica identity
When a publication WHERE clause references columns not covered by the
table replica identity, UPDATE and DELETE fail at DML time with an
ERROR from CheckCmdReplicaIdentity(). The DBA only discovers this
misconfiguration when production writes start failing.
Add DDL-time WARNINGs so the problem is reported immediately. The
warnings fire at:
- CREATE PUBLICATION / ALTER PUBLICATION ... ADD TABLE / SET TABLE
when the WHERE clause references non-identity columns
- ALTER PUBLICATION SET (publish = ...) when widening the publish
set to include UPDATE or DELETE while existing row filters
reference non-identity columns
- ALTER TABLE ... REPLICA IDENTITY when the new identity no longer
covers columns used in a publication WHERE clause
The existing DML-time ERROR is preserved as a safety net.
A CommandCounterIncrement() is needed at each call site so that catalog
rows inserted by PublicationAddTables() or relation_mark_replica_identity()
are visible to the syscache/relcache when the check runs. For the ALTER
TABLE path, a fresh table_open() after CCI ensures the relcache reflects
the updated replica identity.
---
doc/src/sgml/logical-replication.sgml | 15 +++
src/backend/commands/publicationcmds.c | 130 ++++++++++++++++++++++
src/backend/commands/tablecmds.c | 89 ++++++++++++++-
src/test/regress/expected/publication.out | 63 +++++++++++
src/test/regress/sql/publication.sql | 35 ++++++
5 files changed, 330 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/logical-replication.sgml b/doc/src/sgml/logical-replication.sgml
index 23b268273b9..cc0dd5f57e1 100644
--- a/doc/src/sgml/logical-replication.sgml
+++ b/doc/src/sgml/logical-replication.sgml
@@ -881,6 +881,21 @@ HINT: To initiate replication, you must manually create the replication slot, e
<literal>WHERE</literal> clause can use any column.
</para>
+ <warning>
+ <para>
+ If a row filter references columns not covered by the replica identity,
+ <command>UPDATE</command> and <command>DELETE</command> operations on the
+ table will fail at run time with an error. A <literal>WARNING</literal> is
+ emitted at <command>CREATE PUBLICATION</command>, <command>ALTER
+ PUBLICATION</command>, or <command>ALTER TABLE ... REPLICA IDENTITY</command>
+ time to alert the user to this misconfiguration. To resolve it, either
+ adjust the row filter to reference only replica identity columns, set the
+ replica identity to <literal>FULL</literal>, or configure the publication
+ to not publish <command>UPDATE</command> or <command>DELETE</command>
+ operations.
+ </para>
+ </warning>
+
</sect2>
<sect2 id="logical-replication-row-filter-transformations">
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 440adb356ad..8769e342a54 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -22,6 +22,7 @@
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
#include "catalog/objectaddress.h"
+#include "catalog/partition.h"
#include "catalog/pg_database.h"
#include "catalog/pg_inherits.h"
#include "catalog/pg_namespace.h"
@@ -346,6 +347,55 @@ pub_rf_contains_invalid_column(Oid pubid, Relation relation, List *ancestors,
return result;
}
+/*
+ * Warn at DDL time if a publication's row filter references columns that are
+ * not part of the table's replica identity. This catches a configuration
+ * mistake that would otherwise only surface as a hard ERROR on the first
+ * UPDATE or DELETE against the table (in CheckCmdReplicaIdentity).
+ *
+ * We only warn (not error) so that the existing DML-time safety check
+ * remains the authoritative gate. The warning gives the DBA immediate
+ * feedback when creating or altering the publication.
+ */
+static void
+check_pub_rf_columns_at_ddl(Oid pubid, List *rels,
+ bool pubupdate, bool pubdelete,
+ bool pubviaroot)
+{
+ ListCell *lc;
+
+ /* No point checking if the publication doesn't replicate UPD/DEL. */
+ if (!pubupdate && !pubdelete)
+ return;
+
+ foreach(lc, rels)
+ {
+ PublicationRelInfo *pub_rel = (PublicationRelInfo *) lfirst(lc);
+ Relation rel = pub_rel->relation;
+ List *ancestors = NIL;
+
+ /* Only tables with a WHERE clause need the check. */
+ if (pub_rel->whereClause == NULL)
+ continue;
+
+ if (rel->rd_rel->relispartition)
+ ancestors = get_partition_ancestors(RelationGetRelid(rel));
+
+ if (pub_rf_contains_invalid_column(pubid, rel, ancestors,
+ pubviaroot))
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column used in the publication WHERE expression is not part of the replica identity for table \"%s\"",
+ RelationGetRelationName(rel)),
+ errhint("UPDATE and DELETE on this table will fail until "
+ "the replica identity covers the filtered columns, "
+ "or the publication is changed to not publish those "
+ "operations.")));
+
+ list_free(ancestors);
+ }
+}
+
/*
* Check for invalid columns in the publication table definition.
*
@@ -978,6 +1028,15 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
publish_via_partition_root);
PublicationAddTables(puboid, rels, true, NULL);
+
+ /* Make publication_rel rows visible for the check. */
+ CommandCounterIncrement();
+
+ check_pub_rf_columns_at_ddl(puboid, rels,
+ pubactions.pubupdate,
+ pubactions.pubdelete,
+ publish_via_partition_root);
+
CloseTableList(rels);
}
@@ -1028,6 +1087,8 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
char publish_generated_columns;
ObjectAddress obj;
Form_pg_publication pubform;
+ bool old_pubupdate;
+ bool old_pubdelete;
List *root_relids = NIL;
ListCell *lc;
@@ -1128,6 +1189,10 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+ /* Remember current publish flags so we can detect widening. */
+ old_pubupdate = pubform->pubupdate;
+ old_pubdelete = pubform->pubdelete;
+
/* Everything ok, form a new tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -1170,6 +1235,55 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
pubform = (Form_pg_publication) GETSTRUCT(tup);
+ /*
+ * If the publish set was widened to include UPDATE or DELETE, check
+ * whether any existing row filters reference columns not covered by the
+ * replica identity. Skip FOR ALL TABLES publications, which cannot have
+ * per-table WHERE clauses.
+ */
+ if (publish_given &&
+ ((pubactions.pubupdate && !old_pubupdate) ||
+ (pubactions.pubdelete && !old_pubdelete)) &&
+ !pubform->puballtables)
+ {
+ List *relids;
+ ListCell *rlc;
+
+ relids = GetIncludedPublicationRelations(pubform->oid,
+ PUBLICATION_PART_ALL);
+
+ foreach(rlc, relids)
+ {
+ Oid relid = lfirst_oid(rlc);
+ Relation pubrel;
+ List *ancestors = NIL;
+
+ pubrel = table_open(relid, AccessShareLock);
+
+ if (pubrel->rd_rel->relispartition)
+ ancestors = get_partition_ancestors(relid);
+
+ if (pub_rf_contains_invalid_column(pubform->oid, pubrel,
+ ancestors,
+ pubform->pubviaroot))
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column used in the row filter of publication \"%s\" "
+ "is not part of the replica identity for table \"%s\"",
+ stmt->pubname,
+ RelationGetRelationName(pubrel)),
+ errhint("UPDATE and DELETE on this table will fail until "
+ "the replica identity covers the filtered columns, "
+ "or the publication is changed to not publish those "
+ "operations.")));
+
+ list_free(ancestors);
+ table_close(pubrel, AccessShareLock);
+ }
+
+ list_free(relids);
+ }
+
/* Invalidate the relcache. */
if (pubform->puballtables)
{
@@ -1267,6 +1381,14 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
pubform->pubviaroot);
PublicationAddTables(pubid, rels, false, stmt);
+
+ /* Make publication_rel rows visible for the check. */
+ CommandCounterIncrement();
+
+ check_pub_rf_columns_at_ddl(pubid, rels,
+ pubform->pubupdate,
+ pubform->pubdelete,
+ pubform->pubviaroot);
}
else if (stmt->action == AP_DropObjects)
PublicationDropTables(pubid, rels, false);
@@ -1412,6 +1534,14 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
*/
PublicationAddTables(pubid, rels, true, stmt);
+ /* Make publication_rel rows visible for the check. */
+ CommandCounterIncrement();
+
+ check_pub_rf_columns_at_ddl(pubid, rels,
+ pubform->pubupdate,
+ pubform->pubdelete,
+ pubform->pubviaroot);
+
CloseTableList(delrels);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 8b4ebc6f226..80391b578fd 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -48,6 +48,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_policy.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
#include "catalog/pg_rewrite.h"
#include "catalog/pg_statistic_ext.h"
@@ -61,6 +62,7 @@
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/event_trigger.h"
+#include "commands/publicationcmds.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
@@ -18720,6 +18722,70 @@ relation_mark_replica_identity(Relation rel, char ri_type, Oid indexOid,
table_close(pg_index, RowExclusiveLock);
}
+/*
+ * Check whether any publication row filter on this relation references
+ * columns that are not part of the (possibly just-changed) replica identity.
+ * If so, emit a WARNING so the DBA knows that UPDATE/DELETE will fail.
+ */
+static void
+check_relation_publications_rf(Relation rel)
+{
+ Oid relid = RelationGetRelid(rel);
+ List *puboids;
+ ListCell *lc;
+ List *ancestors = NIL;
+
+ puboids = GetRelationIncludedPublications(relid);
+ if (puboids == NIL)
+ return;
+
+ if (rel->rd_rel->relispartition)
+ ancestors = get_partition_ancestors(relid);
+
+ foreach(lc, puboids)
+ {
+ Oid pubid = lfirst_oid(lc);
+ HeapTuple pubtup;
+ Form_pg_publication pubform;
+
+ pubtup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(pubid));
+ if (!HeapTupleIsValid(pubtup))
+ continue;
+
+ pubform = (Form_pg_publication) GETSTRUCT(pubtup);
+
+ /*
+ * Only check publications that replicate UPDATE or DELETE;
+ * INSERT-only publications do not need old tuple values.
+ */
+ if ((pubform->pubupdate || pubform->pubdelete) &&
+ !pubform->puballtables &&
+ pub_rf_contains_invalid_column(pubid, rel, ancestors,
+ pubform->pubviaroot))
+ {
+ char *pubname = pstrdup(NameStr(pubform->pubname));
+
+ ReleaseSysCache(pubtup);
+ ereport(WARNING,
+ (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
+ errmsg("column used in the row filter of publication \"%s\" "
+ "is not part of the replica identity for table \"%s\"",
+ pubname, RelationGetRelationName(rel)),
+ errhint("UPDATE and DELETE on this table will fail until "
+ "the replica identity covers the filtered columns, "
+ "or the publication is changed to not publish those "
+ "operations.")));
+ pfree(pubname);
+ continue;
+ }
+
+ ReleaseSysCache(pubtup);
+ }
+
+ list_free(ancestors);
+ list_free(puboids);
+}
+
/*
* ALTER TABLE <name> REPLICA IDENTITY ...
*/
@@ -18733,17 +18799,18 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
if (stmt->identity_type == REPLICA_IDENTITY_DEFAULT)
{
relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true);
- return;
+ goto check_publications;
}
else if (stmt->identity_type == REPLICA_IDENTITY_FULL)
{
relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true);
+ /* FULL covers all columns, so no warning is possible. */
return;
}
else if (stmt->identity_type == REPLICA_IDENTITY_NOTHING)
{
relation_mark_replica_identity(rel, stmt->identity_type, InvalidOid, true);
- return;
+ goto check_publications;
}
else if (stmt->identity_type == REPLICA_IDENTITY_INDEX)
{
@@ -18832,6 +18899,24 @@ ATExecReplicaIdentity(Relation rel, ReplicaIdentityStmt *stmt, LOCKMODE lockmode
relation_mark_replica_identity(rel, stmt->identity_type, indexOid, true);
index_close(indexRel, NoLock);
+
+check_publications:
+
+ /*
+ * The catalog changes from relation_mark_replica_identity are not yet
+ * visible to the syscache/relcache. Make them visible so that
+ * check_relation_publications_rf can see the new replica identity when
+ * evaluating publication row filters.
+ */
+ CommandCounterIncrement();
+ {
+ Oid relid = RelationGetRelid(rel);
+ Relation freshrel;
+
+ freshrel = table_open(relid, NoLock);
+ check_relation_publications_rf(freshrel);
+ table_close(freshrel, NoLock);
+ }
}
/*
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index d2aa9d45e4a..0d291c98a0a 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -883,17 +883,23 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (b > 99);
-- ok - "b" is a PK col
UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "c" is not part of the PK
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (d > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "d" is not part of the PK
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
-- 1b. REPLICA IDENTITY is DEFAULT and table has no PK
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "a" is not part of REPLICA IDENTITY
UPDATE rf_tbl_abcd_nopk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_nopk"
@@ -910,17 +916,25 @@ UPDATE rf_tbl_abcd_nopk SET a = 1;
-- Case 3. REPLICA IDENTITY NOTHING
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
+WARNING: column used in the row filter of publication "testpub6" is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "a" is in PK but it is not part of REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "c" is not in PK and not in REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "a" is not in REPLICA IDENTITY NOTHING
UPDATE rf_tbl_abcd_nopk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_nopk"
@@ -932,7 +946,11 @@ ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY USING INDEX idx_abcd_pk_c;
ALTER TABLE rf_tbl_abcd_nopk ALTER COLUMN c SET NOT NULL;
CREATE UNIQUE INDEX idx_abcd_nopk_c ON rf_tbl_abcd_nopk(c);
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY USING INDEX idx_abcd_nopk_c;
+WARNING: column used in the row filter of publication "testpub6" is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (a > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "a" is in PK but it is not part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_pk"
@@ -941,6 +959,8 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
-- ok - "c" is not in PK but it is part of REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "a" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_nopk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_nopk"
@@ -979,6 +999,8 @@ ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
-- Now change the root filter to use a column "b"
-- (which is not in the replica identity)
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk_1 WHERE (b > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_part_pk_1"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- ok - we don't have row filter for partitioned table.
ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=0);
-- fail - "b" is not in REPLICA IDENTITY INDEX
@@ -990,10 +1012,51 @@ DETAIL: Column used in the publication WHERE expression is not part of the repl
ALTER PUBLICATION testpub6 SET (PUBLISH_VIA_PARTITION_ROOT=1);
-- ok - can use row filter for partitioned table
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_part_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
-- fail - "b" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_part_pk SET a = 1;
ERROR: cannot update table "rf_tbl_abcd_part_pk_1"
DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+-- Tests for DDL-time WARNING when row filter columns are not in replica
+-- identity. The DML-time ERROR still fires as a safety net.
+-- Reset tables to known state
+ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY DEFAULT;
+ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
+-- DDL-time warning: "c" is not part of PK (DEFAULT replica identity)
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+WARNING: column used in the publication WHERE expression is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
+-- DML-time error still present
+UPDATE rf_tbl_abcd_pk SET a = 1;
+ERROR: cannot update table "rf_tbl_abcd_pk"
+DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+-- No warning: publication only publishes INSERT
+ALTER PUBLICATION testpub6 SET (publish = 'insert');
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+-- DDL-time warning when widening publish set to include UPDATE/DELETE
+-- while an existing row filter references a non-identity column.
+ALTER PUBLICATION testpub6 SET (publish = 'insert, update, delete');
+WARNING: column used in the row filter of publication "testpub6" is not part of the replica identity for table "rf_tbl_abcd_pk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
+-- DML-time error still present after widening
+UPDATE rf_tbl_abcd_pk SET a = 1;
+ERROR: cannot update table "rf_tbl_abcd_pk"
+DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
+-- No warning: REPLICA IDENTITY FULL covers all columns
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
+-- No warning: widening publish set when REPLICA IDENTITY FULL
+ALTER PUBLICATION testpub6 SET (publish = 'insert');
+ALTER PUBLICATION testpub6 SET (publish = 'insert, update, delete');
+-- DDL-time warning from ALTER TABLE REPLICA IDENTITY: narrowing identity
+-- while a publication filter references a now-uncovered column.
+ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
+WARNING: column used in the row filter of publication "testpub6" is not part of the replica identity for table "rf_tbl_abcd_nopk"
+HINT: UPDATE and DELETE on this table will fail until the replica identity covers the filtered columns, or the publication is changed to not publish those operations.
+-- DML-time error still present
+UPDATE rf_tbl_abcd_nopk SET a = 1;
+ERROR: cannot update table "rf_tbl_abcd_nopk"
+DETAIL: Column used in the publication WHERE expression is not part of the replica identity.
DROP PUBLICATION testpub6;
DROP TABLE rf_tbl_abcd_pk;
DROP TABLE rf_tbl_abcd_nopk;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 6bafad27571..b35430da612 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -548,6 +548,41 @@ ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_part_pk WHERE (b > 99);
-- fail - "b" is not in REPLICA IDENTITY INDEX
UPDATE rf_tbl_abcd_part_pk SET a = 1;
+-- Tests for DDL-time WARNING when row filter columns are not in replica
+-- identity. The DML-time ERROR still fires as a safety net.
+
+-- Reset tables to known state
+ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY DEFAULT;
+ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
+
+-- DDL-time warning: "c" is not part of PK (DEFAULT replica identity)
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+-- DML-time error still present
+UPDATE rf_tbl_abcd_pk SET a = 1;
+
+-- No warning: publication only publishes INSERT
+ALTER PUBLICATION testpub6 SET (publish = 'insert');
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk WHERE (c > 99);
+
+-- DDL-time warning when widening publish set to include UPDATE/DELETE
+-- while an existing row filter references a non-identity column.
+ALTER PUBLICATION testpub6 SET (publish = 'insert, update, delete');
+-- DML-time error still present after widening
+UPDATE rf_tbl_abcd_pk SET a = 1;
+
+-- No warning: REPLICA IDENTITY FULL covers all columns
+ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk WHERE (a > 99);
+
+-- No warning: widening publish set when REPLICA IDENTITY FULL
+ALTER PUBLICATION testpub6 SET (publish = 'insert');
+ALTER PUBLICATION testpub6 SET (publish = 'insert, update, delete');
+
+-- DDL-time warning from ALTER TABLE REPLICA IDENTITY: narrowing identity
+-- while a publication filter references a now-uncovered column.
+ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
+-- DML-time error still present
+UPDATE rf_tbl_abcd_nopk SET a = 1;
+
DROP PUBLICATION testpub6;
DROP TABLE rf_tbl_abcd_pk;
DROP TABLE rf_tbl_abcd_nopk;
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
2026-03-28 15:11 PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Roberto Mello <[email protected]>
2026-04-01 23:25 ` Re: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Roberto Mello <[email protected]>
@ 2026-04-02 09:21 ` Zhijie Hou (Fujitsu) <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Zhijie Hou (Fujitsu) @ 2026-04-02 09:21 UTC (permalink / raw)
To: Roberto Mello <[email protected]>; +Cc: pgsql-hackers
On Thursday, April 2, 2026 7:26 AM Roberto Mello <[email protected]> wrote:
> When a publication's WHERE clause references columns that are not
> covered by the table's replica identity, UPDATE and DELETE silently
> succeed at the SQL level but fail with:
>
> ERROR: cannot update table "t"
> DETAIL: Column used in the publication WHERE expression is not part
> of the replica identity.
>
> This error fires at DML time inside CheckCmdReplicaIdentity(), which means the
> DBA discovers the misconfiguration only when production writes start failing,
> potentially long after the publication or replica identity was created, and
> creating a real potentially serious problem of inadvertently disallowing
> writes in a production system.
Thanks for raising this point.
>
> The attached patch adds DDL-time WARNINGs so the misconfiguration is reported
> immediately. The warnings fire at:
>
> - CREATE PUBLICATION / ALTER PUBLICATION ... SET TABLE / ADD TABLE
> when the WHERE clause references non-identity columns
>
> - ALTER PUBLICATION SET (publish = ...) when the publish set is
> widened to include UPDATE or DELETE while existing row filters
> reference non-identity columns
>
> - ALTER TABLE ... REPLICA IDENTITY when the new identity no longer
> covers columns used in an existing publication WHERE clause
>
> The existing DML-time ERROR is preserved as a safety net.
I recall that the reason we did not add hard restrictions for all those cases is
that it is tricky to catch every DDL that might bypass the restrictions. Even if
we were to identify them today, implementing the necessary checks would require
a significant amount of code, and future DDL changes could introduce new ways to
bypass the restrictions without us noticing. All of this suggests that the
effort and complexity involved could not be justified by the value of such
checks. Therefore, we decided to handle these cases in a uniform place (e.g.,
during DML processing).
That said, while it might be acceptable to add a few simple checks for the most
common cases, I notice that the patch seems to lack handling for partitioned
tables. Specifically, when a partitioned table is added to a publication with
pubviaroot = true and a valid row filter, we may need to verify that the replica
identity of every leaf partition is sufficient to support the row filter. I
recall this part would add some complexity.
Considering that the stated plan for this thread is to eventually WAL‑log the
columns needed for row filters regardless of replica identity, I suspect it
might not be ideal to introduce a large amount of code to emit warnings at this
stage. If we support logging the necessary columns in the WAL and enable that
behavior by default, these checks might no longer be useful. However, if we do
not enable WAL logging of row‑filter columns by default, then such checks could
still have some value.
> Known limitations:
>
> - ALTER PUBLICATION SET (publish_via_partition_root = ...) is not
> checked. This is a narrow edge case involving partitioned tables
> and is deferred to a follow-up.
>
> - DROP INDEX on a replica-identity index is not checked due to
> layering concerns (would require publication code in
> catalog/index.c).
In addition to above cases, I think we also need to check the replica identity
whenever a new partition is attached to a published partitioned table that has a
row filter. The relevant DDLs include ATTACH PARTITION and CREATE PARTITION ...
OF... And, the recently introduced SPLIT PARTITION command might also have an
impact, although I cannot say for certain without a closer look.
Best Regards,
Hou zj
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-04-02 09:21 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-28 15:11 PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434) Roberto Mello <[email protected]>
2026-04-01 23:25 ` Roberto Mello <[email protected]>
2026-04-02 09:21 ` Zhijie Hou (Fujitsu) <[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