Re: support ALTER TABLE DROP EXPRESSION for virtual generated column

On Wed, Mar 26, 2025 at 8:15 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> jian he <jian(dot)universality(at)gmail(dot)com> writes:
> > the attached patch is to implement $subject.
>
> Why would this be a good idea? I don't see any principled fallback
> definition of the column. (No, "NULL" is not that.) Certainly we
> should support ALTER TABLE DROP COLUMN, but removing the expression
> and not providing a substitute seems semantically nonsensical.
>

The fallback value being proposed is the result of evaluating the
about-to-be-dropped expression.

We already allow removing a generated expression from a column so it cannot
be that nonsensical.

In either case we are saying the value of this column for a given row is
X. If you "select col from tbl where id = n" you will get "X".
Whether X is:
Physical
Stored Generated
Virtual Generated
is immaterial.

Physical - Physical: N/A
Physical - Stored: Disallowed (syntax but doesn't work)
Physical - Virtual: Disallowed (no syntax)

Stored - Physical: Drop Expression (no table rewrite)
Stored - Stored: Set Expression As (table rewrite)
Stored - Virtual: Disallowed (no syntax)

*Virtual - Physical: Prohibited; Proposal: Drop Expression (table rewrite)*
Virtual - Stored: Disallowed (no syntax)
Virtual - Virtual: Set Expression As (no table rewrite)

In short, the following returns '1id' today.
create table tbl (id serial primary key,
val text not null generated always as (id || 'id') stored);
insert into tbl values (default, default);
alter table tbl alter val drop expression;
select * from tbl;

This otherwise identical sequence (just using virtual) returns "not
implemented", and this proposal means to implement it.
create table tbl (id serial primary key,
val text not null generated always as (id || 'id') virtual);
insert into tbl values (default, default);
alter table tbl alter val drop expression;
select * from tbl; -- would return '1id' under the proposal (not tested...)

The reference to 'NULL' is because the physical table has no stored value
of '1id' and so we need a table rewrite to populate it.

David J.