public inbox for [email protected]
help / color / mirror / Atom feedFrom: Roberto Mello <[email protected]>
To: PostgreSQL Hackers <[email protected]>
Subject: Re: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
Date: Wed, 1 Apr 2026 17:25:58 -0600
Message-ID: <CAKz==b+-+QFiqtpzDYXYfCUjoKJUN9VjpdfM-Fou44C9KaQCZg@mail.gmail.com> (raw)
In-Reply-To: <CAKz==bJXZxTa-++31aSbPhfUNdRtD-Y7oBF=W193cL1er6L-BQ@mail.gmail.com>
References: <CAKz==bJXZxTa-++31aSbPhfUNdRtD-Y7oBF=W193cL1er6L-BQ@mail.gmail.com>
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)
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: PROPOSAL for when publication row filter columns are not in replica identity (BUG #19434)
In-Reply-To: <CAKz==b+-+QFiqtpzDYXYfCUjoKJUN9VjpdfM-Fou44C9KaQCZg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox