Обсуждение: Why cannot alter a column's type when it's used by a generated column

Hi Hackers,

I noticed this problem while percolating in the other discussion.

```
evantest=# create table abc (a int, b int generated always as (a+c) stored, c int);
CREATE TABLE
evantest=# insert into abc (a, c) values (1, 2);
INSERT 0 1
evantest=# select * from abc;
 a | b | c
---+---+---
 1 | 3 | 2
(1 row)
evantest=# alter table abc 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".
```

I understand that is to maintain data correctness and dependency integrity. Change a’s type might break the expression
ofgenerated column b. 

Now, if I have to change the column type, I have to execute 3 statements:
* drop the generated column
* alter the column’s type
* create the generated column again

Which is inconvenient.

But look at this SQL:
```
evantest=# alter table abc alter column a set data type bigint, alter column b set data type bigint, alter column b set
expressionas (a*c), alter column c 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”.
```

If I explicitly update all columns’ type and explicitly set expression of b in the same command, which looks a
reasonableoperation. If the new expression doesn’t work, then the entire command will fail. This is similar to run the
3statements in the same transaction. 

So I think it would be reasonable to support that, when updating a column’s type that is used by generated columns,
"setexpression”s for all related generated columns explicitly present, then the "alter column type" should be allowed. 

Before proposing a patch, I would to like hear what hackers think about that.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Fri, Oct 17, 2025 at 10:04 AM Chao Li <li.evan.chao@gmail.com> wrote:
> ```
> evantest=# create table abc (a int, b int generated always as (a+c) stored, c int);
> CREATE TABLE
> evantest=# insert into abc (a, c) values (1, 2);
> INSERT 0 1
> evantest=# select * from abc;
>  a | b | c
> ---+---+---
>  1 | 3 | 2
> (1 row)
> evantest=# alter table abc 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".
> ```
>
> ...
>
> Before proposing a patch, I would to like hear what hackers think about that.
>
in RememberAllDependentForRebuilding

                        /*
                         * This must be a reference from the expression of a
                         * generated column elsewhere in the same table.
                         * Changing the type/generated expression of a column
                         * that is used by a generated column is not allowed
                         * by SQL standard, so just punt for now.  It might be
                         * doable with some thinking and effort.
                         */
                        if (subtype == AT_AlterColumnType)
                            ereport(ERROR,
                                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                                     errmsg("cannot alter type of a
column used by a generated column"),
                                     errdetail("Column \"%s\" is used
by generated column \"%s\".",
                                               colName,
                                               get_attname(col.objectId,
                                                           col.objectSubId,
                                                           false))));

The error code is ERRCODE_FEATURE_NOT_SUPPORTED.
the above comment said "It might be doable with some thinking and effort."

The attached patch removes this restriction.
it need more polish, but it's good enough to use it to verify the bug I reported
on
https://postgr.es/m/CACJufxHZsgn3zM5g-x7YmtFGzNDnRwR07S+GYfiUs+tZ45MDDw@mail.gmail.com

On Tue, Oct 21, 2025 at 3:03 PM Chao Li <li.evan.chao@gmail.com> wrote:
>
> Here I am just proposing a patch for a small step. When any dependent generated column has SET EXPRESSION, then we
canallow the column type change: 
>
> ```
>     -- Before this patch, the recommended usage was:
>     ALTER TABLE gtest
>       DROP COLUMN x,
>       ALTER COLUMN a TYPE float8,
>       ADD COLUMN x bigint GENERATED ALWAYS AS ((a::int + b) * 2) STORED;
>
>     -- With this patch, the statement is simplified as:
>     ALTER TABLE gtest
>       ALTER COLUMN a TYPE float8,
>       ALTER COLUMN x SET EXPRESSION ((a::int + b) * 2);
> ```
hi.

please feel free to bump the attached patch version.

+-- So, you can change a column's type as long as any dependent generated
+-- column already has a set expression defined:
+ALTER TABLE gtest27
+  ALTER COLUMN a TYPE float8,
+  ALTER COLUMN x SET EXPRESSION AS ((a::int + b) * 2);
+\d gtest27
+                                Table "generated_stored_tests.gtest27"
+ Column |       Type       | Collation | Nullable |
   Default
+--------+------------------+-----------+----------+---------------------------------------------------
+ a      | double precision |           |          |
+ b      | bigint           |           |          |
+ x      | bigint           |           |          | generated always
as ((a::integer + b) * 2) stored
+

the above output seems wrong?
one way to quickly test it is create table  gtest27 again
(
create table gtest27(a double precision, b bigint, c bigint GENERATED
ALWAYS as ((a +b) * 2) stored);
)
and the result of "\d gtest27" is

 Column |       Type       | Collation | Nullable |
                Default

--------+------------------+-----------+----------+--------------------------------------------------------------------------------
 a      | double precision |           |          |
 b      | bigint           |           |          |
 c      | bigint           |           |          | generated always
as (((a + b::double precision) * 2::double precision)) stored

which conflicts with your changes.

> On Oct 21, 2025, at 15:30, jian he <jian.universality@gmail.com> wrote:
>
> +-- So, you can change a column's type as long as any dependent generated
> +-- column already has a set expression defined:
> +ALTER TABLE gtest27
> +  ALTER COLUMN a TYPE float8,
> +  ALTER COLUMN x SET EXPRESSION AS ((a::int + b) * 2);
> +\d gtest27
> +                                Table "generated_stored_tests.gtest27"
> + Column |       Type       | Collation | Nullable |
>   Default
> +--------+------------------+-----------+----------+---------------------------------------------------
> + a      | double precision |           |          |
> + b      | bigint           |           |          |
> + x      | bigint           |           |          | generated always
> as ((a::integer + b) * 2) stored
> +
>
> the above output seems wrong?
> one way to quickly test it is create table  gtest27 again

I don’t get what is wrong? Actually “make check” passed from my side:

```
% make check
…
1..230
# All 230 tests passed.
```

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

On Tue, Oct 21, 2025 at 3:56 PM Chao Li <li.evan.chao@gmail.com> wrote:

> > On Oct 21, 2025, at 15:30, jian he <jian.universality@gmail.com> wrote:
> >
> > +-- So, you can change a column's type as long as any dependent generated
> > +-- column already has a set expression defined:
> > +ALTER TABLE gtest27
> > +  ALTER COLUMN a TYPE float8,
> > +  ALTER COLUMN x SET EXPRESSION AS ((a::int + b) * 2);
> > +\d gtest27
> > +                                Table "generated_stored_tests.gtest27"
> > + Column |       Type       | Collation | Nullable |
> >   Default
> > +--------+------------------+-----------+----------+---------------------------------------------------
> > + a      | double precision |           |          |
> > + b      | bigint           |           |          |
> > + x      | bigint           |           |          | generated always
> > as ((a::integer + b) * 2) stored
> > +
> >
> > the above output seems wrong?
> > one way to quickly test it is create table  gtest27 again
>
> I don’t get what is wrong? Actually “make check” passed from my side:
>

Sorry for the noise, I misunderstood something.
I think I understand what your patch does now.

I’ll polish my patch and submit it in a new thread.