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.
From 8a60de43a7d1abf765a16890d6da7dc7e7f8a06d Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Wed, 26 Mar 2025 15:01:28 +0800 Subject: [PATCH v1 1/1] support create index on virtual generated column.
* 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. discussion: https://postgr.es/m/ commitfest entry: --- contrib/pageinspect/expected/btree.out | 33 +++ contrib/pageinspect/sql/btree.sql | 21 ++ doc/src/sgml/catalogs.sgml | 15 ++ src/backend/catalog/index.c | 8 + src/backend/commands/indexcmds.c | 188 ++++++++++++++---- src/backend/commands/tablecmds.c | 46 +++++ src/backend/utils/adt/ruleutils.c | 29 ++- src/backend/utils/cache/relcache.c | 74 +++++++ src/include/catalog/pg_index.h | 1 + src/include/nodes/execnodes.h | 1 + src/include/utils/relcache.h | 1 + src/test/regress/expected/fast_default.out | 8 + .../regress/expected/generated_virtual.out | 116 +++++++++-- src/test/regress/sql/fast_default.sql | 6 + src/test/regress/sql/generated_virtual.sql | 60 ++++-- 15 files changed, 530 insertions(+), 77 deletions(-) diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out index 0aa5d73322f..56d57848cf7 100644 --- a/contrib/pageinspect/expected/btree.out +++ b/contrib/pageinspect/expected/btree.out @@ -183,6 +183,39 @@ 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 test3 (a int8, b int4range, c int8 generated always as (a+1) virtual); +INSERT INTO test3 VALUES (72057594037927936, '[0,1)'); +CREATE INDEX test3_a_idx ON test3 USING btree (c); +SELECT * FROM bt_page_items('test3_a_idx', 1); +-[ RECORD 1 ]----------------------- +itemoffset | 1 +ctid | (0,1) +itemlen | 16 +nulls | f +vars | f +data | 01 00 00 00 00 00 00 01 +dead | f +htid | (0,1) +tids | + +--expect zero row. +SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1)) +EXCEPT ALL +SELECT * FROM bt_page_items(get_raw_page('test3_a_idx', 1)); +(0 rows) + +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 ON test4 USING btree (b); +CREATE INDEX test4_c ON test4 USING btree (c); +ALTER TABLE test4 alter column b set data type text; +---should return zero row. +SELECT * FROM bt_page_items('test4_b', 1) +EXCEPT ALL +SELECT * FROM bt_page_items('test4_c', 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..d3392c11d5f 100644 --- a/contrib/pageinspect/sql/btree.sql +++ b/contrib/pageinspect/sql/btree.sql @@ -32,6 +32,27 @@ 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 test3 (a int8, b int4range, c int8 generated always as (a+1) virtual); +INSERT INTO test3 VALUES (72057594037927936, '[0,1)'); +CREATE INDEX test3_a_idx ON test3 USING btree (c); +SELECT * FROM bt_page_items('test3_a_idx', 1); + +--expect zero row. +SELECT * FROM bt_page_items(get_raw_page('test1_a_idx', 1)) +EXCEPT ALL +SELECT * FROM bt_page_items(get_raw_page('test3_a_idx', 1)); + +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 ON test4 USING btree (b); +CREATE INDEX test4_c ON test4 USING btree (c); +ALTER TABLE test4 alter column b set data type text; +---should return zero row. +SELECT * FROM bt_page_items('test4_b', 1) +EXCEPT ALL +SELECT * FROM bt_page_items('test4_c', 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/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index fb050635551..da37c7a45d8 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4595,6 +4595,21 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l </para></entry> </row> + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>indattrgenerated</structfield> <type>int2vector</type> + (references <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>.<structfield>attnum</structfield>) + </para> + <para> + This is an array of <structfield>indnatts</structfield> values that + indicate which table virtual generated columns this index indexes. + For example, a value of <literal>1 3</literal> would mean that the first + and the third table columns of this index entries are virtual generated + column. A zero in this array indicates that the corresponding index + attribute is not virtual generated column reference. + </para></entry> + </row> + <row> <entry role="catalog_table_entry"><para role="column_definition"> <structfield>indexprs</structfield> <type>pg_node_tree</type> diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 739a92bdcc1..105d48c49d0 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -584,6 +584,12 @@ UpdateIndexRelation(Oid indexoid, Relation pg_index; HeapTuple tuple; int i; + int2vector *indgenkey; + int16 *colgenerated; + + colgenerated = palloc_array(int16, indexInfo->ii_NumIndexAttrs); + for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++) + colgenerated[i] = indexInfo->ii_IndexAttrGeneratedNumbers[i]; /* * Copy the index key, opclass, and indoption info into arrays (should we @@ -596,6 +602,7 @@ UpdateIndexRelation(Oid indexoid, indclass = buildoidvector(opclassOids, indexInfo->ii_NumIndexKeyAttrs); indoption = buildint2vector(coloptions, indexInfo->ii_NumIndexKeyAttrs); + indgenkey = buildint2vector(colgenerated, indexInfo->ii_NumIndexAttrs); /* * Convert the index expressions (if any) to a text datum */ @@ -653,6 +660,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); values[Anum_pg_index_indoption - 1] = PointerGetDatum(indoption); + values[Anum_pg_index_indattrgenerated - 1] = PointerGetDatum(indgenkey); values[Anum_pg_index_indexprs - 1] = exprsDatum; if (exprsDatum == (Datum) 0) nulls[Anum_pg_index_indexprs - 1] = true; diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 89cc83e8843..44e806520f8 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" @@ -90,6 +91,7 @@ static void ComputeIndexAttrs(IndexInfo *indexInfo, bool amcanorder, bool isconstraint, bool iswithoutoverlaps, + bool is_primary, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel); @@ -182,6 +184,7 @@ CheckIndexCompatible(Oid oldId, bool isWithoutOverlaps) { bool isconstraint; + bool is_primary; Oid *typeIds; Oid *collationIds; Oid *opclassIds; @@ -214,6 +217,12 @@ CheckIndexCompatible(Oid oldId, */ isconstraint = false; + /* + * We can pretend is_primary = false unconditionally. It only serves to + * decide the text of an error message that should never happen for us. + */ + is_primary = false; + numberOfAttributes = list_length(attributeList); Assert(numberOfAttributes > 0); Assert(numberOfAttributes <= INDEX_MAX_KEYS); @@ -254,7 +263,7 @@ CheckIndexCompatible(Oid oldId, coloptions, attributeList, exclusionOpNames, relationId, accessMethodName, accessMethodId, - amcanorder, isconstraint, isWithoutOverlaps, InvalidOid, + amcanorder, isconstraint, isWithoutOverlaps, is_primary, InvalidOid, 0, NULL); /* Get the soon-obsolete pg_index tuple. */ @@ -905,6 +914,29 @@ DefineIndex(Oid tableId, if (stmt->whereClause) CheckPredicate((Expr *) stmt->whereClause); + /* virtual generated column over predicate index not supported */ + if (RelationGetDescr(rel)->constr && RelationGetDescr(rel)->constr->has_generated_virtual) + { + Bitmapset *indexattrs_pred = NULL; + int j; + + pull_varattnos(stmt->whereClause, 1, &indexattrs_pred); + + j = -1; + while ((j = bms_next_member(indexattrs_pred, j)) >= 0) + { + AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber; + + if (TupleDescAttr(RelationGetDescr(rel), attno - 1)->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + { + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("partial index on virtual generated columns are not supported")); + break; + } + } + } + /* * Parse AM-specific options, convert to text array form, validate. */ @@ -941,6 +973,7 @@ DefineIndex(Oid tableId, stmt->excludeOpNames, tableId, accessMethodName, accessMethodId, amcanorder, stmt->isconstraint, stmt->iswithoutoverlaps, + stmt->primary, root_save_userid, root_save_sec_context, &root_save_nestlevel); @@ -1101,10 +1134,7 @@ DefineIndex(Oid tableId, /* * We disallow indexes on system columns. They would not necessarily get - * updated correctly, and they don't seem useful anyway. - * - * Also disallow virtual generated columns in indexes (use expression - * index instead). + * updated correctly, and they don't seem useful anyway. */ for (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++) { @@ -1114,24 +1144,14 @@ DefineIndex(Oid tableId, 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->isconstraint ? - errmsg("unique constraints on virtual generated columns are not supported") : - errmsg("indexes on virtual generated columns are not supported"))); } /* - * Also check for system and generated columns used in expressions or - * predicates. + * Also check for system used in expressions or predicates. */ 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); @@ -1144,24 +1164,6 @@ DefineIndex(Oid tableId, (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() */ @@ -1879,6 +1881,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, bool amcanorder, bool isconstraint, bool iswithoutoverlaps, + bool is_primary, Oid ddl_userid, int ddl_sec_context, int *ddl_save_nestlevel) @@ -1889,6 +1892,28 @@ ComputeIndexAttrs(IndexInfo *indexInfo, int nkeycols = indexInfo->ii_NumIndexKeyAttrs; Oid save_userid; int save_sec_context; + Relation rel; + TupleDesc reltupldesc; + List *virtual_generated = NIL; + + rel = table_open(relId, NoLock); + reltupldesc = RelationGetDescr(rel); + + /* + * currently, we do not support virtual generated columns over expression + * indexes. we accumulate the attribute number of virtual generated columns + * so we can verify it later. + */ + if (reltupldesc && reltupldesc->constr && reltupldesc->constr->has_generated_virtual) + { + for (int i = 0; i < reltupldesc->natts; i++) + { + Form_pg_attribute attr = TupleDescAttr(reltupldesc, i); + + if (attr->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + virtual_generated = lappend_int(virtual_generated, attr->attnum); + } + } /* Allocate space for exclusion operator info, if needed */ if (exclusionOpNames) @@ -1931,6 +1956,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, IndexElem *attribute = (IndexElem *) lfirst(lc); Oid atttype; Oid attcollation; + char attgenerated = '\0'; /* * Process the column-or-expression to be indexed. @@ -1940,6 +1966,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, /* Simple index attribute */ HeapTuple atttuple; Form_pg_attribute attform; + AttrNumber attnum; Assert(attribute->expr == NULL); atttuple = SearchSysCacheAttName(relId, attribute->name); @@ -1958,15 +1985,95 @@ ComputeIndexAttrs(IndexInfo *indexInfo, attribute->name))); } attform = (Form_pg_attribute) GETSTRUCT(atttuple); + attnum = attform->attnum; indexInfo->ii_IndexAttrNumbers[attn] = attform->attnum; atttype = attform->atttypid; attcollation = attform->attcollation; + attgenerated = attform->attgenerated; ReleaseSysCache(atttuple); + + if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL) + { + Node *node; + Bitmapset *genattrs = NULL; + + if (attn >= nkeycols) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("virtual generated column are not supported in included columns")); + + if (is_primary) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("primary key on virtual generated columns are not supported")); + + if (indexInfo->ii_Unique) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("unique constraints on virtual generated columns are not supported")); + + /* Fetch the GENERATED AS expression tree */ + node = build_generation_expression(rel, attnum); + if (node == NULL) + elog(ERROR, "no generation expression found for column number %d of table \"%s\"", + attnum, RelationGetRelationName(rel)); + + + /* + * if the generation expression is reference another simple Var, + * then set ii_IndexAttrNumbers to that Var->varattno. + */ + if (IsA(node, Var)) + { + Var *var = (Var *) node; + + Assert(var->varattno > 0); + + if (atttype != var->vartype) + elog(ERROR, "expect type %u but get %u", atttype, var->vartype); + + if (attcollation != var->varcollid) + elog(ERROR, "expect collation %u but get %u", attcollation, var->varcollid); + + indexInfo->ii_IndexAttrNumbers[attn] = var->varattno; + } + else + { + /* + * Strip any top-level COLLATE clause. This ensures that we treat + * "x COLLATE y" and "(x COLLATE y)" alike. + */ + while (IsA(node, CollateExpr)) + node = (Node *) ((CollateExpr *) node)->arg; + + /* generation expressions are immutable, so this unlikely to happen */ + if (contain_mutable_functions_after_planning((Expr *) node)) + elog(ERROR,"functions in index expression must be marked IMMUTABLE"); + + /* virtual generated column should based on pg_attribute.attcollation */ + if (attcollation != exprCollation(node)) + elog(ERROR, "expect collation %u but get %u", attcollation, exprCollation(node)); + + pull_varattnos(node, 1, &genattrs); + + if (genattrs == NULL) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("can not create index based on variable free generation expression")); + + indexInfo->ii_IndexAttrNumbers[attn] = 0; /* marks expression */ + indexInfo->ii_Expressions = lappend(indexInfo->ii_Expressions, + node); + } + indexInfo->ii_IndexAttrGeneratedNumbers[attn] = attnum; + } } else { /* Index expression */ Node *expr = attribute->expr; + Bitmapset *indexattrs_expr = NULL; + int j; Assert(expr != NULL); @@ -1977,6 +2084,18 @@ ComputeIndexAttrs(IndexInfo *indexInfo, atttype = exprType(expr); attcollation = exprCollation(expr); + pull_varattnos(expr, 1, &indexattrs_expr); + + j = -1; + while ((j = bms_next_member(indexattrs_expr, j)) >= 0) + { + AttrNumber attno = j + FirstLowInvalidHeapAttributeNumber; + if (list_member_int(virtual_generated, attno)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("expression index over virtual generated columns are not supported")); + } + /* * Strip any top-level COLLATE clause. This ensures that we treat * "x COLLATE y" and "(x COLLATE y)" alike. @@ -2246,6 +2365,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo, attn++; } + table_close(rel, NoLock); } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1202544ebd0..b9561a9cf1a 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -8547,6 +8547,35 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, */ RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); } + else + { + List *changed_gen_IndexOids = NIL; + + RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName); + + /* + * Internally, we convert index of virtual generation column into an + * expression index. For example, if column 'b' is defined as (b INT + * GENERATED ALWAYS AS (a * 2) VIRTUAL) then index over 'b' would + * transformed into an expression index as ((a * 2)). As a result, the + * pg_depend refobjsubid does not retain the original attribute number + * of the virtual generated column. But we need rebuild any index that + * was build on virtual generated column. so we need search pg_index. + */ + changed_gen_IndexOids = RelationGetGeneratedIndexList(rel, attnum); + + foreach_oid(idxoid, changed_gen_IndexOids) + RememberIndexForRebuilding(idxoid, tab); + + /* + * Changing the generation expression of the virtual generated column + * does not require table rewrite. However, if an index is built on top + * of it, table rewrite is necessary. So in phase 3, index_rebuild can + * successfully rebuild the index based on the new generation expression + */ + if (attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && tab->changedIndexOids != NIL) + rewrite = true; + } /* * Drop the dependency records of the GENERATED expression, in particular @@ -14035,6 +14064,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, SysScanDesc scan; HeapTuple depTup; ObjectAddress address; + List *changed_gen_IndexOids = NIL; /* * Clear all the missing values if we're rewriting the table, since this @@ -14115,6 +14145,7 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, errmsg("default for column \"%s\" cannot be cast automatically to type %s", colName, format_type_be(targettype)))); } + exprSetCollation(defaultexpr, targetcollid); } else defaultexpr = NULL; @@ -14130,6 +14161,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel, */ RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName); + /* + * RememberAllDependentForRebuilding cannot collect everything that is + * depends on virtual generated column. Because simple column index over + * virtual generated column was converted to expression index. see + * ATExecSetExpression also. + */ + changed_gen_IndexOids = RelationGetGeneratedIndexList(rel, attnum); + + foreach_oid(idxoid, changed_gen_IndexOids) + RememberIndexForRebuilding(idxoid, tab); + + if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL && + tab->changedIndexOids != NIL) + tab->rewrite |= AT_REWRITE_COLUMN_REWRITE; + /* * Now scan for dependencies of this column on other things. The only * things we should find are the dependency on the column datatype and diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 9e90acedb91..6dffd1ca6c1 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1290,9 +1290,11 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, Datum indcollDatum; Datum indclassDatum; Datum indoptionDatum; + Datum indgenkeyDatum; oidvector *indcollation; oidvector *indclass; int2vector *indoption; + int2vector *indgenkey; StringInfoData buf; char *str; char *sep; @@ -1325,6 +1327,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, Anum_pg_index_indoption); indoption = (int2vector *) DatumGetPointer(indoptionDatum); + indgenkeyDatum = SysCacheGetAttrNotNull(INDEXRELID, ht_idx, + Anum_pg_index_indattrgenerated); + indgenkey = (int2vector *) DatumGetPointer(indgenkeyDatum); + /* * Fetch the pg_class tuple of the index relation */ @@ -1398,6 +1404,7 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, for (keyno = 0; keyno < idxrec->indnatts; keyno++) { AttrNumber attnum = idxrec->indkey.values[keyno]; + AttrNumber gennum = indgenkey->values[keyno]; Oid keycoltype; Oid keycolcollation; @@ -1418,7 +1425,27 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, appendStringInfoString(&buf, sep); sep = ", "; - if (attnum != 0) + if (attnum == 0 && gennum != 0) + indexpr_item = lnext(indexprs, indexpr_item); + + if (gennum != 0) + { + /* + * index over virtual generated column was converted into a + * expression index, but we need restore the original attribute + * number for recreate it. + */ + char *virtual_attname; + int32 geneycoltypmod; + + virtual_attname = get_attname(indrelid, gennum, false); + if (!colno || colno == keyno + 1) + appendStringInfoString(&buf, quote_identifier(virtual_attname)); + get_atttypetypmodcoll(indrelid, gennum, + &keycoltype, &geneycoltypmod, + &keycolcollation); + } + else if (attnum != 0) { /* Simple index column */ char *attname; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 9f54a9e72b7..e931f2f0390 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -4880,6 +4880,80 @@ RelationGetIndexList(Relation relation) return result; } +/* + * RelationGetGeneratedIndexList + * + * attnum is the virtual generated column attribute number + * get the index that is build based on virtual generation column. + * note: currently we do not support primary key/unique index over virtual + * generated column. +*/ +List * +RelationGetGeneratedIndexList(Relation relation, int attnum) +{ + Relation indrel; + SysScanDesc indscan; + ScanKeyData skey; + HeapTuple htup; + List *result; + Datum datum; + int2vector *indattrgenerated; + + result = NIL; + + /* Prepare to scan pg_index for entries having indrelid = this relation. */ + ScanKeyInit(&skey, + Anum_pg_index_indrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(relation))); + + indrel = table_open(IndexRelationId, AccessShareLock); + indscan = systable_beginscan(indrel, IndexIndrelidIndexId, true, + NULL, 1, &skey); + + while (HeapTupleIsValid(htup = systable_getnext(indscan))) + { + Form_pg_index index = (Form_pg_index) GETSTRUCT(htup); + + /* + * Ignore any indexes that are currently being dropped. This will + * prevent them from being searched, inserted into, or considered in + * HOT-safety decisions. It's unsafe to touch such an index at all + * since its catalog entries could disappear at any instant. + */ + if (!index->indislive) + continue; + + if (!index->indisvalid) + continue; + + datum = SysCacheGetAttrNotNull(INDEXRELID, htup, Anum_pg_index_indattrgenerated); + indattrgenerated = ((int2vector *) DatumGetPointer(datum)); + + if (indattrgenerated->dim1 > 0) + { + int i; + for (i = 0; i < indattrgenerated->dim1; i++) + { + if (indattrgenerated->values[i] == attnum) + { + result = lappend_oid(result, index->indexrelid); + break; + } + } + } + } + + systable_endscan(indscan); + + table_close(indrel, AccessShareLock); + + /* Sort the result list into OID order, per API spec. */ + list_sort(result, list_oid_cmp); + + return result; +} + /* * RelationGetStatExtList * get a list of OIDs of statistics objects on this relation diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index 4392b9d221d..7ed74a593a4 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -54,6 +54,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO oidvector indclass BKI_LOOKUP(pg_opclass) BKI_FORCE_NOT_NULL; /* opclass identifiers */ int2vector indoption BKI_FORCE_NOT_NULL; /* per-column flags * (AM-specific meanings) */ + int2vector indattrgenerated BKI_FORCE_NOT_NULL; /* the attribute of virtual generated column? */ pg_node_tree indexprs; /* expression trees for index attributes that * are not simple column references; one for * each zero entry in indkey[] */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index d4d4e655180..ba544f33cb4 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -196,6 +196,7 @@ typedef struct IndexInfo int ii_NumIndexAttrs; /* total number of columns in index */ int ii_NumIndexKeyAttrs; /* number of key columns in index */ AttrNumber ii_IndexAttrNumbers[INDEX_MAX_KEYS]; + AttrNumber ii_IndexAttrGeneratedNumbers[INDEX_MAX_KEYS]; /* XXX more better comments */ List *ii_Expressions; /* list of Expr */ List *ii_ExpressionsState; /* list of ExprState */ List *ii_Predicate; /* list of Expr */ diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h index a7c55db339e..56bcc03dc51 100644 --- a/src/include/utils/relcache.h +++ b/src/include/utils/relcache.h @@ -45,6 +45,7 @@ extern void RelationClose(Relation relation); */ extern List *RelationGetFKeyList(Relation relation); extern List *RelationGetIndexList(Relation relation); +extern List *RelationGetGeneratedIndexList(Relation relation, int attnum); extern List *RelationGetStatExtList(Relation relation); extern Oid RelationGetPrimaryKeyIndex(Relation relation, bool deferrable_ok); extern Oid RelationGetReplicaIndex(Relation relation); diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out index ccbcdf8403f..ef19f667cc1 100644 --- a/src/test/regress/expected/fast_default.out +++ b/src/test/regress/expected/fast_default.out @@ -70,6 +70,14 @@ NOTICE: rewriting table has_volatile for reason 4 -- stored generated columns need a rewrite ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored; NOTICE: rewriting table has_volatile for reason 2 +-- if there is any index over virtual generated columns, +-- change generation expression need rewrite +CREATE INDEX on has_volatile(col6); +ALTER TABLE has_volatile ALTER COLUMN col6 SET EXPRESSION AS (col1 * 3); +NOTICE: rewriting table has_volatile for reason 2 +-- table rewrite again. +ALTER TABLE has_volatile ALTER COLUMN col6 SET DATA TYPE INT8; +NOTICE: rewriting table has_volatile for reason 4 -- Test a large sample of different datatypes CREATE TABLE T(pk INT NOT NULL PRIMARY KEY, c_int INT DEFAULT 1); SELECT set('t'); diff --git a/src/test/regress/expected/generated_virtual.out b/src/test/regress/expected/generated_virtual.out index dc09c85938e..21173f3b205 100644 --- a/src/test/regress/expected/generated_virtual.out +++ b/src/test/regress/expected/generated_virtual.out @@ -697,30 +697,106 @@ ERROR: not-null constraints are not supported on virtual generated columns --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; +CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL + ,c int GENERATED ALWAYS AS (11) VIRTUAL + ,d int GENERATED ALWAYS AS (a *3) VIRTUAL + ,e int4range GENERATED ALWAYS AS (int4range(a, a+10)) VIRTUAL + ,f int GENERATED ALWAYS AS (a) VIRTUAL); +CREATE INDEX gtest22c_b_idx ON gtest22c (b); +--variable free generation expression have no pratical usage, so error out. +CREATE INDEX gtest22c_c_idx ON gtest22c (c); +ERROR: can not create index based on variable free generation expression +CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d); +CREATE INDEX gtest22c_e_idx ON gtest22c USING gist(e); +--error. include columns are not supported. +CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (b,c); +ERROR: virtual generated column are not supported in included columns +CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (f); +ERROR: virtual generated column are not supported in included columns +-- 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) + c | integer | | | generated always as (11) + d | integer | | | generated always as (a * 3) + e | int4range | | | generated always as (int4range(a, a + 10)) + f | integer | | | generated always as (a) +Indexes: + "gtest22c_b_idx" btree (b) + "gtest22c_d_idx" hash (d) + "gtest22c_e_idx" gist (e) + +INSERT INTO gtest22c(a) VALUES (1), (2), (3), (10); +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 | c | d | e | f +---+---+----+---+--------+--- + 2 | 4 | 11 | 6 | [2,12) | 2 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE d = 6; + QUERY PLAN +--------------------------------------------- + Index Scan using gtest22c_d_idx on gtest22c + Index Cond: ((a * 3) = 6) +(2 rows) + +SELECT * FROM gtest22c WHERE d = 6; + a | b | c | d | e | f +---+---+----+---+--------+--- + 2 | 4 | 11 | 6 | [2,12) | 2 +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE e @> 12; + QUERY PLAN +---------------------------------------------- + Index Scan using gtest22c_e_idx on gtest22c + Index Cond: (int4range(a, (a + 10)) @> 12) +(2 rows) + +select count(*) from gtest22c where e @> 12; + count +------- + 2 +(1 row) + +-- 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; + QUERY PLAN +--------------------------------------------- + Index Scan using gtest22c_b_idx on gtest22c + Index Cond: ((a * 4) = 8) +(2 rows) + +SELECT * FROM gtest22c WHERE b = 8; + a | b | c | d | e | f +---+---+----+---+--------+--- + 2 | 8 | 11 | 6 | [2,12) | 2 +(1 row) + --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; +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); diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql index 068dd0bc8aa..b39e76bcfc3 100644 --- a/src/test/regress/sql/fast_default.sql +++ b/src/test/regress/sql/fast_default.sql @@ -77,6 +77,12 @@ ALTER TABLE has_volatile ALTER COLUMN col1 SET DATA TYPE float8, -- stored generated columns need a rewrite ALTER TABLE has_volatile ADD col7 int GENERATED ALWAYS AS (55) stored; +-- if there is any index over virtual generated columns, +-- change generation expression need rewrite +CREATE INDEX on has_volatile(col6); +ALTER TABLE has_volatile ALTER COLUMN col6 SET EXPRESSION AS (col1 * 3); +-- table rewrite again. +ALTER TABLE has_volatile ALTER COLUMN col6 SET DATA TYPE INT8; -- Test a large sample of different datatypes diff --git a/src/test/regress/sql/generated_virtual.sql b/src/test/regress/sql/generated_virtual.sql index dab8c92ef99..ae58cff7d22 100644 --- a/src/test/regress/sql/generated_virtual.sql +++ b/src/test/regress/sql/generated_virtual.sql @@ -1,6 +1,4 @@ -- keep these tests aligned with generated_stored.sql - - CREATE SCHEMA generated_virtual_tests; GRANT USAGE ON SCHEMA generated_virtual_tests TO PUBLIC; SET search_path = generated_virtual_tests; @@ -363,32 +361,50 @@ CREATE TABLE gtest22b (a int, b int GENERATED ALWAYS AS (a / 2) VIRTUAL, PRIMARY --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 +CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2) VIRTUAL + ,c int GENERATED ALWAYS AS (11) VIRTUAL + ,d int GENERATED ALWAYS AS (a *3) VIRTUAL + ,e int4range GENERATED ALWAYS AS (int4range(a, a+10)) VIRTUAL + ,f int GENERATED ALWAYS AS (a) VIRTUAL); +CREATE INDEX gtest22c_b_idx ON gtest22c (b); +--variable free generation expression have no pratical usage, so error out. +CREATE INDEX gtest22c_c_idx ON gtest22c (c); +CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d); +CREATE INDEX gtest22c_e_idx ON gtest22c USING gist(e); +--error. include columns are not supported. +CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (b,c); +CREATE INDEX gtest22c_idx1 ON gtest22c USING btree(a) include (f); ---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; +-- CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3)); +-- CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0; +\d gtest22c ---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; +INSERT INTO gtest22c(a) VALUES (1), (2), (3), (10); +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 d = 6; +SELECT * FROM gtest22c WHERE d = 6; +EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE e @> 12; +select count(*) from gtest22c where e @> 12; + +-- 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; + +RESET enable_seqscan; +RESET enable_bitmapscan; -- foreign keys CREATE TABLE gtest23a (x int PRIMARY KEY, y int); -- 2.34.1
