v1-0001-Allow-ALTER-COLUMN-TYPE-for-publication-column-li.patch

From 049b9745069befda5d00f6680c7dc06367666814 Mon Sep 17 00:00:00 2001
From: Kevin Biju <kevinkbiju@gmail.com>
Date: Mon, 22 Dec 2025 00:29:27 +0530
Subject: [PATCH v1] Allow ALTER COLUMN TYPE for publication column lists

Currently, ALTER COLUMN TYPE is blocked for any column with a
pg_publication_rel dependency, even when the column is only in a publication's
column list and not referenced in a row filter. This is because both
column lists and row filters create identical entries in pg_depend.

This patch refines the check to distinguish between the two cases.
If prqual is NULL, the dependency is from a column list only and
ALTER COLUMN TYPE is allowed. If prqual is not NULL, we parse it
and validate if the specific column is actually used in the
row filter expression.
---
 src/backend/commands/tablecmds.c          | 69 ++++++++++++++++++----
 src/test/regress/expected/publication.out | 70 +++++++++++++++++++++++
 src/test/regress/sql/publication.sql      | 24 ++++++++
 3 files changed, 151 insertions(+), 12 deletions(-)

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 6b1a00ed477..8d36605c79d 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15259,19 +15259,64 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
 				break;
 
 			case PublicationRelRelationId:
+				{
+					HeapTuple pubreloidtuple;
+					Form_pg_publication_rel pubrelform;
+					Oid publicationId;
+					HeapTuple pubreltuple;
+					bool isnull;
+					Datum rfdatum;
+
+					pubreloidtuple = SearchSysCache1(PUBLICATIONREL,
+												  ObjectIdGetDatum(foundObject.objectId));
+					if (!HeapTupleIsValid(pubreloidtuple))
+						elog(ERROR, "cache lookup failed for pg_publication_rel OID %u", foundObject.objectId);
+
+					pubrelform = (Form_pg_publication_rel) GETSTRUCT(pubreloidtuple);
+					publicationId = pubrelform->prpubid;
+					ReleaseSysCache(pubreloidtuple);
+
+					pubreltuple = SearchSysCache2(PUBLICATIONRELMAP,
+							ObjectIdGetDatum(RelationGetRelid(rel)),
+							ObjectIdGetDatum(publicationId));
+					if (!HeapTupleIsValid(pubreltuple))
+						elog(ERROR, "cache lookup failed for publication ID %u", publicationId);
+
+					/* Lookup the prqual to check row filter, if it is null dependency is a column list only. */
+					rfdatum = SysCacheGetAttr(PUBLICATIONRELMAP, pubreltuple,
+											Anum_pg_publication_rel_prqual, &isnull);
+
+					if (!isnull)
+					{
+						Node *rfnode;
+						Bitmapset *rfattrs = NULL;
 
-				/*
-				 * Column reference in a PUBLICATION ... FOR TABLE ... WHERE
-				 * clause.  Same issues as above.  FIXME someday.
-				 */
-				if (subtype == AT_AlterColumnType)
-					ereport(ERROR,
-							(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-							 errmsg("cannot alter type of a column used by a publication WHERE clause"),
-							 errdetail("%s depends on column \"%s\"",
-									   getObjectDescription(&foundObject, false),
-									   colName)));
-				break;
+						/*
+						 * The column dependency could be from the column list, but there's
+						 * also a row filter present. Check if this specific column is used
+						 * in the row filter expression.
+						 */
+						rfnode = (Node *) stringToNode(TextDatumGetCString(rfdatum));
+						pull_varattnos(rfnode, 1, &rfattrs);
+						if (bms_is_member(attnum - FirstLowInvalidHeapAttributeNumber, rfattrs))
+						{
+							/*
+							 * Column reference in a PUBLICATION ... FOR TABLE ... WHERE
+							 * clause.  Same issues as above.  FIXME someday.
+							 */
+							if (subtype == AT_AlterColumnType)
+								ereport(ERROR,
+										(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+										errmsg("cannot alter type of a column used by a publication WHERE clause"),
+										errdetail("%s depends on column \"%s\"",
+												getObjectDescription(&foundObject, false),
+												colName)));
+						}
+					}
+
+					ReleaseSysCache(pubreltuple);
+					break;
+				}
 
 			default:
 
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index e72d1308967..ced751de631 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -631,6 +631,54 @@ CREATE PUBLICATION testpub8 FOR TABLE testpub_rf_tbl7 WHERE (y > 100);
 ALTER TABLE testpub_rf_tbl7 ALTER COLUMN y SET EXPRESSION AS (x * testpub_rf_func2());
 ERROR:  ALTER TABLE / SET EXPRESSION is not supported for virtual generated columns in tables that are part of a publication
 DETAIL:  Column "y" of relation "testpub_rf_tbl7" is a virtual generated column.
