Hi,
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. The code that raises this error was added in commit 5f4a1a0a [1]
ALTER COLUMN TYPE should have no adverse effects on column lists, so it
makes sense to unblock that case. This is done by looking up the
corresponding pg_publication_rel entry and fetching prqual (containing the
serialized row filter expression). In case prqual is NULL, the pg_depend
entry corresponds only to a column list and ALTER COLUMN TYPE is therefore
safe to apply.
There is, however, an edge case when a publication contains both column
lists and row filters, and the column being ALTERed is only in the former
and not in the latter. In this case, we need to actually parse prqual and
check if the column in question is present in it.
I've attached a patch that does both these checks, along with regression
tests.
Thanks,
Kevin
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=5f4a1a0a7758bb3bd0cfa58a48a1537bb2c0024b
From 049b9745069befda5d00f6680c7dc06367666814 Mon Sep 17 00:00:00 2001
From: Kevin Biju
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
