On Fri, Mar 13, 2026 at 10:01 PM Peter Eisentraut <[email protected]> wrote: > > I think you could do a much simpler initial version of this if you just > supported virtual generated columns in expression indexes. And then > prohibit SET EXPRESSION if the column is used in an index. Then you > don't need to worry about index rebuilding, ALTER TABLE recursion, new > catalog columns, and all that. >
CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a) VIRTUAL); CREATE INDEX gtest22c_a_idx ON gtest22c (a); CREATE INDEX gtest22c_b_idx ON gtest22c (b); If we don't add a new catalog column (just a single boolean indisvirtual is not enough, i think), how can we distinguish between the gtest22c_a_idx and gtest22c_b_idx indexes in the example above? If CREATE INDEX simply expands the virtual generated column expression without dependency tracking, that would be quite easy, see the attached v8. If so, we need to explicitly document that SET EXPRESSION has no effect on existing indexes that originally referenced the virtual generated column when CREATE INDEX was used. > But there is a comment in DefineIndex(): > > /* > * XXX Virtual generated columns in index expressions or predicates > * could be supported, but it needs support in > * RelationGetIndexExpressions() and RelationGetIndexPredicate(). > */ > > which you delete, but you don't make any changes to those mentioned > functions. Maybe the comment is wrong, in which case, let's discuss > that and fix it. (If the comment is indeed wrong, then the feature > might even be very easy.) > I don't think it's a good idea to store the virtual generated columns as is in Form_pg_index->indkey because IndexInfo->ii_IndexAttrNumbers and Form->pg_index->indkey are referenced in too many places (see BuildIndexInfo). For every single occurrence of ndkey.values[i], we need to consider whether it's ok for it be a virtual generated column. Instead, Anum_pg_index_indexprs and Anum_pg_index_indpred store the expressions after the virtual generated columns expansion, then we don't need to worry about Form_pg_index->indkey.values[i] is virtual generated column or not. Therefore, i think RelationGetIndexExpressions and RelationGetIndexPredicate don't need to deal with virtual generated column expressions at all. Overall, I think the comment above is wrong. -- jian https://www.enterprisedb.com/
From b5a053bd62a7801c607968d75337bbb378fd0f99 Mon Sep 17 00:00:00 2001 From: jian he <[email protected]> Date: Mon, 16 Mar 2026 11:28:02 +0800 Subject: [PATCH v8 1/1] index on virtual generated column discussion: https://postgr.es/m/CACJufxGao-cypdNhifHAdt8jHfK6-HX=trbovbkgruxw063...@mail.gmail.com discussion: https://postgr.es/m/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9m...@mail.gmail.com related thread: https://postgr.es/m/[email protected] commitfest: https://commitfest.postgresql.org/patch/5667 --- contrib/pageinspect/expected/btree.out | 11 ++ contrib/pageinspect/sql/btree.sql | 10 ++ src/backend/commands/indexcmds.c | 54 +++----- src/test/regress/expected/alter_table.out | 20 +++ .../regress/expected/generated_virtual.out | 131 ++++++++++++++---- src/test/regress/sql/alter_table.sql | 9 ++ src/test/regress/sql/generated_virtual.sql | 46 +++--- 7 files changed, 201 insertions(+), 80 deletions(-) diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out index 0aa5d73322f..7c4fe667d98 100644 --- a/contrib/pageinspect/expected/btree.out +++ b/contrib/pageinspect/expected/btree.out @@ -183,6 +183,17 @@ tids | SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2)); ERROR: block number 2 is out of range for relation "test1_a_idx" +--- test index over virtual generated column +CREATE TABLE test4(a int, b int GENERATED ALWAYS AS (a + 1), c text); +INSERT INTO test4(a,c) VALUES (10,11), (10,11); +CREATE INDEX test4_b_idx ON test4 USING btree (b); +CREATE INDEX test4_a_1_idx ON test4 USING btree ((a+1)); +-- expect return zero row +SELECT * FROM bt_page_items('test4_b_idx', 1) +EXCEPT ALL +SELECT * FROM bt_page_items('test4_a_1_idx', 1); +(0 rows) + -- Failure when using a non-btree index. CREATE INDEX test1_a_hash ON test1 USING hash(a); SELECT bt_metap('test1_a_hash'); diff --git a/contrib/pageinspect/sql/btree.sql b/contrib/pageinspect/sql/btree.sql index 102ebdefe3c..13e97cb6744 100644 --- a/contrib/pageinspect/sql/btree.sql +++ b/contrib/pageinspect/sql/btree.sql @@ -32,6 +32,16 @@ SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 0)); SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1)); SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 2)); +--- test index over virtual generated column +CREATE TABLE test4(a int, b int GENERATED ALWAYS AS (a + 1), c text); +INSERT INTO test4(a,c) VALUES (10,11), (10,11); +CREATE INDEX test4_b_idx ON test4 USING btree (b); +CREATE INDEX test4_a_1_idx ON test4 USING btree ((a+1)); +-- expect return zero row +SELECT * FROM bt_page_items('test4_b_idx', 1) +EXCEPT ALL +SELECT * FROM bt_page_items('test4_a_1_idx', 1); + -- Failure when using a non-btree index. CREATE INDEX test1_a_hash ON test1 USING hash(a); SELECT bt_metap('test1_a_hash'); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 635679cc1f2..0f3bbb1a390 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -53,6 +53,7 @@ #include "parser/parse_utilcmd.h" #include "partitioning/partdesc.h" #include "pgstat.h" +#include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" #include "storage/lmgr.h" #include "storage/proc.h" @@ -905,8 +906,13 @@ DefineIndex(ParseState *pstate, * Validate predicate, if given */ if (stmt->whereClause) + { CheckPredicate((Expr *) stmt->whereClause); + stmt->whereClause = + expand_generated_columns_in_expr(stmt->whereClause, rel, 1); + } + /* * Parse AM-specific options, convert to text array form, validate. */ @@ -1120,16 +1126,6 @@ DefineIndex(ParseState *pstate, ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("index creation on system columns is not supported"))); - - - if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - stmt->primary ? - errmsg("primary keys on virtual generated columns are not supported") : - stmt->isconstraint ? - errmsg("unique constraints on virtual generated columns are not supported") : - errmsg("indexes on virtual generated columns are not supported")); } /* @@ -1139,7 +1135,6 @@ DefineIndex(ParseState *pstate, if (indexInfo->ii_Expressions || indexInfo->ii_Predicate) { Bitmapset *indexattrs = NULL; - int j; pull_varattnos((Node *) indexInfo->ii_Expressions, 1, &indexattrs); pull_varattnos((Node *) indexInfo->ii_Predicate, 1, &indexattrs); @@ -1152,24 +1147,6 @@ DefineIndex(ParseState *pstate, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("index creation on system columns is not supported"))); } - - /* - * XXX Virtual generated columns in index expressions or predicates - * could be supported, but it needs support in - * RelationGetIndexExpressions() and RelationGetIndexPredicate(). - */ - j = -1; - while ((j = bms_next_member(indexattrs, j)) >= 0) - { - AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber; - - if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - stmt->isconstraint ? - errmsg("unique constraints on virtual generated columns are not supported") : - errmsg("indexes on virtual generated columns are not supported"))); - } } /* Is index safe for others to ignore? See set_indexsafe_procflags() */ @@ -1900,6 +1877,8 @@ ComputeIndexAttrs(ParseState *pstate, int nkeycols = indexInfo->ii_NumIndexKeyAttrs; Oid save_userid; int save_sec_context; + Relation rel = table_open(relId, NoLock); + Node *defexpr = NULL; /* Allocate space for exclusion operator info, if needed */ if (exclusionOpNames) @@ -1974,12 +1953,24 @@ ComputeIndexAttrs(ParseState *pstate, indexInfo->ii_IndexAttrNumbers[attn] = attform->attnum; atttype = attform->atttypid; attcollation = attform->attcollation; + + if (attform->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + defexpr = build_generation_expression(rel, attform->attnum); + ReleaseSysCache(atttuple); } - else + + if (attribute->name == NULL || defexpr != NULL) { /* Index expression */ - Node *expr = attribute->expr; + Node *expr; + + if (defexpr) + expr = defexpr; + else + expr = expand_generated_columns_in_expr(attribute->expr, rel, 1); + + defexpr = NULL; Assert(expr != NULL); @@ -2271,6 +2262,7 @@ ComputeIndexAttrs(ParseState *pstate, attn++; } + table_close(rel, NoLock); } /* diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index 5998c670aa3..92276c00211 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -119,6 +119,26 @@ HINT: Alter statistics on table column instead. ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000; ERROR: column number 4 of relation "attmp_idx" does not exist ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1; +ALTER TABLE attmp + ADD COLUMN col1 int GENERATED ALWAYS AS (a), + ADD COLUMN col2 int GENERATED ALWAYS AS (a + 1); +CREATE INDEX attmp_idx1 ON attmp (a, col1, col2); +ALTER INDEX attmp_idx1 ALTER COLUMN 1 SET STATISTICS 1000; +ERROR: cannot alter statistics on non-expression column "a" of index "attmp_idx1" +HINT: Alter statistics on table column instead. +ALTER INDEX attmp_idx1 ALTER COLUMN 2 SET STATISTICS 1000; +ERROR: cannot alter statistics on non-expression column "col1" of index "attmp_idx1" +HINT: Alter statistics on table column instead. +ALTER INDEX attmp_idx1 ALTER COLUMN 3 SET STATISTICS 1000; +\d+ attmp_idx1 + Index "public.attmp_idx1" + Column | Type | Key? | Definition | Storage | Stats target +--------+---------+------+------------+---------+-------------- + a | integer | yes | a | plain | + col1 | integer | yes | a | plain | + col2 | integer | yes | (a + 1) | plain | 1000 +btree, for table "public.attmp" + DROP TABLE attmp; -- -- rename - check on both non-temp and temp tables diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index 6dab60c937b..f99feeaf6e0 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -754,39 +754,118 @@ ERROR: column "c" of relation "gtestnn_child" contains null values ALTER TABLE gtestnn_parent ADD COLUMN c int NOT NULL GENERATED ALWAYS AS (nullif(f1, 4) + nullif(f2, 6)) VIRTUAL; -- ok -- index constraints CREATE TABLE gtest22a (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a / 2) VIRTUAL UNIQUE); -ERROR: unique constraints on virtual generated columns are not supported +ERROR: constraints cannot have index expressions --INSERT INTO gtest22a VALUES (2); --INSERT INTO gtest22a VALUES (3); --INSERT INTO gtest22a VALUES (4); CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY KEY (a, b)); -ERROR: primary keys on virtual generated columns are not supported +ERROR: primary keys cannot be expressions --INSERT INTO gtest22b VALUES (2); --INSERT INTO gtest22b VALUES (2); -- indexes CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); ---CREATE INDEX gtest22c_b_idx ON gtest22c (b); ---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); ---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; ---\d gtest22c ---INSERT INTO gtest22c VALUES (1), (2), (3); ---SET enable_seqscan TO off; ---SET enable_bitmapscan TO off; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; ---SELECT * FROM gtest22c WHERE b = 4; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; ---SELECT * FROM gtest22c WHERE b * 3 = 6; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4); ---ANALYZE gtest22c; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8; ---SELECT * FROM gtest22c WHERE b = 8; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12; ---SELECT * FROM gtest22c WHERE b * 3 = 12; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---RESET enable_seqscan; ---RESET enable_bitmapscan; +CREATE INDEX gtest22c_b_idx ON gtest22c (b); +CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); +CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; +\d gtest22c + Table "generated_virtual_tests.gtest22c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+----------------------------- + a | integer | | | + b | integer | | | generated always as (a * 2) +Indexes: + "gtest22c_b_idx" btree ((a * 2)) + "gtest22c_expr_idx" btree ((a * 2 * 3)) + "gtest22c_pred_idx" btree (a) WHERE (a * 2) > 0 + +INSERT INTO gtest22c VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; + QUERY PLAN +--------------------------------------------- + Index Scan using gtest22c_b_idx on gtest22c + Index Cond: ((a * 2) = 4) +(2 rows) + +SELECT * FROM gtest22c WHERE b = 4; + a | b +---+--- + 2 | 4 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; + QUERY PLAN +------------------------------------------------ + Index Scan using gtest22c_expr_idx on gtest22c + Index Cond: (((a * 2) * 3) = 6) +(2 rows) + +SELECT * FROM gtest22c WHERE b * 3 = 6; + a | b +---+--- + 1 | 2 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; + QUERY PLAN +----------------------------------------------------- + Index Only Scan using gtest22c_pred_idx on gtest22c + Index Cond: (a = 1) +(2 rows) + +SELECT * FROM gtest22c WHERE a = 1 AND b > 0; + a | b +---+--- + 1 | 2 +(1 row) + +ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4); +ANALYZE gtest22c; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8; + QUERY PLAN +------------------------- + Seq Scan on gtest22c + Disabled: true + Filter: ((a * 4) = 8) +(3 rows) + +SELECT * FROM gtest22c WHERE b = 8; + a | b +---+--- + 2 | 8 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12; + QUERY PLAN +-------------------------------- + Seq Scan on gtest22c + Disabled: true + Filter: (((a * 4) * 3) = 12) +(3 rows) + +SELECT * FROM gtest22c WHERE b * 3 = 12; + a | b +---+--- + 1 | 4 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; + QUERY PLAN +--------------------------------------- + Seq Scan on gtest22c + Disabled: true + Filter: ((a = 1) AND ((a * 4) > 0)) +(3 rows) + +SELECT * FROM gtest22c WHERE a = 1 AND b > 0; + a | b +---+--- + 1 | 4 +(1 row) + +RESET enable_seqscan; +RESET enable_bitmapscan; -- foreign keys CREATE TABLE gtest23a (x int PRIMARY KEY, y int); --INSERT INTO gtest23a VALUES (1, 11), (2, 22), (3, 33); @@ -804,7 +883,7 @@ ERROR: foreign key constraints on virtual generated columns are not supported --DROP TABLE gtest23b; --DROP TABLE gtest23a; CREATE TABLE gtest23p (x int, y int GENERATED ALWAYS AS (x * 2) VIRTUAL, PRIMARY KEY (y)); -ERROR: primary keys on virtual generated columns are not supported +ERROR: primary keys cannot be expressions --INSERT INTO gtest23p VALUES (1), (2), (3); CREATE TABLE gtest23q (a int PRIMARY KEY, b int REFERENCES gtest23p (y)); ERROR: relation "gtest23p" does not exist diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index d6b6381ae5c..f042264bac2 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -156,6 +156,15 @@ ALTER INDEX attmp_idx ALTER COLUMN 4 SET STATISTICS 1000; ALTER INDEX attmp_idx ALTER COLUMN 2 SET STATISTICS -1; +ALTER TABLE attmp + ADD COLUMN col1 int GENERATED ALWAYS AS (a), + ADD COLUMN col2 int GENERATED ALWAYS AS (a + 1); + +CREATE INDEX attmp_idx1 ON attmp (a, col1, col2); +ALTER INDEX attmp_idx1 ALTER COLUMN 1 SET STATISTICS 1000; +ALTER INDEX attmp_idx1 ALTER COLUMN 2 SET STATISTICS 1000; +ALTER INDEX attmp_idx1 ALTER COLUMN 3 SET STATISTICS 1000; +\d+ attmp_idx1 DROP TABLE attmp; diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index e750866d2d8..4590dc85fea 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -402,31 +402,31 @@ CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY -- indexes CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL); ---CREATE INDEX gtest22c_b_idx ON gtest22c (b); ---CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); ---CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; ---\d gtest22c +CREATE INDEX gtest22c_b_idx ON gtest22c (b); +CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); +CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; +\d gtest22c ---INSERT INTO gtest22c VALUES (1), (2), (3); ---SET enable_seqscan TO off; ---SET enable_bitmapscan TO off; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; ---SELECT * FROM gtest22c WHERE b = 4; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; ---SELECT * FROM gtest22c WHERE b * 3 = 6; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---SELECT * FROM gtest22c WHERE a = 1 AND b > 0; +INSERT INTO gtest22c VALUES (1), (2), (3); +SET enable_seqscan TO off; +SET enable_bitmapscan TO off; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4; +SELECT * FROM gtest22c WHERE b = 4; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 6; +SELECT * FROM gtest22c WHERE b * 3 = 6; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; +SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4); ---ANALYZE gtest22c; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8; ---SELECT * FROM gtest22c WHERE b = 8; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12; ---SELECT * FROM gtest22c WHERE b * 3 = 12; ---EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---SELECT * FROM gtest22c WHERE a = 1 AND b > 0; ---RESET enable_seqscan; ---RESET enable_bitmapscan; +ALTER TABLE gtest22c ALTER COLUMN b SET EXPRESSION AS (a * 4); +ANALYZE gtest22c; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 8; +SELECT * FROM gtest22c WHERE b = 8; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b * 3 = 12; +SELECT * FROM gtest22c WHERE b * 3 = 12; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE a = 1 AND b > 0; +SELECT * FROM gtest22c WHERE a = 1 AND b > 0; +RESET enable_seqscan; +RESET enable_bitmapscan; -- foreign keys CREATE TABLE gtest23a (x int PRIMARY KEY, y int); -- 2.34.1
