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 <[email protected]> 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)
