On Mon, Sep 14, 2026 at 5:21 AM Bryan Green <[email protected]> wrote: > > On 8/3/2026 1:00 AM, Chao Li wrote: > > > >> On Aug 2, 2026, at 19:52, jian he <[email protected]> wrote: > >>
> With that list added, the rest of the approach looks correct to me. > Thanks. I did not notice the newly added AlteredTableInfo->changedStatisticsOwners. I have added some tests to stats_ext.sql -- jian https://www.enterprisedb.com/
From 80e40525948b1690560b004bdcaa74290cf79fc0 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Tue, 15 Sep 2026 14:45:27 +0800 Subject: [PATCH v7 1/1] Fix ALTER TABLE when ALTER TYPE and SET EXPRESSION are used together ATPostAlterTypeCleanup() is called twice when a single ALTER TABLE contains both ALTER COLUMN SET DATA TYPE and ALTER COLUMN SET EXPRESSION. The first call drops the objects listed in tab->changedConstraintOids, tab->changedIndexOids and tab->changedStatisticsOids via performMultipleDeletions(), but left those lists untouched. The second call would drop the same OIDs again, failing with errors like "cache lookup failed". Fix by resetting the changed-object lists (and the replica identity and CLUSTER index markings, which would otherwise queue duplicate subcommands) at the end of ATPostAlterTypeCleanup(), so the second invocation only processes objects registered by the SET EXPRESSION pass. Oversight in 5d06e99a3, so backpatch to v17. Author: Jian He <[email protected]> Reviewed-by: Chao Li <[email protected]> Reviewed-by: Bryan Green <[email protected]> Reviewed-by: David Rowley <[email protected]> Discussion: https://postgr.es/m/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com --- src/backend/commands/tablecmds.c | 23 +++++++++++++ .../regress/expected/generated_stored.out | 34 +++++++++++++++++++ .../regress/expected/generated_virtual.out | 34 +++++++++++++++++++ src/test/regress/expected/stats_ext.out | 20 ++++++++++- src/test/regress/sql/generated_stored.sql | 15 ++++++++ src/test/regress/sql/generated_virtual.sql | 14 ++++++++ src/test/regress/sql/stats_ext.sql | 14 +++++++- 7 files changed, 152 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 2f073ddb84a..9411492b725 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -16270,6 +16270,29 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode) free_object_addresses(objects); + /* + * This function may be called a second time (once after the ALTER TYPE + * pass, again after the SET EXPRESSION pass). Reset the lists so objects + * already dropped and re-queued here aren't processed again. + */ + list_free(tab->changedConstraintOids); + list_free(tab->changedConstraintDefs); + list_free(tab->changedIndexOids); + list_free(tab->changedIndexDefs); + list_free(tab->changedStatisticsOids); + list_free(tab->changedStatisticsDefs); + list_free(tab->changedStatisticsOwners); + + tab->changedConstraintOids = NIL; + tab->changedConstraintDefs = NIL; + tab->changedIndexOids = NIL; + tab->changedIndexDefs = NIL; + tab->changedStatisticsOids = NIL; + tab->changedStatisticsDefs = NIL; + tab->changedStatisticsOwners = NIL; + tab->replicaIdentityIndex = NULL; + tab->clusterOnIndex = NULL; + /* * The objects will get recreated during subsequent passes over the work * queue. diff --git a/src/test/regress/expected/generated_stored.out b/src/test/regress/expected/generated_stored.out index fd6caf1cf2d..750b0259994 100644 --- a/src/test/regress/expected/generated_stored.out +++ b/src/test/regress/expected/generated_stored.out @@ -1235,6 +1235,40 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +ALTER TABLE gtest25 + ADD COLUMN z INT DEFAULT 11, + ADD CONSTRAINT cc CHECK(b > 9) NOT VALID, + ADD CONSTRAINT check_z CHECK(z > 9) NOT VALID; +ALTER TABLE gtest25 + ALTER COLUMN z SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 1); -- ok +ALTER TABLE gtest25 + ALTER COLUMN b SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 2); -- ok +SELECT * FROM gtest25 ORDER BY a; + a | b | c | x | d | y | z +---+----+----+-----+-----+-----+---- + 3 | 13 | 42 | 168 | 101 | 404 | 11 + 4 | 13 | 42 | 168 | 101 | 404 | 11 +(2 rows) + +\d gtest25 + Table "generated_stored_tests.gtest25" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+------------------------------------------------------ + a | integer | | not null | + b | numeric | | | generated always as (z + 2::numeric) stored + c | integer | | | 42 + x | integer | | | generated always as (c * 4) stored + d | double precision | | | 101 + y | double precision | | | generated always as (d * 4::double precision) stored + z | numeric | | | 11 +Indexes: + "gtest25_pkey" PRIMARY KEY, btree (a) +Check constraints: + "cc" CHECK (b > 9::numeric) NOT VALID + "check_z" CHECK (z > 9::numeric) NOT VALID + -- 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 6ee029796f1..ca995b296da 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -1184,6 +1184,40 @@ SELECT * FROM gtest25 ORDER BY a; Indexes: "gtest25_pkey" PRIMARY KEY, btree (a) +ALTER TABLE gtest25 + ADD COLUMN z INT DEFAULT 11, + ADD CONSTRAINT cc CHECK(b > 9) NOT VALID, + ADD CONSTRAINT check_z CHECK(z > 9) NOT VALID; +ALTER TABLE gtest25 + ALTER COLUMN z SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 1); -- ok +ALTER TABLE gtest25 + ALTER COLUMN b SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 2); -- ok +SELECT * FROM gtest25 ORDER BY a; + a | b | c | x | d | y | z +---+----+----+-----+-----+-----+---- + 3 | 13 | 42 | 168 | 101 | 404 | 11 + 4 | 13 | 42 | 168 | 101 | 404 | 11 +(2 rows) + +\d gtest25 + Table "generated_virtual_tests.gtest25" + Column | Type | Collation | Nullable | Default +--------+------------------+-----------+----------+----------------------------------------------- + a | integer | | not null | + b | numeric | | | generated always as (z + 2::numeric) + c | integer | | | 42 + x | integer | | | generated always as (c * 4) + d | double precision | | | 101 + y | double precision | | | generated always as (d * 4::double precision) + z | numeric | | | 11 +Indexes: + "gtest25_pkey" PRIMARY KEY, btree (a) +Check constraints: + "cc" CHECK (b > 9::numeric) NOT VALID + "check_z" CHECK (z > 9::numeric) NOT VALID + -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( a int, diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out index 4512670d525..8db98597364 100644 --- a/src/test/regress/expected/stats_ext.out +++ b/src/test/regress/expected/stats_ext.out @@ -3585,10 +3585,16 @@ DROP USER regress_stats_user1; -- CREATE STATISTICS checks for the owner CREATE ROLE regress_relowner; CREATE ROLE regress_stxowner; -CREATE TABLE stats_ext_tbl (a int, b int); +CREATE ROLE regress_stxowner1; +CREATE TABLE stats_ext_tbl ( + a int, b int, c int, + d int generated always as (c + 1) virtual, + e int generated always as (c + 1) stored); ALTER TABLE stats_ext_tbl OWNER TO regress_relowner; CREATE STATISTICS tst ON a, b FROM stats_ext_tbl; +CREATE STATISTICS tst1 ON a, c, d FROM stats_ext_tbl; ALTER STATISTICS tst OWNER TO regress_stxowner; +ALTER STATISTICS tst1 OWNER TO regress_stxowner1; SELECT stxowner::regrole FROM pg_statistic_ext WHERE stxname = 'tst'; stxowner ------------------ @@ -3603,10 +3609,22 @@ SELECT stxowner::regrole FROM pg_statistic_ext WHERE stxname = 'tst'; regress_stxowner (1 row) +ALTER TABLE stats_ext_tbl + ALTER COLUMN b TYPE int, + ALTER COLUMN d SET EXPRESSION AS (c + 2), + ALTER COLUMN e SET EXPRESSION AS (c + 2); +SELECT stxname, stxowner::regrole FROM pg_statistic_ext WHERE stxname IN ('tst', 'tst1'); + stxname | stxowner +---------+------------------- + tst | regress_stxowner + tst1 | regress_stxowner1 +(2 rows) + -- Tidy up DROP TABLE stats_ext_tbl; DROP ROLE regress_relowner; DROP ROLE regress_stxowner; +DROP ROLE regress_stxowner1; CREATE TABLE grouping_unique (x integer); INSERT INTO grouping_unique (x) SELECT gs FROM generate_series(1,1000) AS gs; ANALYZE grouping_unique; diff --git a/src/test/regress/sql/generated_stored.sql b/src/test/regress/sql/generated_stored.sql index 235bc28db81..ad1b3827edb 100644 --- a/src/test/regress/sql/generated_stored.sql +++ b/src/test/regress/sql/generated_stored.sql @@ -598,6 +598,21 @@ 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 COLUMN z INT DEFAULT 11, + ADD CONSTRAINT cc CHECK(b > 9) NOT VALID, + ADD CONSTRAINT check_z CHECK(z > 9) NOT VALID; + +ALTER TABLE gtest25 + ALTER COLUMN z SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 1); -- ok + +ALTER TABLE gtest25 + ALTER COLUMN b SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 2); -- ok +SELECT * FROM gtest25 ORDER BY a; +\d gtest25 + -- 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 e4ea63bb3a1..35dd7d952c7 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -613,6 +613,20 @@ 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 COLUMN z INT DEFAULT 11, + ADD CONSTRAINT cc CHECK(b > 9) NOT VALID, + ADD CONSTRAINT check_z CHECK(z > 9) NOT VALID; + +ALTER TABLE gtest25 + ALTER COLUMN z SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 1); -- ok + +ALTER TABLE gtest25 + ALTER COLUMN b SET DATA TYPE numeric, + ALTER COLUMN b SET EXPRESSION AS (z + 2); -- ok +SELECT * FROM gtest25 ORDER BY a; +\d gtest25 -- ALTER TABLE ... ALTER COLUMN CREATE TABLE gtest27 ( diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql index a9176e74f3c..5dc408f43b9 100644 --- a/src/test/regress/sql/stats_ext.sql +++ b/src/test/regress/sql/stats_ext.sql @@ -1835,20 +1835,32 @@ DROP USER regress_stats_user1; -- CREATE STATISTICS checks for the owner CREATE ROLE regress_relowner; CREATE ROLE regress_stxowner; -CREATE TABLE stats_ext_tbl (a int, b int); +CREATE ROLE regress_stxowner1; +CREATE TABLE stats_ext_tbl ( + a int, b int, c int, + d int generated always as (c + 1) virtual, + e int generated always as (c + 1) stored); ALTER TABLE stats_ext_tbl OWNER TO regress_relowner; CREATE STATISTICS tst ON a, b FROM stats_ext_tbl; +CREATE STATISTICS tst1 ON a, c, d FROM stats_ext_tbl; ALTER STATISTICS tst OWNER TO regress_stxowner; +ALTER STATISTICS tst1 OWNER TO regress_stxowner1; SELECT stxowner::regrole FROM pg_statistic_ext WHERE stxname = 'tst'; -- re-creating statistics via ALTER TABLE preserve the statistics owner. ALTER TABLE stats_ext_tbl ALTER COLUMN a TYPE bigint; SELECT stxowner::regrole FROM pg_statistic_ext WHERE stxname = 'tst'; +ALTER TABLE stats_ext_tbl + ALTER COLUMN b TYPE int, + ALTER COLUMN d SET EXPRESSION AS (c + 2), + ALTER COLUMN e SET EXPRESSION AS (c + 2); +SELECT stxname, stxowner::regrole FROM pg_statistic_ext WHERE stxname IN ('tst', 'tst1'); -- Tidy up DROP TABLE stats_ext_tbl; DROP ROLE regress_relowner; DROP ROLE regress_stxowner; +DROP ROLE regress_stxowner1; CREATE TABLE grouping_unique (x integer); INSERT INTO grouping_unique (x) SELECT gs FROM generate_series(1,1000) AS gs; -- 2.34.1
