support ALTER TABLE DROP EXPRESSION for virtual generated column
hi.
the attached patch is to implement $subject.
the generation expression will be dropped.
the column value previous was NULL will be materialized
based on generation expression.
It seems fairly straightforward:
drop the generation expression in ATExecDropExpression,
and instruct phase 3 to compute the generation expression
and do the table rewrite.
the doc changes:
<para>
- This form turns a stored generated column into a normal base column.
- Existing data in the columns is retained, but future changes will no
- longer apply the generation expression.
- </para>
-
- <para>
- This form is currently only supported for stored generated columns (not
- virtual ones).
+ This form turns a generated column into a normal base column.
+ For stored generated column, existing data in the columns is retained;
+ For virtual generated column, it will compute the generation
expression and
+ store the value in the columns. For inheritance hierarchy or
partition hierarchy,
+ the virtual generation expression is computed based on the
child's own generation expression.
+ The future changes will no longer apply the generation expression.
</para>Attachments:
v1-0001-support-ALTER-TABLE-DROP-EXPRESSION-for-virtual-g.patchtext/x-patch; charset=US-ASCII; name=v1-0001-support-ALTER-TABLE-DROP-EXPRESSION-for-virtual-g.patchDownload+114-46
jian he <jian.universality@gmail.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.
regards, tom lane
On Wednesday, March 26, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
jian he <jian.universality@gmail.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.
I don’t follow how NULL got involved in this discussion. The proposal is
basically: turn the virtual expression into an equivalent stored
expression, then drop the expression.
I suppose it would make sense to first add an alter table command to allow
the user to do a virtual/stored mode swap manually before adding this,
which then just becomes a convenient way to specify swap-and-drop as a
single command.
David J.
On Thu, Mar 27, 2025 at 11:44 AM David G. Johnston
<david.g.johnston@gmail.com> wrote:
On Wednesday, March 26, 2025, Tom Lane <tgl@sss.pgh.pa.us> wrote:
jian he <jian.universality@gmail.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.I don’t follow how NULL got involved in this discussion. The proposal is basically: turn the virtual expression into an equivalent stored expression, then drop the expression.
This discussion [0]/messages/by-id/CAFCRh-8grTkEy+73q79iXB+q+sD=Qkbz-vNAN1KJCF6PRLa=zg@mail.gmail.com wants to change stored to virtual.
drop the virtual generation, not materialize, column value will be NULL,
but that will not work because commit [1]https://git.postgresql.org/cgit/postgresql.git/commit/?id=cdc168ad4b22ea4183f966688b245cabb5935d1f
So we are either saying that
virtual generation expression cannot be dropped, you can only
substitute another expression
or drop the expression, based on the dropped expression materializing
that column value.
[0]: /messages/by-id/CAFCRh-8grTkEy+73q79iXB+q+sD=Qkbz-vNAN1KJCF6PRLa=zg@mail.gmail.com
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=cdc168ad4b22ea4183f966688b245cabb5935d1f
On Wed, Mar 26, 2025 at 8:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
jian he <jian.universality@gmail.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.