hi.
bug demo:
drop table if exists gtest25;
CREATE TABLE gtest25 (a0 int, a int, b int GENERATED ALWAYS AS (a * 2
+ a0) STORED);
alter table gtest25 add constraint cc check (b > 0);
alter table gtest25 alter column b set data type int8, ALTER COLUMN b
SET EXPRESSION AS (a * 3 + a0);
ERROR: cache lookup failed for constraint 18432
ALTER COLUMN SET EXPRESSION only in 17, so it's a 17 and up related bug.
The reason is ATRewriteCatalogs->ATPostAlterTypeCleanup is called
twice for the same relation.
the second time you call it, the constraint cc is already dropped,
then the "cache lookup failed" error will happen.
While at it, maybe we can also polish the comment below in ATRewriteCatalogs.
/*
* After the ALTER TYPE or SET EXPRESSION pass, do cleanup work
* (this is not done in ATExecAlterColumnType since it should be
* done only once if multiple columns of a table are altered).
*/
but I didn't do it...
From 452f38f84fb61b08e200c7bd0d96663701df299c Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 27 Sep 2025 17:46:45 +0800
Subject: [PATCH v1 1/1] ALTER TABLE each relation call ATPostAlterTypeCleanup
only once
bug demo:
drop table if exists gtest25;
CREATE TABLE gtest25 (a0 int, a int, b int GENERATED ALWAYS AS (a * 2 + a0) STORED);
alter table gtest25 add constraint cc check (b > 0);
alter table gtest25 alter column b set data type int8, ALTER COLUMN b SET EXPRESSION AS (a * 3 + a0);
ERROR: cache lookup failed for constraint 18432
The reason it can only call once is:
ATPostAlterTypeCleanup searches the system catalog for recreate objects such as
STATISTICS, CONSTRAINTS. After that, the original object will be dropped via
performMultipleDeletions.
If ATPostAlterTypeCleanup is called a second time, the catalog cache lookup will
fail because those objects were already removed during the first call.
discussion: https://postgr.es/m/
---
src/backend/commands/tablecmds.c | 9 ++++++++-
src/test/regress/expected/generated_stored.out | 2 ++
src/test/regress/expected/generated_virtual.out | 2 ++
src/test/regress/sql/generated_stored.sql | 2 ++
src/test/regress/sql/generated_virtual.sql | 2 ++
5 files changed, 16 insertions(+), 1 deletion(-)
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 3be2e051d32..39b32af4b7e 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -5296,6 +5296,7 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
AlterTableUtilityContext *context)
{
ListCell *ltab;
+ List *relids = NIL;
/*
* We process all the tables "in parallel", one pass at a time. This is
@@ -5334,7 +5335,13 @@ ATRewriteCatalogs(List **wqueue, LOCKMODE lockmode,
* done only once if multiple columns of a table are altered).
*/
if (pass == AT_PASS_ALTER_TYPE || pass == AT_PASS_SET_EXPRESSION)
- ATPostAlterTypeCleanup(wqueue, tab, lockmode);
+ {
+ if (!list_member_oid(relids, tab->relid))
+ {
+ ATPostAlterTypeCleanup(wqueue, tab, lockmode);
+ relids = lappend_oid(relids, tab->relid);
+ }
+ }
if (tab->rel)
{
diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out
index adac2cedfb2..b0bc33576ca 100644
--- a/src/test/regress/expected/generated_stored.out
+++ b/src/test/regress/expected/generated_stored.out
@@ -1120,6 +1120,8 @@ SELECT * FROM gtest25 ORDER BY a;
Indexes:
"gtest25_pkey" PRIMARY KEY, btree (a)
+ALTER TABLE gtest25 ADD CONSTRAINT cc CHECK(b > 9) NOT VALID;
+ALTER TABLE gtest25 ALTER COLUMN b SET DATA TYPE INT8, ALTER COLUMN b SET EXPRESSION AS (a * 3); --ok
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
a int,
diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out
index d8645192351..1417369213c 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -1082,6 +1082,8 @@ SELECT * FROM gtest25 ORDER BY a;
Indexes:
"gtest25_pkey" PRIMARY KEY, btree (a)
+ALTER TABLE gtest25 ADD CONSTRAINT cc CHECK(b > 9) NOT VALID;
+ALTER TABLE gtest25 ALTER COLUMN b SET DATA TYPE INT8, ALTER COLUMN b SET EXPRESSION AS (a * 3); --ok
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
a int,
diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql
index f56fde8d4e5..e0c9ac32158 100644
--- a/src/test/regress/sql/generated_stored.sql
+++ b/src/test/regress/sql/generated_stored.sql
@@ -516,6 +516,8 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) STORED;
SELECT * FROM gtest25 ORDER BY a;
\d gtest25
+ALTER TABLE gtest25 ADD CONSTRAINT cc CHECK(b > 9) NOT VALID;
+ALTER TABLE gtest25 ALTER COLUMN b SET DATA TYPE INT8, ALTER COLUMN b SET EXPRESSION AS (a * 3); --ok
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql
index adfe88d74ae..b5d785734f4 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -559,6 +559,8 @@ ALTER TABLE gtest25 ALTER COLUMN d SET DATA TYPE float8,
ADD COLUMN y float8 GENERATED ALWAYS AS (d * 4) VIRTUAL;
SELECT * FROM gtest25 ORDER BY a;
\d gtest25
+ALTER TABLE gtest25 ADD CONSTRAINT cc CHECK(b > 9) NOT VALID;
+ALTER TABLE gtest25 ALTER COLUMN b SET DATA TYPE INT8, ALTER COLUMN b SET EXPRESSION AS (a * 3); --ok
-- ALTER TABLE ... ALTER COLUMN
CREATE TABLE gtest27 (
--
2.34.1
