From b5a053bd62a7801c607968d75337bbb378fd0f99 Mon Sep 17 00:00:00 2001 From: jian he 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=tRBovBkgRuxw063GaA@mail.gmail.com discussion: https://postgr.es/m/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9MAwg@mail.gmail.com related thread: https://postgr.es/m/18970-a7d1cfe1f8d5d8d9@postgresql.org 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