+RESET client_min_messages;
+-- test that ALTER COLUMN ALTER [ SET DATA ] TYPE is rejected when the column is in a row filter
+CREATE TABLE testpub_rf_tbl8 (id int PRIMARY KEY, x int, y int, z int);
+CREATE PUBLICATION testpub9 FOR TABLE testpub_rf_tbl8 WHERE (x > 100 AND y < 200);
+WARNING:  "wal_level" is insufficient to publish logical changes
+HINT:  Set "wal_level" to "logical" before creating subscriptions.
+SET client_min_messages = 'ERROR';
+-- fail
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN x TYPE bigint;
+ERROR:  cannot alter type of a column used by a publication WHERE clause
+DETAIL:  publication of table testpub_rf_tbl8 in publication testpub9 depends on column "x"
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN y TYPE bigint;
+ERROR:  cannot alter type of a column used by a publication WHERE clause
+DETAIL:  publication of table testpub_rf_tbl8 in publication testpub9 depends on column "y"
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN y TYPE bigint, ALTER COLUMN z TYPE bigint;
+ERROR:  cannot alter type of a column used by a publication WHERE clause
+DETAIL:  publication of table testpub_rf_tbl8 in publication testpub9 depends on column "y"
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN z TYPE bigint, ALTER COLUMN x TYPE bigint;
+ERROR:  cannot alter type of a column used by a publication WHERE clause
+DETAIL:  publication of table testpub_rf_tbl8 in publication testpub9 depends on column "x"
+\d testpub_rf_tbl8
+          Table "public.testpub_rf_tbl8"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ x      | integer |           |          | 
+ y      | integer |           |          | 
+ z      | integer |           |          | 
+Indexes:
+    "testpub_rf_tbl8_pkey" PRIMARY KEY, btree (id)
+Publications:
+    "testpub9" WHERE ((x > 100) AND (y < 200))
+
+-- ok
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN z TYPE bigint;
+\d testpub_rf_tbl8
+          Table "public.testpub_rf_tbl8"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ id     | integer |           | not null | 
+ x      | integer |           |          | 
+ y      | integer |           |          | 
+ z      | bigint  |           |          | 
+Indexes:
+    "testpub_rf_tbl8_pkey" PRIMARY KEY, btree (id)
+Publications:
+    "testpub9" WHERE ((x > 100) AND (y < 200))
+
 RESET client_min_messages;
 DROP TABLE testpub_rf_tbl1;
 DROP TABLE testpub_rf_tbl2;
@@ -650,6 +698,8 @@ DROP OPERATOR =#>(integer, integer);
 DROP FUNCTION testpub_rf_func1(integer, integer);
 DROP FUNCTION testpub_rf_func2();
 DROP COLLATION user_collation;
+DROP PUBLICATION testpub9;
+DROP TABLE testpub_rf_tbl8;
 -- ======================================================
 -- More row filter tests for validating column references
 CREATE TABLE rf_tbl_abcd_nopk(a int, b int, c int, d int);
@@ -1114,6 +1164,26 @@ Not-null constraints:
     "testpub_tbl_both_filters_a_not_null" NOT NULL "a"
     "testpub_tbl_both_filters_c_not_null" NOT NULL "c"
 
