On Wed, Mar 26, 2025 at 5:36 PM Kirill Reshke <reshkekirill@gmail.com> wrote:
> reshke=# CREATE TABLE xx (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL) ;
> CREATE TABLE
> reshke=# create index on xx (b);
> CREATE INDEX
> reshke=#
> reshke=# \d+ xx
> Table "public.xx"
> Column | Type | Collation | Nullable | Default
> | Storage | Compression | Stats target | Description
>
--------+---------+-----------+----------+-----------------------------+---------+-------------+--------------+-------------
> a | integer | | |
> | plain | | |
> b | integer | | | generated always as (a * 2)
> | plain | | |
> Indexes:
> "xx_b_idx" btree (b)
> Access method: heap
>
> reshke=# alter table xx drop column b;
> ALTER TABLE
> reshke=# \d+ xx
> Table "public.xx"
> Column | Type | Collation | Nullable | Default | Storage |
> Compression | Stats target | Description
> --------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
> a | integer | | | | plain |
> | |
> Indexes:
> "xx_b_idx" btree ("........pg.dropped.2........" int4_ops)
> Access method: heap
>
> reshke=#
> ```
>
> with regular columns we have different behaviour - with drop column we
> drop the index
>
I was wrong about dependency.
when creating an index on a virtual generated column, it will have
dependency with
virtual generated column attribute and the generation expression
associated attribute.
new patch attached. Now,
ALTER TABLE DROP COLUMN works fine.
ALTER INDEX ATTACH PARTITION works fine.
creating such an index on a partitioned table works just fine.
for table inheritance: create index on parent table will not cascade
to child table,
so we don't need to worry about this.
