Re: bug, ALTER TABLE call ATPostAlterTypeCleanup twice for the same relation - Mailing list pgsql-hackers

On Sep 29, 2025, at 05:36, David Rowley <dgrowleyml@gmail.com> wrote:

On Sat, 27 Sept 2025 at 21:54, jian he <jian.universality@gmail.com> wrote:

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);
+ }
+ }

drop table if exists gtest25;
CREATE TABLE gtest25 (a0 int, z int, a int, b int GENERATED ALWAYS AS
(a * 2 + a0) STORED);
alter table gtest25 add constraint check_z check (z > 0);
alter table gtest25 add constraint cc check (b > 0);

select oid, conname,conbin from pg_constraint where
conrelid='gtest25'::regclass;

alter table gtest25  alter column z set data type numeric, ALTER
COLUMN b SET EXPRESSION AS (z + 0);
select oid, conname,conbin from pg_constraint where
conrelid='gtest25'::regclass;

and that's certainly wrong as if I separate the ALTER TABLE into two
separate commands, then "cc" does get recreated.

I do wonder what the exact reason was that AT_PASS_ALTER_TYPE and
AT_PASS_SET_EXPRESSION are separate passes. I'd have expected that's
maybe so that it's possible to alter the type of a column used within
a generated column, but looking at the following error message makes
me think I might not be correct in that thinking:

create table test1 (a int, b int generated always as (a + 1) stored);
alter table test1 alter column a set data type bigint;
ERROR:  cannot alter type of a column used by a generated column
DETAIL:  Column "a" is used by generated column "b".

There's probably some good explanation for the separate pass, but I'm
not sure of it for now. I'm including in the author and committer of
the patch which added this code to see if we can figure this out.