+SET client_min_messages = 'ERROR';
+-- fail - ALTER TABLE ALTER COLUMN [ SET DATA ] TYPE affecting column in row filter and column list
+ALTER TABLE testpub_tbl_both_filters ALTER COLUMN c TYPE bigint;
+ERROR:  cannot alter type of a column used by a publication WHERE clause
+DETAIL:  publication of table testpub_tbl_both_filters in publication testpub_both_filters depends on column "c"
+-- ok - ALTER TABLE ALTER COLUMN ALTER [ SET DATA ] TYPE affecting column in column list and not in row filter
+ALTER TABLE testpub_tbl_both_filters ALTER COLUMN a TYPE text;
+\d testpub_tbl_both_filters
+      Table "public.testpub_tbl_both_filters"
+ Column |  Type   | Collation | Nullable | Default 
+--------+---------+-----------+----------+---------
+ a      | text    |           | not null | 
+ b      | integer |           |          | 
+ c      | integer |           | not null | 
+Indexes:
+    "testpub_tbl_both_filters_pkey" PRIMARY KEY, btree (a, c) REPLICA IDENTITY
+Publications:
+    "testpub_both_filters" (a, c) WHERE (c <> 1)
+
+RESET client_min_messages;
 DROP TABLE testpub_tbl_both_filters;
 DROP PUBLICATION testpub_both_filters;
 -- ======================================================
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 00390aecd47..06e343d523b 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -320,6 +320,20 @@ CREATE TABLE testpub_rf_tbl7 (id int PRIMARY KEY, x int, y int GENERATED ALWAYS
 CREATE PUBLICATION testpub8 FOR TABLE testpub_rf_tbl7 WHERE (y > 100);
 ALTER TABLE testpub_rf_tbl7 ALTER COLUMN y SET EXPRESSION AS (x * testpub_rf_func2());
 RESET client_min_messages;
+-- test that ALTER COLUMN ALTER [ SET DATA ] TYPE is rejected when the column is in a row filter
+CREATE TABLE testpub_rf_tbl8 (id int PRIMARY KEY, x int, y int, z int);
+CREATE PUBLICATION testpub9 FOR TABLE testpub_rf_tbl8 WHERE (x > 100 AND y < 200);
+SET client_min_messages = 'ERROR';
+-- fail
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN x TYPE bigint;
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN y TYPE bigint;
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN y TYPE bigint, ALTER COLUMN z TYPE bigint;
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN z TYPE bigint, ALTER COLUMN x TYPE bigint;
+\d testpub_rf_tbl8
+-- ok
+ALTER TABLE testpub_rf_tbl8 ALTER COLUMN z TYPE bigint;
+\d testpub_rf_tbl8
+RESET client_min_messages;
 
 DROP TABLE testpub_rf_tbl1;
 DROP TABLE testpub_rf_tbl2;
@@ -339,6 +353,8 @@ DROP OPERATOR =#>(integer, integer);
 DROP FUNCTION testpub_rf_func1(integer, integer);
 DROP FUNCTION testpub_rf_func2();
 DROP COLLATION user_collation;
+DROP PUBLICATION testpub9;
+DROP TABLE testpub_rf_tbl8;
 
 -- ======================================================
 -- More row filter tests for validating column references
@@ -708,6 +724,14 @@ ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c)
 \dRp+ testpub_both_filters
 \d+ testpub_tbl_both_filters
 
+SET client_min_messages = 'ERROR';
+-- fail - ALTER TABLE ALTER COLUMN [ SET DATA ] TYPE affecting column in row filter and column list
+ALTER TABLE testpub_tbl_both_filters ALTER COLUMN c TYPE bigint;
+-- ok - ALTER TABLE ALTER COLUMN ALTER [ SET DATA ] TYPE affecting column in column list and not in row filter
+ALTER TABLE testpub_tbl_both_filters ALTER COLUMN a TYPE text;
+\d testpub_tbl_both_filters
+RESET client_min_messages;
+
 DROP TABLE testpub_tbl_both_filters;
 DROP PUBLICATION testpub_both_filters;
 -- ======================================================
-- 
2.50.1 (Apple Git-155)