From 8a60de43a7d1abf765a16890d6da7dc7e7f8a06d Mon Sep 17 00:00:00 2001 From: jian he 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$<iteration count>:&l + + + indattrgenerated int2vector + (references pg_attribute.attnum) + + + This is an array of indnatts values that + indicate which table virtual generated columns this index indexes. + For example, a value of 1 3 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. + + + indexprs pg_node_tree 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