Re: support ALTER TABLE DROP EXPRESSION for virtual generated column
| От | David G. Johnston |
|---|---|
| Тема | Re: support ALTER TABLE DROP EXPRESSION for virtual generated column |
| Дата | |
| Msg-id | CAKFQuwbyOfLqHi0Z9+j+84tVNSO1JOgJnV3gp2XpHkyxGgPt_A@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: support ALTER TABLE DROP EXPRESSION for virtual generated column (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-hackers |
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);
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);
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.
В списке pgsql-hackers по дате отправления: