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?

In the regress, you can replace  \d table_name to sql query (similar
to above) to get the generated expression meta data.
since here you want the meta data to be correct. then one select query
to valid generated expression behaviored sane or not.