Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression

On Wed, Aug 2, 2023 at 9:16 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> On Wed, Aug 2, 2023 at 6:36 PM Amul Sul <sulamul(at)gmail(dot)com> wrote:
> >
> > Hi,
> >
> > Currently, we have an option to drop the expression of stored generated
> columns
> > as:
> >
> > ALTER [ COLUMN ] column_name DROP EXPRESSION [ IF EXISTS ]
> >
> > But don't have support to update that expression. The attached patch
> provides
> > that as:
> >
> > ALTER [ COLUMN ] column_name SET EXPRESSION expression
> >
> > Note that this form of ALTER is meant to work for the column which is
> already
> > generated. It then changes the generation expression in the catalog and
> rewrite
> > the table, using the existing table rewrite facilities for ALTER TABLE.
> > Otherwise, an error will be reported.
> >
> > To keep the code flow simple, I have renamed the existing function that
> was in
> > use for DROP EXPRESSION so that it can be used for SET EXPRESSION as
> well,
> > which is a similar design as SET/DROP DEFAULT. I kept this renaming code
> > changes in a separate patch to minimize the diff in the main patch.
> >
> > Demo:
> > -- Create table
> > CREATE TABLE t1 (x int, y int GENERATED ALWAYS AS (x * 2) STORED);
> > INSERT INTO t1 VALUES(generate_series(1,3));
> >
> > -- Check the generated data
> > SELECT * FROM t1;
> > x | y
> > ---+---
> > 1 | 2
> > 2 | 4
> > 3 | 6
> > (3 rows)
> >
> > -- Alter the expression
> > ALTER TABLE t1 ALTER COLUMN y SET EXPRESSION (x * 4);
> >
> > -- Check the new data
> > SELECT * FROM t1;
> > x | y
> > ---+----
> > 1 | 4
> > 2 | 8
> > 3 | 12
> > (3 rows)
> >
> > Thank you.
> > --
> > Regards,
> > Amul Sul
> > EDB: http://www.enterprisedb.com
> -------------------------
> setup.
>
> BEGIN;
> set search_path = test;
> DROP TABLE if exists gtest_parent, gtest_child;
>
> CREATE TABLE gtest_parent (f1 date NOT NULL, f2 bigint, f3 bigint
> GENERATED ALWAYS AS (f2 * 2) STORED) PARTITION BY RANGE (f1);
>
> CREATE TABLE gtest_child PARTITION OF gtest_parent
> FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- inherits gen expr
>
> CREATE TABLE gtest_child2 PARTITION OF gtest_parent (
> f3 WITH OPTIONS GENERATED ALWAYS AS (f2 * 22) STORED -- overrides gen
> expr
> ) FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
>
> CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint
> GENERATED ALWAYS AS (f2 * 33) STORED);
> ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM
> ('2016-09-01') TO ('2016-10-01');
>
> INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
> INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 2);
> INSERT INTO gtest_parent (f1, f2) VALUES ('2016-08-15', 3);
> UPDATE gtest_parent SET f1 = f1 + 60 WHERE f2 = 1;
>
> ALTER TABLE ONLY gtest_parent ALTER COLUMN f3 SET EXPRESSION (f2 * 4);
> ALTER TABLE gtest_child ALTER COLUMN f3 SET EXPRESSION (f2 * 10);
> COMMIT;
>
> set search_path = test;
> SELECT table_name, column_name, is_generated, generation_expression
> FROM information_schema.columns
> WHERE table_name in ('gtest_child','gtest_child1',
> 'gtest_child2','gtest_child3')
> order by 1,2;
> result:
> table_name | column_name | is_generated | generation_expression
> --------------+-------------+--------------+-----------------------
> gtest_child | f1 | NEVER |
> gtest_child | f1 | NEVER |
> gtest_child | f2 | NEVER |
> gtest_child | f2 | NEVER |
> gtest_child | f3 | ALWAYS | (f2 * 2)
> gtest_child | f3 | ALWAYS | (f2 * 10)
> gtest_child2 | f1 | NEVER |
> gtest_child2 | f1 | NEVER |
> gtest_child2 | f2 | NEVER |
> gtest_child2 | f2 | NEVER |
> gtest_child2 | f3 | ALWAYS | (f2 * 22)
> gtest_child2 | f3 | ALWAYS | (f2 * 2)
> gtest_child3 | f1 | NEVER |
> gtest_child3 | f1 | NEVER |
> gtest_child3 | f2 | NEVER |
> gtest_child3 | f2 | NEVER |
> gtest_child3 | f3 | ALWAYS | (f2 * 2)
> gtest_child3 | f3 | ALWAYS | (f2 * 33)
> (18 rows)
>
> one partition, one column 2 generated expression. Is this the expected
> behavior?

That is not expected & acceptable. But, somehow, I am not able to reproduce
this behavior. Could you please retry this experiment by adding
"table_schema"
in your output query?

Thank you.

Regards,
Amul