Hi hackers,
I would like to propose a patch to catch an invalid publication
configuration
at DDL time rather than deferring the failure to runtime DML.
Problem:
Currently, CREATE PUBLICATION and ALTER PUBLICATION allow specifying a
column
list on tables that have REPLICA IDENTITY FULL, even when the
publication publishes
UPDATE and DELETE operations (the default).
For example:
CREATE TABLE mytab (id int, val text, extra text);
ALTER TABLE mytab REPLICA IDENTITY FULL;
-- 1. This succeeds silently with no warning or error:
CREATE PUBLICATION mypub FOR TABLE mytab (id, val);
-- 2. Inserts work fine:
INSERT INTO mytab VALUES (1, 'hello', 'world');
-- 3. Any subsequent UPDATE or DELETE fails in production:
UPDATE mytab SET val = 'updated' WHERE id = 1;
-- ERROR: cannot update table "mytab"
-- DETAIL: Column list used by the publication does not cover the
replica identity.
This recently caused an unexpected production outage for us: the schema
migration
succeeded cleanly without error in CI/CD, but application web traffic
immediately
began failing on subsequent UPDATE and DELETE operations.
Proposed Solution:
Because REPLICA IDENTITY FULL requires the entire row to identify
tuples on the
subscriber, a column list on such a table can never work for updates or
deletes.
The attached patch adds early validation in
CheckPubRelationColumnList() and
AlterPublicationOptions():
1. During CREATE PUBLICATION and ALTER PUBLICATION ... ADD/SET TABLE,
if the
publication publishes UPDATE or DELETE and the table has REPLICA
IDENTITY
FULL, an immediate error is raised:
"ERROR: cannot use column list for relation "..." in publication
"...""
"DETAIL: Column lists cannot be specified for tables with REPLICA
IDENTITY FULL
when publishing updates or deletes."
"HINT: Either change the table's REPLICA IDENTITY or configure the
publication
to publish only 'insert'."
2. During ALTER PUBLICATION ... SET (publish = ...), if a publication
already
contains a table with a column list and REPLICA IDENTITY FULL,
adding 'update'
or 'delete' to publish actions is rejected.
3. Insert-only publications (e.g. publish = 'insert') remain allowed and
unaffected.
Regression tests in publication.sql and publication.out have been
updated,
and all regression tests pass.
Looking forward to your feedback.
Thanks,
Mohit
From 44d3018006a9ea1c6acee66bfe28fd6dc2db9d76 Mon Sep 17 00:00:00 2001
From: mohit-k-s <[email protected]>
Date: Sun, 25 Oct 2026 01:57:02 +0530
Subject: [PATCH] Disallow column lists on tables with REPLICA IDENTITY FULL in
publications.
Previously, CREATE PUBLICATION and ALTER PUBLICATION allowed specifying
a column list for tables with REPLICA IDENTITY FULL even when the
publication published UPDATE or DELETE operations. Because REPLICA
IDENTITY FULL requires the full tuple to identify rows on the
subscriber, any subsequent UPDATE or DELETE on such tables failed at
runtime with:
"ERROR: Column list used by the publication does not cover the replica identity."
This silent DDL success created a production trap where migrations
succeeded cleanly but normal runtime DML queries failed.
Fix this by validating the table's replica identity in
CheckPubRelationColumnList() and AlterPublicationOptions(). If the
publication publishes updates or deletes and the table has REPLICA
IDENTITY FULL, reject the command immediately with a clear error
message and hint. Column lists on REPLICA IDENTITY FULL tables remain
permitted for publications that only publish INSERT operations.
---
src/backend/commands/publicationcmds.c | 78 +++++++++++++++++++++--
src/test/regress/expected/publication.out | 35 ++++++----
src/test/regress/sql/publication.sql | 17 +++--
3 files changed, 110 insertions(+), 20 deletions(-)
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 301050104dd..c8983e1831c 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -780,10 +780,12 @@ TransformPubWhereClauses(List *tables, const char *queryString,
* 'publish_schema' indicates that the publication contains any TABLES IN
* SCHEMA elements (newly added in this command, or preexisting).
* 'pubviaroot' is the value of publish_via_partition_root.
+ * 'pubupdate' and 'pubdelete' indicate whether UPDATE or DELETE actions are published.
*/
static void
CheckPubRelationColumnList(char *pubname, List *tables,
- bool publish_schema, bool pubviaroot)
+ bool publish_schema, bool pubviaroot,
+ bool pubupdate, bool pubdelete)
{
ListCell *lc;
@@ -826,6 +828,20 @@ CheckPubRelationColumnList(char *pubname, List *tables,
RelationGetRelationName(pri->relation), pubname),
errdetail("Column lists cannot be specified for partitioned tables when %s is false.",
"publish_via_partition_root")));
+
+ /*
+ * Disallow specifying column list if the table has REPLICA IDENTITY
+ * FULL and the publication publishes UPDATE or DELETE.
+ */
+ if ((pubupdate || pubdelete) &&
+ pri->relation->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot use column list for relation \"%s.%s\" in publication \"%s\"",
+ get_namespace_name(RelationGetNamespace(pri->relation)),
+ RelationGetRelationName(pri->relation), pubname),
+ errdetail("Column lists cannot be specified for tables with REPLICA IDENTITY FULL when publishing updates or deletes."),
+ errhint("Either change the table's REPLICA IDENTITY or configure the publication to publish only 'insert'.")));
}
}
@@ -975,7 +991,9 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
CheckPubRelationColumnList(stmt->pubname, rels,
schemaidlist != NIL,
- publish_via_partition_root);
+ publish_via_partition_root,
+ pubactions.pubupdate,
+ pubactions.pubdelete);
PublicationAddTables(puboid, rels, true, NULL);
CloseTableList(rels);
@@ -1119,6 +1137,54 @@ AlterPublicationOptions(ParseState *pstate, AlterPublicationStmt *stmt,
}
}
+ /*
+ * Disallow publishing UPDATE or DELETE if the publication contains any
+ * table with a column list and REPLICA IDENTITY FULL.
+ */
+ if (!pubform->puballtables && publish_given &&
+ (pubactions.pubupdate || pubactions.pubdelete))
+ {
+ List *relids = GetIncludedPublicationRelations(pubform->oid,
+ PUBLICATION_PART_ROOT);
+
+ foreach(lc, relids)
+ {
+ Oid relid = lfirst_oid(lc);
+ HeapTuple rftuple;
+ bool has_collist;
+ Relation targetrel;
+
+ rftuple = SearchSysCache2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pubform->oid));
+ if (!HeapTupleIsValid(rftuple))
+ continue;
+ has_collist = !heap_attisnull(rftuple, Anum_pg_publication_rel_prattrs, NULL);
+ ReleaseSysCache(rftuple);
+
+ if (!has_collist)
+ continue;
+
+ targetrel = table_open(relid, AccessShareLock);
+ if (targetrel->rd_rel->relreplident == REPLICA_IDENTITY_FULL)
+ {
+ char *relname = pstrdup(RelationGetRelationName(targetrel));
+
+ table_close(targetrel, AccessShareLock);
+ list_free(relids);
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("cannot publish updates or deletes for publication \"%s\"",
+ stmt->pubname),
+ errdetail("Table \"%s\" has a column list and REPLICA IDENTITY FULL.",
+ relname),
+ errhint("Either change the table's REPLICA IDENTITY or remove the column list.")));
+ }
+ table_close(targetrel, AccessShareLock);
+ }
+ list_free(relids);
+ }
+
/* Everything ok, form a new tuple. */
memset(values, 0, sizeof(values));
memset(nulls, false, sizeof(nulls));
@@ -1255,7 +1321,9 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
publish_schema |= is_schema_publication(pubid);
CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
- pubform->pubviaroot);
+ pubform->pubviaroot,
+ pubform->pubupdate,
+ pubform->pubdelete);
PublicationAddTables(pubid, rels, false, stmt);
}
@@ -1292,7 +1360,9 @@ AlterPublicationTables(AlterPublicationStmt *stmt, HeapTuple tup,
TransformPubWhereClauses(rels, queryString, pubform->pubviaroot);
CheckPubRelationColumnList(stmt->pubname, rels, publish_schema,
- pubform->pubviaroot);
+ pubform->pubviaroot,
+ pubform->pubupdate,
+ pubform->pubdelete);
}
/*
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index b55da39fde5..d283ffa019e 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -1210,11 +1210,24 @@ DROP TABLE testpub_tbl5d;
-- tests with REPLICA IDENTITY FULL
CREATE TABLE testpub_tbl6 (a int, b text, c text);
ALTER TABLE testpub_tbl6 REPLICA IDENTITY FULL;
+-- fail: with REPLICA IDENTITY FULL no column list is allowed
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6 (a, b, c);
-UPDATE testpub_tbl6 SET a = 1;
-ERROR: cannot update table "testpub_tbl6"
-DETAIL: Column list used by the publication does not cover the replica identity.
-ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl6;
+ERROR: cannot use column list for relation "public.testpub_tbl6" in publication "testpub_fortable"
+DETAIL: Column lists cannot be specified for tables with REPLICA IDENTITY FULL when publishing updates or deletes.
+HINT: Either change the table's REPLICA IDENTITY or configure the publication to publish only 'insert'.
+-- fail: cannot CREATE PUBLICATION with column list for REPLICA IDENTITY FULL table
+CREATE PUBLICATION testpub_full_col FOR TABLE testpub_tbl6 (a, b);
+ERROR: cannot use column list for relation "public.testpub_tbl6" in publication "testpub_full_col"
+DETAIL: Column lists cannot be specified for tables with REPLICA IDENTITY FULL when publishing updates or deletes.
+HINT: Either change the table's REPLICA IDENTITY or configure the publication to publish only 'insert'.
+-- ok: insert-only publication allows column list for REPLICA IDENTITY FULL table
+CREATE PUBLICATION testpub_full_col_ins FOR TABLE testpub_tbl6 (a, b) WITH (publish = 'insert');
+-- fail: cannot change publish actions to include update/delete when column list exists on FULL table
+ALTER PUBLICATION testpub_full_col_ins SET (publish = 'insert, update');
+ERROR: cannot publish updates or deletes for publication "testpub_full_col_ins"
+DETAIL: Table "testpub_tbl6" has a column list and REPLICA IDENTITY FULL.
+HINT: Either change the table's REPLICA IDENTITY or remove the column list.
+DROP PUBLICATION testpub_full_col_ins;
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6; -- ok
UPDATE testpub_tbl6 SET a = 1;
-- make sure changing the column list is propagated to the catalog
@@ -1444,16 +1457,16 @@ HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
-- Case 2. REPLICA IDENTITY FULL
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
+-- fail - with REPLICA IDENTITY FULL no column list is allowed
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c);
+ERROR: cannot use column list for relation "public.rf_tbl_abcd_pk" in publication "testpub6"
+DETAIL: Column lists cannot be specified for tables with REPLICA IDENTITY FULL when publishing updates or deletes.
+HINT: Either change the table's REPLICA IDENTITY or configure the publication to publish only 'insert'.
-- fail - with REPLICA IDENTITY FULL no column list is allowed
-UPDATE rf_tbl_abcd_pk SET a = 1;
-ERROR: cannot update table "rf_tbl_abcd_pk"
-DETAIL: Column list used by the publication does not cover the replica identity.
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a, b, c, d);
--- fail - with REPLICA IDENTITY FULL no column list is allowed
-UPDATE rf_tbl_abcd_nopk SET a = 1;
-ERROR: cannot update table "rf_tbl_abcd_nopk"
-DETAIL: Column list used by the publication does not cover the replica identity.
+ERROR: cannot use column list for relation "public.rf_tbl_abcd_nopk" in publication "testpub6"
+DETAIL: Column lists cannot be specified for tables with REPLICA IDENTITY FULL when publishing updates or deletes.
+HINT: Either change the table's REPLICA IDENTITY or configure the publication to publish only 'insert'.
-- Case 3. REPLICA IDENTITY NOTHING
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY NOTHING;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index e3dbb2bc57c..5b4ac4f4c60 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -722,9 +722,18 @@ DROP TABLE testpub_tbl5d;
CREATE TABLE testpub_tbl6 (a int, b text, c text);
ALTER TABLE testpub_tbl6 REPLICA IDENTITY FULL;
+-- fail: with REPLICA IDENTITY FULL no column list is allowed
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6 (a, b, c);
-UPDATE testpub_tbl6 SET a = 1;
-ALTER PUBLICATION testpub_fortable DROP TABLE testpub_tbl6;
+
+-- fail: cannot CREATE PUBLICATION with column list for REPLICA IDENTITY FULL table
+CREATE PUBLICATION testpub_full_col FOR TABLE testpub_tbl6 (a, b);
+
+-- ok: insert-only publication allows column list for REPLICA IDENTITY FULL table
+CREATE PUBLICATION testpub_full_col_ins FOR TABLE testpub_tbl6 (a, b) WITH (publish = 'insert');
+
+-- fail: cannot change publish actions to include update/delete when column list exists on FULL table
+ALTER PUBLICATION testpub_full_col_ins SET (publish = 'insert, update');
+DROP PUBLICATION testpub_full_col_ins;
ALTER PUBLICATION testpub_fortable ADD TABLE testpub_tbl6; -- ok
UPDATE testpub_tbl6 SET a = 1;
@@ -889,12 +898,10 @@ UPDATE rf_tbl_abcd_nopk SET a = 1;
-- Case 2. REPLICA IDENTITY FULL
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY FULL;
ALTER TABLE rf_tbl_abcd_nopk REPLICA IDENTITY FULL;
+-- fail - with REPLICA IDENTITY FULL no column list is allowed
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_pk (c);
-- fail - with REPLICA IDENTITY FULL no column list is allowed
-UPDATE rf_tbl_abcd_pk SET a = 1;
ALTER PUBLICATION testpub6 SET TABLE rf_tbl_abcd_nopk (a, b, c, d);
--- fail - with REPLICA IDENTITY FULL no column list is allowed
-UPDATE rf_tbl_abcd_nopk SET a = 1;
-- Case 3. REPLICA IDENTITY NOTHING
ALTER TABLE rf_tbl_abcd_pk REPLICA IDENTITY NOTHING;
--
2.56.0
