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 of generated 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 expression as (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 reasonable operation. If the new expression doesn’t work, then the entire command will fail. This is similar to run the 3 statements 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, "set expression”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/
