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

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