Re: bug, ALTER TABLE call ATPostAlterTypeCleanup twice for the same relation

On Sat, 27 Sept 2025 at 21:54, jian he <jian(dot)universality(at)gmail(dot)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);
> + }
> + }

I've not studied this long enough to know what the correct fix should
be, but I have studied it long enough to know what you're proposing
isn't it.

You can't just forego the subsequent call to ATPostAlterTypeCleanup()
because you've done it during the AT_PASS_ALTER_TYPE pass as that
doesn't account for the fact that there might be more work to do (i.e
more constraints added since AT_PASS_ALTER_TYPE) in the
AT_PASS_SET_EXPRESSION pass.

With your patch applied, if I adapt your example case to alter the
type of an unrelated column so that the constraint related to that
column is adjusted first and the constraint related to the expression
column is only added to the changedConstraintOids list after the first
call to ATPostAlterTypeCleanup(), you'll see that "cc" isn't recreated
because your code thinks nothing further needs to be done:

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.

David