v1-0001-index-on-virtual-generated-column.patch
application/octet-stream
Filename: v1-0001-index-on-virtual-generated-column.patch
Type: application/octet-stream
Part: 0
From 04061150c5d0ac65814c05c921f21efd04a22e9a Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 16 Mar 2026 11:28:02 +0800
Subject: [PATCH v1] 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 | 56 +++-----
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 | 50 +++----
7 files changed, 203 insertions(+), 84 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 9ab74c8df0a..0d256507f86 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -54,6 +54,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"
@@ -906,8 +907,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.
*/
@@ -1121,17 +1127,6 @@ DefineIndex(ParseState *pstate,
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("index creation on system columns is not supported")));
-
-
- if (attno > 0 &&
- 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"));
}
/*
@@ -1141,7 +1136,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);
@@ -1154,25 +1148,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 (attno > 0 &&
- 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() */
@@ -1903,6 +1878,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)
@@ -1977,12 +1954,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);
@@ -2274,6 +2263,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 6dd22be0e8d..fe159dd6a93 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 24d5dbf46ca..3b61ae81197 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -763,39 +763,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);
@@ -813,7 +892,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 f5f13bbd3e7..7c35f214f63 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 9c2bb6590b3..b99616dff77 100644
--- a/src/test/regress/sql/generated_virtual.sql
+++ b/src/test/regress/sql/generated_virtual.sql
@@ -405,31 +405,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
-
---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
+
+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;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
--
2.43.0