Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression - Mailing list pgsql-hackers
From | Amul Sul |
---|---|
Subject | Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression |
Date | |
Msg-id | CAAJ_b97ut=ib-gmoFLq4BzG+JOuqapbRVTNX-FSy14Jpigq39A@mail.gmail.com Whole thread Raw |
In response to | Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression (jian he <jian.universality@gmail.com>) |
Responses |
Re: ALTER COLUMN ... SET EXPRESSION to alter stored generated column's expression
|
List | pgsql-hackers |
On Wed, Aug 2, 2023 at 9:16 PM jian he <jian.universality@gmail.com> wrote:
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?
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?
this behavior. Could you please retry this experiment by adding "table_schema"
in your output query?
Thank you.
Regards,
Amul
pgsql-hackers by date: