On 8/2/23 12:35, Amul Sul 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
I love this idea. It is something that the standard SQL language is
lacking and I am submitting a paper to correct that based on this. I
will know in October what the committee thinks of it. Thanks!
Note that this form of ALTER is meant to work for the column which is
already generated.
Why? SQL does not have a way to convert a non-generated column into a
generated column, and this seems like as good a way as any.
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.
I don't like this part of the patch at all. Not only is the
documentation only half baked, but the entire concept of the two
commands is different. Especially since I believe the command should
also create a generated column from a non-generated one.
Is is possible to compare the old and new expressions and no-op if they
are the same?
psql (17devel)
Type "help" for help.
postgres=# create table t (c integer generated always as (null) stored);
CREATE TABLE
postgres=# select relfilenode from pg_class where oid = 't'::regclass;
relfilenode
-------------
16384
(1 row)
postgres=# alter table t alter column c set expression (null);
ALTER TABLE
postgres=# select relfilenode from pg_class where oid = 't'::regclass;
relfilenode
-------------
16393
(1 row)
I am not saying we should make every useless case avoid rewriting the
table, but if there are simple wins, we should take them. (I don't know
how feasible this is.)
I think repeating the STORED keyword should be required here to
future-proof virtual generated columns.
Consider this hypothetical example:
CREATE TABLE t (c INTEGER);
ALTER TABLE t ALTER COLUMN c SET EXPRESSION (42) STORED;
ALTER TABLE t ALTER COLUMN c SET EXPRESSION VIRTUAL;
If we don't require the STORED keyword on the second command, it becomes
ambiguous. If we then decide that VIRTUAL should be the default, we
will break people's scripts.
--
Vik Fearing
