Thread: 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>
Attachment
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 anequivalent stored expression, then drop the expression. > This discussion [0] 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] 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]: https://postgr.es/m/CAFCRh-8grTkEy%2B73q79iXB%2Bq%2BsD%3DQkbz-vNAN1KJCF6PRLa%3Dzg%40mail.gmail.com [1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=cdc168ad4b22ea4183f966688b245cabb5935d1f