Why cannot alter a column's type when it's used by a generated column - Mailing list pgsql-hackers

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/