Re: support create index on virtual generated column.

On Wed, 26 Mar 2025 at 12:15, jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> hi.
> attached patch for implementing $subject feature.
>
> * internally such index will be transformed into expression index.
> for example, an index on (b int GENERATED ALWAYS AS (a * 2) VIRTUAL) will be
> converted into an expression index on ((a * 2)).
> * in pageinspect module, add some test to check the index content of
> virtual generated column.
> * primary key, unique index over virtual generated column are not supported.
> not sure they make sense or not.
> * expression index and predicate index over virtual generated columns are
> currently not supported.
> * virtual generated column can not be in "include column"
> * all types of indexes are supported, and a hash index, gist test has
> been added.
> * To support ALTER TABLE SET EXPRESSION, in pg_index, we need to track
> the original
> virtual generated column attribute number, so ALTER TABLE SET EXPRESSION can
> identify which index needs to be rebuilt.
> * ALTER COLUMN SET DATA TYPE will also cause table rewrite, so we really
> need to track the virtual generated column attribute number that
> index was built on.

Hi!
patch applies with warns
```
Applying: support create index on virtual generated column.
.git/rebase-apply/patch:250: trailing whitespace.
* updated correctly, and they don't seem useful anyway.
.git/rebase-apply/patch:271: trailing whitespace.
* Also check for system used in expressions or predicates.
warning: 2 lines add whitespace errors.
```

consider this case:

```

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

--
Best regards,
Kirill Reshke