On Wed, Aug 2, 2023 at 9:16 PM jian he <jian.universal...@gmail.com> wrote:
> On Wed, Aug 2, 2023 at 6:36 PM Amul Sul <sula...@gmail.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
