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

On Wed, Aug 2, 2023 at 6:36 PM Amul Sul <sulamul@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?