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