On Wed, Jul 23, 2025 at 4:54 AM Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> Corey Huinker <corey.huin...@gmail.com> writes:
> > I'm interested in this feature, specifically whether the optimizer uses the
> > index in situations where the expression is used rather than the virtual
> > column name.
>
> Hmm, I kinda think we should not do this. The entire point of a
> virtual column is that its values are not stored and so you can
> (for example) change the generation expression "for free".
> If it's referenced in an index that advantage goes out the window
> because we'll have to rebuild the index.
>
> Besides, this does nothing you haven't been able to do for
> decades with expression indexes.
>
hi.
CREATE TABLE example (regular_name text, lowecase_name text GENERATED
ALWAYS AS (lower(regular_name)) VIRTUAL);
CREATE INDEX example_b ON example(lowecase_name);
CREATE INDEX example_c ON example(lower(regular_name));
select distinct indnatts,indnkeyatts,indisunique,
indnullsnotdistinct,indisprimary,indisexclusion,indimmediate,indisclustered,indisvalid,
indcheckxmin,indisready,indislive,indisreplident,indkey,indcollation,indclass,indoption,
indexprs
from pg_index
where indrelid ='example'::regclass;
will return one row, meaning catalog table pg_index stored almost all
the same information.
For indexes example_b and example_c, the only difference lies in the new column
indattrgenerated. In example_b, indattrgenerated is not null, whereas in
example_c, it is null. This column (indattrgenerated) is needed to track
dependencies on generated columns, which is important for index
rebuild.
obviously, get_relation_info will collect the same information for
example_b, example_c.
which means the optimizer will use the same information to make the decision.
---------------------------------
set enable_seqscan to off;
set enable_bitmapscan to off;
CREATE TABLE example (regular_name text, lowecase_name text GENERATED
ALWAYS AS (lower(regular_name)) VIRTUAL);
CREATE INDEX example_b ON example(lowecase_name);
EXPLAIN(COSTS OFF) SELECT regular_name FROM example WHERE
lowecase_name = 'john q smith';
EXPLAIN(COSTS OFF) SELECT regular_name FROM example WHERE
lower(regular_name) = 'john q smith';
So current implementation, the above two query plans will produce the same query
plan. the generation expression or virtual generated column data type changes
will cause the index to rebuild.
Is this we want?
Or should changing the generation expression or data type of a virtual generated
column mark the associated index as invalid, without triggering a rebuild?
From bbc6f3bd7daf53fe97e72bf92a6f9e1f1d71e736 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Thu, 24 Jul 2025 18:33:33 +0800
Subject: [PATCH v6 1/1] index on virtual generated column
* btree, hash, gist, spgist, gin, brin all are supported
* Primary key and unique indexes on virtual generated columns are not supported.
* exclusion constraint on virtual generated columns are not supported, maybe future
* Expression indexes and partial (predicate) indexes on virtual generated
columns are currently unsupported.
* Indexes with included columns cannot use virtual generated columns.
* An index on a virtual generated column like ``(b INT GENERATED ALWAYS AS (a) VIRTUAL)``
is effectively equivalent to an index on a.
* Internally, such indexes are transformed into expression indexes. For
example, an index on ``(b INT GENERATED ALWAYS AS (a * 2) VIRTUAL)``
is internally represented as an expression index on (a * 2).
* In the pageinspect module, additional tests added for verify the index content
for virtual generated columns.
* All index types are supported, including hash and GiST indexes, and
corresponding regression tests have been added.
* To support ALTER TABLE ... SET EXPRESSION, the pg_index catalog tracks the
original attribute number of the virtual generated column, allowing
identification of indexes that require rebuilding.
* ALTER COLUMN SET DATA TYPE also triggers a table rewrite; therefore, tracking
the attribute number of the virtual generated column used by the index is necessary.
* if the partitioned table and partition both have an index, then the index over the virtual
generated column should be the same expression. For example, the following last
command should error out.
CREATE TABLE parted (b integer,c integer,a integer GENERATED ALWAYS AS (c+1)) PARTITION BY RANGE (b);
CREATE TABLE part (b integer,c integer,a integer GENERATED ALWAYS AS (c));
create index on part(a);
create index on parted(a);
alter table parted ATTACH partition part for values from (1) to (10);
discussion: https://postgr.es/m/CACJufxGao-cypdNhifHAdt8jHfK6-HX=trbovbkgruxw063...@mail.gmail.com
discussion: https://postgr.es/m/CACJufxGgkH0PyyqP6ggqcEWHxZzmkV=puY8ad=s8kisss9m...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/5667
---
contrib/pageinspect/expected/btree.out | 11 +
contrib/pageinspect/sql/btree.sql | 10 +
doc/src/sgml/catalogs.sgml | 15 +
src/backend/catalog/index.c | 77 +++++
src/backend/commands/indexcmds.c | 314 +++++++++++++++---
src/backend/commands/tablecmds.c | 72 ++++
src/backend/parser/parse_utilcmd.c | 25 +-
src/backend/utils/adt/ruleutils.c | 34 +-
src/include/catalog/index.h | 6 +
src/include/catalog/pg_index.h | 1 +
src/include/nodes/execnodes.h | 8 +
.../regress/expected/collate.icu.utf8.out | 11 +
src/test/regress/expected/fast_default.out | 8 +
.../regress/expected/generated_virtual.out | 262 ++++++++++++++-
src/test/regress/sql/collate.icu.utf8.sql | 5 +
src/test/regress/sql/fast_default.sql | 6 +
src/test/regress/sql/generated_virtual.sql | 124 ++++++-
17 files changed, 916 insertions(+), 73 deletions(-)
diff --git a/contrib/pageinspect/expected/btree.out b/contrib/pageinspect/expected/btree.out
index 0aa5d73322f..b7d36f7d047 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..2670f85f79a 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/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 97f547b3cc4..455c27c1617 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4589,6 +4589,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 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 c4029a4f3d3..db067d2788f 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
@@ -595,6 +601,7 @@ UpdateIndexRelation(Oid indexoid,
indcollation = buildoidvector(collationOids, indexInfo->ii_NumIndexKeyAttrs);
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;
@@ -1134,6 +1142,28 @@ index_create(Relation heapRelation,
}
}
+ /*
+ * 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 auto
+ * dependencies on referenced virtual generated columns.
+ */
+ for (i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
+ {
+ if (indexInfo->ii_IndexAttrGeneratedNumbers[i] != 0)
+ {
+ ObjectAddressSubSet(referenced, RelationRelationId,
+ heapRelationId,
+ indexInfo->ii_IndexAttrGeneratedNumbers[i]);
+ add_exact_object_address(&referenced, addrs);
+ have_simple_col = false;
+ }
+ }
+
/*
* If there are no simply-referenced columns, give the index an
* auto dependency on the whole table. In most cases, this will
@@ -1411,6 +1441,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexColNames = lappend(indexColNames, NameStr(att->attname));
newInfo->ii_IndexAttrNumbers[i] = oldInfo->ii_IndexAttrNumbers[i];
+ newInfo->ii_IndexAttrGeneratedNumbers[i] = oldInfo->ii_IndexAttrGeneratedNumbers[i];
}
/* Extract opclass options for each attribute */
@@ -2428,9 +2459,12 @@ IndexInfo *
BuildIndexInfo(Relation index)
{
IndexInfo *ii;
+ HeapTuple ht_idx;
Form_pg_index indexStruct = index->rd_index;
int i;
int numAtts;
+ Datum indgenkeyDatum;
+ int2vector *indgenkey;
/* check the number of keys, and copy attr numbers into the IndexInfo */
numAtts = indexStruct->indnatts;
@@ -2454,9 +2488,19 @@ BuildIndexInfo(Relation index)
index->rd_indam->amsummarizing,
indexStruct->indisexclusion && indexStruct->indisunique);
+ ht_idx = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexStruct->indexrelid));
+ indgenkeyDatum = SysCacheGetAttrNotNull(INDEXRELID, ht_idx,
+ Anum_pg_index_indattrgenerated);
+ indgenkey = (int2vector *) DatumGetPointer(indgenkeyDatum);
+
/* fill in attribute numbers */
for (i = 0; i < numAtts; i++)
+ {
ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i];
+ ii->ii_IndexAttrGeneratedNumbers[i] = indgenkey->values[i];
+ }
+
+ ReleaseSysCache(ht_idx);
/* fetch exclusion constraint info if any */
if (indexStruct->indisexclusion)
@@ -2518,11 +2562,35 @@ BuildDummyIndexInfo(Relation index)
for (i = 0; i < numAtts; i++)
ii->ii_IndexAttrNumbers[i] = indexStruct->indkey.values[i];
+ /*
+ * Index expressions or predicates are skipped here, see above comments. If
+ * virtual generated columns references another column, ii_IndexAttrNumbers
+ * will set to that referenced column. So do nothing for
+ * ii_IndexAttrGeneratedNumbers here.
+ */
+
/* We ignore the exclusion constraint if any */
return ii;
}
+/*
+ * IndexOverVirtualGenerated
+ * Return whether this index was built on virtual generated column.
+ */
+bool
+IsIndexOverVirtualGenerated(const IndexInfo *info)
+{
+ int i;
+
+ for (i = 0; i < info->ii_NumIndexAttrs; i++)
+ {
+ if (AttributeNumberIsValid(info->ii_IndexAttrGeneratedNumbers[i]))
+ return true;
+ }
+ return false;
+}
+
/*
* CompareIndexInfo
* Return whether the properties of two indexes (in different tables)
@@ -2585,6 +2653,15 @@ CompareIndexInfo(const IndexInfo *info1, const IndexInfo *info2,
return false;
}
+ if (AttributeNumberIsValid(info1->ii_IndexAttrGeneratedNumbers[i]) ||
+ AttributeNumberIsValid(info2->ii_IndexAttrGeneratedNumbers[i]))
+ {
+ /* fail if index over virtual generated column does not match */
+ if (attmap->attnums[info2->ii_IndexAttrGeneratedNumbers[i] - 1] !=
+ info1->ii_IndexAttrGeneratedNumbers[i])
+ return false;
+ }
+
/* collation and opfamily are not valid for included columns */
if (i >= info1->ii_NumIndexKeyAttrs)
continue;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 6f753ab6d7a..c1ddad07888 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,9 +91,15 @@ 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);
+static void compute_index_generatedattrs(IndexInfo *indexInfo,
+ Relation rel,
+ bool is_primary,
+ int attn,
+ int attnum);
static char *ChooseIndexName(const char *tabname, Oid namespaceId,
const List *colnames, const List *exclusionOpNames,
bool primary, bool isconstraint);
@@ -182,6 +189,7 @@ CheckIndexCompatible(Oid oldId,
bool isWithoutOverlaps)
{
bool isconstraint;
+ bool is_primary;
Oid *typeIds;
Oid *collationIds;
Oid *opclassIds;
@@ -214,6 +222,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 +268,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 +919,31 @@ DefineIndex(Oid tableId,
if (stmt->whereClause)
CheckPredicate((Expr *) stmt->whereClause);
+ /* virtual generated column over predicate indexes are not supported */
+ if (RelationGetDescr(rel)->constr &&
+ RelationGetDescr(rel)->constr->has_generated_virtual &&
+ stmt->whereClause)
+ {
+ 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 +980,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);
@@ -1102,9 +1142,6 @@ 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).
*/
for (int i = 0; i < indexInfo->ii_NumIndexAttrs; i++)
{
@@ -1114,26 +1151,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->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"));
}
/*
- * Also check for system and generated columns used in expressions or
- * predicates.
+ * Also check for system columns 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);
@@ -1146,24 +1171,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() */
@@ -1307,6 +1314,7 @@ DefineIndex(Oid tableId,
bool invalidate_parent = false;
Relation parentIndex;
TupleDesc parentDesc;
+ bool parent_idx_virtual;
/*
* Report the total number of partitions at the start of the
@@ -1353,6 +1361,8 @@ DefineIndex(Oid tableId,
parentIndex = index_open(indexRelationId, lockmode);
indexInfo = BuildIndexInfo(parentIndex);
+ parent_idx_virtual = IsIndexOverVirtualGenerated(indexInfo);
+
parentDesc = RelationGetDescr(rel);
/*
@@ -1412,6 +1422,14 @@ DefineIndex(Oid tableId,
parentDesc,
false);
+ /*
+ * child don't have any index, but parent have index over
+ * virtual generated column. We need ensure the indexed
+ * generated expression on the parent match with the child.
+ */
+ if (childidxs == NIL && parent_idx_virtual)
+ check_generated_indexattrs(indexInfo, rel, childrel, attmap, false);
+
foreach(cell, childidxs)
{
Oid cldidxid = lfirst_oid(cell);
@@ -1481,6 +1499,22 @@ DefineIndex(Oid tableId,
index_close(cldidx, NoLock);
break;
}
+ else
+ {
+ bool cldidx_virtual;
+ bool index_virtual;
+ index_virtual = IsIndexOverVirtualGenerated(indexInfo);
+ cldidx_virtual = IsIndexOverVirtualGenerated(cldIdxInfo);
+
+ if (index_virtual || cldidx_virtual)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create index on partitioned table \"%s\"",
+ RelationGetRelationName(rel)),
+ errdetail("The index definition of partitioned table \"%s\" does not match table \"%s\"",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(childrel)));
+ }
index_close(cldidx, lockmode);
}
@@ -1857,6 +1891,73 @@ CheckPredicate(Expr *predicate)
errmsg("functions in index predicate must be marked IMMUTABLE")));
}
+/*
+ * Verify that the generated expression of the parent matches the child
+ *
+ * indexinfo: the IndexInfo that is associated with relation "rel".
+ * childrel: the relation to be attached to "rel" or the child of "rel".
+ * attmap: Attribute mapping between childrel and rel.
+ * is_attach: is this command of ALTER TABLE ATTACH PARTITION
+ *
+ * Use build_attrmap_by_name(childrel, rel) to build the attmap.
+*/
+void check_generated_indexattrs(const IndexInfo *indexinfo,
+ Relation rel,
+ Relation childrel,
+ const AttrMap *attmap,
+ bool is_attach)
+{
+ /* if parent have virtual generated column, child must also have */
+ Assert(rel->rd_att->constr->has_generated_virtual);
+ Assert(childrel->rd_att->constr->has_generated_virtual);
+
+ for (int i = 0; i < indexinfo->ii_NumIndexAttrs; i++)
+ {
+ if (AttributeNumberIsValid(indexinfo->ii_IndexAttrGeneratedNumbers[i]))
+ {
+ Node *node_rel;
+ Node *node_attach;
+ AttrNumber attno;
+ bool found_whole_row;
+
+ attno = indexinfo->ii_IndexAttrGeneratedNumbers[i];
+
+ node_rel = build_generation_expression(rel, attno);
+ node_rel = map_variable_attnos(node_rel,
+ 1,
+ 0,
+ attmap,
+ InvalidOid, &found_whole_row);
+ if (found_whole_row)
+ elog(ERROR, "Index contains a whole-row table reference");
+
+ node_attach = build_generation_expression(childrel,
+ attmap->attnums[attno - 1]);
+
+ if (!equal(node_rel, node_attach))
+ {
+ if (is_attach)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot attach table \"%s\" as partition of partitioned table \"%s\"",
+ RelationGetRelationName(childrel),
+ RelationGetRelationName(rel));
+ errdetail("The index definition of partitioned table \"%s\" does not match table \"%s\"",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(childrel)));
+ else
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot create index on partitioned table \"%s\"",
+ RelationGetRelationName(rel)),
+ errdetail("The index definition of partitioned table \"%s\" does not match table \"%s\"",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(childrel)));
+ }
+ }
+ }
+}
+
/*
* Compute per-index-column information, including indexed column numbers
* or index expressions, opclasses and their options. Note, all output vectors
@@ -1881,6 +1982,7 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
bool amcanorder,
bool isconstraint,
bool iswithoutoverlaps,
+ bool is_primary,
Oid ddl_userid,
int ddl_sec_context,
int *ddl_save_nestlevel)
@@ -1891,6 +1993,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
+ * for expression attribute verification.
+ */
+ if (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)
@@ -1960,7 +2084,13 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
attribute->name)));
}
attform = (Form_pg_attribute) GETSTRUCT(atttuple);
- indexInfo->ii_IndexAttrNumbers[attn] = attform->attnum;
+ if (attform->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL)
+ compute_index_generatedattrs(indexInfo, rel, is_primary, attn, attform->attnum);
+ else
+ {
+ indexInfo->ii_IndexAttrGeneratedNumbers[attn] = 0;
+ indexInfo->ii_IndexAttrNumbers[attn] = attform->attnum;
+ }
atttype = attform->atttypid;
attcollation = attform->attcollation;
ReleaseSysCache(atttuple);
@@ -1986,18 +2116,43 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
while (IsA(expr, CollateExpr))
expr = (Node *) ((CollateExpr *) expr)->arg;
+ if (!IsA(expr, Var))
+ {
+ Bitmapset *idxattrs = NULL;
+ int j = -1;
+
+ pull_varattnos(expr, 1, &idxattrs);
+ while ((j = bms_next_member(idxattrs, 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"));
+ }
+ }
+
if (IsA(expr, Var) &&
((Var *) expr)->varattno != InvalidAttrNumber)
{
- /*
- * User wrote "(column)" or "(column COLLATE something)".
- * Treat it like simple attribute anyway.
- */
- indexInfo->ii_IndexAttrNumbers[attn] = ((Var *) expr)->varattno;
+ int attnum = ((Var *) expr)->varattno;
+
+ if (list_member_int(virtual_generated, attnum))
+ compute_index_generatedattrs(indexInfo, rel, is_primary, attn, attnum);
+ else
+ {
+ /*
+ * User wrote "(column)" or "(column COLLATE something)".
+ * Treat it like simple attribute anyway.
+ */
+ indexInfo->ii_IndexAttrNumbers[attn] = attnum;
+ indexInfo->ii_IndexAttrGeneratedNumbers[attn] = 0;
+ }
}
else
{
indexInfo->ii_IndexAttrNumbers[attn] = 0; /* marks expression */
+ indexInfo->ii_IndexAttrGeneratedNumbers[attn] = 0;
indexInfo->ii_Expressions = lappend(indexInfo->ii_Expressions,
expr);
@@ -2248,6 +2403,83 @@ ComputeIndexAttrs(IndexInfo *indexInfo,
attn++;
}
+
+ table_close(rel, NoLock);
+}
+
+/*
+ * compute IndexInfo ii_IndexAttrNumbers, ii_IndexAttrGeneratedNumbers, ii_Expressions
+ *
+ * indexInfo: this IndexInfo to be build.
+ * rel: the relation this indexInfo is based on.
+ * is_primary: is this index a primary key.
+ * attn: indices of the index key attribute, 0 based.
+ * attnum: virtual generated column attribute number.
+*/
+static void
+compute_index_generatedattrs(IndexInfo *indexInfo, Relation rel,
+ bool is_primary, int attn, int attnum)
+{
+ Node *node;
+
+ if (is_primary)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("primary keys 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"));
+
+ if (attn >= indexInfo->ii_NumIndexKeyAttrs)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("virtual generated column are not supported in index included columns"));
+
+ /* Fetch the GENERATED AS expression tree */
+ node = build_generation_expression(rel, attnum);
+
+ /*
+ * if the generation expression just reference another column, then set
+ * ii_IndexAttrNumbers to that column attribute number.
+ */
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ if (var->varattno < 0)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("index creation on system columns is not supported"));
+
+ indexInfo->ii_IndexAttrNumbers[attn] = var->varattno;
+ }
+ else
+ {
+ /*
+ * Strip any top-level COLLATE clause in generated expression. This
+ * ensures that we treat "x COLLATE y" and "(x COLLATE y)" alike.
+ */
+ while (IsA(node, CollateExpr))
+ node = (Node *) ((CollateExpr *) node)->arg;
+
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+
+ Assert(var->varattno > 0);
+ indexInfo->ii_IndexAttrNumbers[attn] = var->varattno;
+ }
+ else
+ {
+ indexInfo->ii_IndexAttrNumbers[attn] = 0; /* mark as expression index */
+ indexInfo->ii_Expressions = lappend(indexInfo->ii_Expressions,
+ node);
+ }
+ }
+
+ indexInfo->ii_IndexAttrGeneratedNumbers[attn] = attnum;
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cb811520c29..ce669028f79 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -8652,6 +8652,33 @@ ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName,
*/
RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
}
+ else
+ {
+ Assert(attgenerated == ATTRIBUTE_GENERATED_VIRTUAL);
+
+ /*
+ * Changing virtual generated column generation expression does not
+ * require table rewrite. However, if any index is built on top of it,
+ * table rewrite is necessary.
+ * Record enough information to let us rebuild index after rewrite in
+ * Phase3.
+ */
+ RememberAllDependentForRebuilding(tab, AT_SetExpression, rel, attnum, colName);
+
+ if (tab->changedIndexOids != NIL)
+ {
+ rewrite = true;
+
+ /*
+ * Clear all the missing values if we're rewriting the table, since
+ * this renders them pointless.
+ */
+ RelationClearMissing(rel);
+
+ /* make sure we don't conflict with later attribute modifications */
+ CommandCounterIncrement();
+ }
+ }
/*
* Drop the dependency records of the GENERATED expression, in particular
@@ -14804,6 +14831,24 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
*/
RememberAllDependentForRebuilding(tab, AT_AlterColumnType, rel, attnum, colName);
+ /*
+ * Tell phase3 do table rewrite if there are any index based on virtual
+ * generated colum.
+ */
+ if (attTup->attgenerated == ATTRIBUTE_GENERATED_VIRTUAL &&
+ tab->changedIndexOids != NIL)
+ {
+ Relation newrel;
+
+ newrel = table_open(RelationGetRelid(rel), NoLock);
+ RelationClearMissing(newrel);
+ relation_close(newrel, NoLock);
+ /* make sure we don't conflict with later attribute modifications */
+ CommandCounterIncrement();
+
+ 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
@@ -20589,6 +20634,7 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel)
AttrMap *attmap;
bool found = false;
Oid constraintOid;
+ bool parent_idx_virtual;
/*
* Ignore indexes in the partitioned table other than partitioned
@@ -20602,9 +20648,19 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel)
/* construct an indexinfo to compare existing indexes against */
info = BuildIndexInfo(idxRel);
+ parent_idx_virtual = IsIndexOverVirtualGenerated(info);
attmap = build_attrmap_by_name(RelationGetDescr(attachrel),
RelationGetDescr(rel),
false);
+
+ /*
+ * The attach partition don't have index, but parent have index over
+ * virtual generated column. We need ensure generated expression on
+ * parent that index was based on it match with attach partition.
+ */
+ if (attachRelIdxs == NIL && parent_idx_virtual)
+ check_generated_indexattrs(info, rel, attachrel, attmap, true);
+
constraintOid = get_relation_idx_constraint_oid(RelationGetRelid(rel), idx);
/*
@@ -20663,6 +20719,22 @@ AttachPartitionEnsureIndexes(List **wqueue, Relation rel, Relation attachrel)
CommandCounterIncrement();
break;
}
+ else
+ {
+ bool attach_idx_virtual;
+ attach_idx_virtual = IsIndexOverVirtualGenerated(attachInfos[i]);
+
+ /* should fail. different index definition cannot merge */
+ if (attach_idx_virtual || parent_idx_virtual)
+ ereport(ERROR,
+ errcode(ERRCODE_WRONG_OBJECT_TYPE),
+ errmsg("cannot attach table \"%s\" as partition of partitioned table \"%s\"",
+ RelationGetRelationName(attachrel),
+ RelationGetRelationName(rel)),
+ errdetail("The index definition of partitioned table \"%s\" does not match table \"%s\"",
+ RelationGetRelationName(rel),
+ RelationGetRelationName(attachrel)));
+ }
}
/*
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index afcf54169c3..a414bfd6252 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -1703,6 +1703,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
Form_pg_am amrec;
oidvector *indcollation;
oidvector *indclass;
+ int2vector *indgenkey;
IndexStmt *index;
List *indexprs;
ListCell *indexpr_item;
@@ -1710,6 +1711,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
int keyno;
Oid keycoltype;
Datum datum;
+ Datum indgenkeyDatum;
bool isnull;
if (constraintOid)
@@ -1745,6 +1747,11 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
datum = SysCacheGetAttrNotNull(INDEXRELID, ht_idx, Anum_pg_index_indclass);
indclass = (oidvector *) DatumGetPointer(datum);
+ /* Extract indattrgenerated from the pg_index tuple */
+ indgenkeyDatum = SysCacheGetAttrNotNull(INDEXRELID, ht_idx,
+ Anum_pg_index_indattrgenerated);
+ indgenkey = (int2vector *) DatumGetPointer(indgenkeyDatum);
+
/* Begin building the IndexStmt */
index = makeNode(IndexStmt);
index->relation = heapRel;
@@ -1876,13 +1883,29 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx,
{
IndexElem *iparam;
AttrNumber attnum = idxrec->indkey.values[keyno];
+ AttrNumber gennum = indgenkey->values[keyno];
Form_pg_attribute attr = TupleDescAttr(RelationGetDescr(source_idx),
keyno);
int16 opt = source_idx->rd_indoption[keyno];
iparam = makeNode(IndexElem);
- if (AttributeNumberIsValid(attnum))
+ if (AttributeNumberIsValid(gennum))
+ {
+ /*
+ * 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;
+
+ virtual_attname = get_attname(indrelid, gennum, false);
+ keycoltype = get_atttype(indrelid, gennum);
+
+ iparam->name = virtual_attname;
+ iparam->expr = NULL;
+ }
+ else if (AttributeNumberIsValid(attnum))
{
/* Simple index column */
char *attname;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 3d6e6bdbfd2..98fd300c35a 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,32 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
appendStringInfoString(&buf, sep);
sep = ", ";
- if (attnum != 0)
+ /*
+ * We need firtst check index over virtual generated column then simple
+ * index column. Because virtual generted column can reference another
+ * column.
+ */
+ if (AttributeNumberIsValid(gennum))
+ {
+ 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);
+
+ /*
+ * We alerady printed the indexing generated column, therefore the
+ * associated generation expression should not printed.
+ */
+ if (!AttributeNumberIsValid(attnum))
+ indexpr_item = lnext(indexprs, indexpr_item);
+ }
+ else if (attnum != 0)
{
/* Simple index column */
char *attname;
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 4daa8bef5ee..a7e93f3a107 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -126,6 +126,7 @@ extern IndexInfo *BuildIndexInfo(Relation index);
extern IndexInfo *BuildDummyIndexInfo(Relation index);
+extern bool IsIndexOverVirtualGenerated(const IndexInfo *info);
extern bool CompareIndexInfo(const IndexInfo *info1, const IndexInfo *info2,
const Oid *collations1, const Oid *collations2,
const Oid *opfamilies1, const Oid *opfamilies2,
@@ -175,6 +176,11 @@ extern void RestoreReindexState(const void *reindexstate);
extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid);
+extern void check_generated_indexattrs(const IndexInfo *rel_idx_info,
+ Relation rel,
+ Relation childrel,
+ const AttrMap *attmap,
+ bool is_attach);
/*
* itemptr_encode - Encode ItemPointer as int64/int8
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index 731d3938169..d00da9c4642 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 e107d6e5f81..7cef81050b5 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -176,6 +176,14 @@ typedef struct IndexInfo
*/
AttrNumber ii_IndexAttrNumbers[INDEX_MAX_KEYS];
+ /*
+ * Virtual generated column attribute numbers of the underlying relation
+ * used as index keys. A value of zero indicates either an expression or a
+ * non-virtual generated column. Note: Virtual generated columns cannot be
+ * used as index key included columns.
+ */
+ AttrNumber ii_IndexAttrGeneratedNumbers[INDEX_MAX_KEYS];
+
/* expr trees for expression entries, or NIL if none */
List *ii_Expressions; /* list of Expr */
/* exec state for expressions, or NIL if none */
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index 69805d4b9ec..4b1ccb7c072 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2690,6 +2690,17 @@ SELECT * FROM t5 ORDER BY c ASC, a ASC;
3 | d1 | d1
(3 rows)
+CREATE INDEX t5_idx1 ON t5 USING btree((c COLLATE "POSIX"));
+CREATE INDEX t5_idx2 ON t5 USING btree((c));
+SELECT indexrelid::regclass, indrelid::regclass, indnatts, indattrgenerated, indcollation[0]::regcollation
+FROM pg_index
+WHERE indrelid = 't5'::regclass ORDER BY indexrelid;
+ indexrelid | indrelid | indnatts | indattrgenerated | indcollation
+------------+----------+----------+------------------+--------------
+ t5_idx1 | t5 | 1 | 3 | "POSIX"
+ t5_idx2 | t5 | 1 | 3 | "C"
+(2 rows)
+
-- cleanup
RESET search_path;
SET client_min_messages TO warning;
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 aca6347babe..3ce22db95fc 100644
--- a/src/test/regress/expected/generated_virtual.out
+++ b/src/test/regress/expected/generated_virtual.out
@@ -739,30 +739,249 @@ ERROR: primary keys on virtual generated columns are not supported
--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 TABLE gtestparted (b integer, c integer,a integer GENERATED ALWAYS AS (c+1))PARTITION BY RANGE (b);
+CREATE TABLE gtestpart1 (b integer, c integer, a integer GENERATED ALWAYS AS (c));
+CREATE TABLE gtestpart2 (b integer, a integer GENERATED ALWAYS AS (c+1), c integer);
+CREATE TABLE gtestpart3 (b integer, c integer, a integer GENERATED ALWAYS AS (c));
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart3 for values from (1) to (10);
+--error: index cannot be created if the partitioned table and its partitions have differing generation expressions.
+CREATE INDEX gtestparted_a_idx_error on gtestparted(a); --error
+ERROR: cannot create index on partitioned table "gtestparted"
+DETAIL: The index definition of partitioned table "gtestparted" does not match table "gtestpart3"
+ALTER TABLE gtestparted DETACH PARTITION gtestpart3;
+CREATE INDEX gtestpart1_a_idx on gtestpart1(a);
+CREATE INDEX gtestpart2_a_idx on gtestpart2(a);
+CREATE INDEX gtestpart2_a_idx_copy on gtestpart2(a);
+CREATE INDEX gtestparted_a_idx on gtestparted(a);
+--error: index cannot be created if the partitioned table and its partitions have differing generation expressions.
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart1 for values from (1) to (10); --error
+ERROR: cannot attach table "gtestpart1" as partition of partitioned table "gtestparted"
+DETAIL: The index definition of partitioned table "gtestparted" does not match table "gtestpart1"
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart2 for values from (1) to (10); --ok
+SELECT * FROM pg_partition_tree('gtestparted_a_idx'::regclass);
+ relid | parentrelid | isleaf | level
+-------------------+-------------------+--------+-------
+ gtestparted_a_idx | | f | 0
+ gtestpart2_a_idx | gtestparted_a_idx | t | 1
+(2 rows)
+
+ALTER INDEX gtestparted_a_idx ATTACH PARTITION gtestpart2_a_idx; --ok
+ALTER INDEX gtestparted_a_idx ATTACH PARTITION gtestpart2_a_idx_copy; --error
+ERROR: cannot attach index "gtestpart2_a_idx_copy" as a partition of index "gtestparted_a_idx"
+DETAIL: Another index is already attached for partition "gtestpart2".
+CREATE INDEX gtestparted_a_idx_1 on gtestparted(a);
+--now index gtestpart2_a_idx_copy should attach to the partition tree.
+SELECT * FROM pg_partition_tree('gtestparted_a_idx_1'::regclass);
+ relid | parentrelid | isleaf | level
+-----------------------+---------------------+--------+-------
+ gtestparted_a_idx_1 | | f | 0
+ gtestpart2_a_idx_copy | gtestparted_a_idx_1 | t | 1
+(2 rows)
+
+--test create table like copy indexes
+CREATE TABLE gtestparted_like (LIKE gtestparted including all);
+\d gtestparted_like
+ Table "generated_virtual_tests.gtestparted_like"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+-----------------------------
+ b | integer | | |
+ c | integer | | |
+ a | integer | | | generated always as (c + 1)
+Indexes:
+ "gtestparted_like_a_idx" btree (a)
+ "gtestparted_like_a_idx1" btree (a)
+
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2),
+ c int GENERATED ALWAYS AS (11),
+ d int GENERATED ALWAYS AS (a *3),
+ e int4range GENERATED ALWAYS AS (int4range(a, a+10)),
+ e1 int8range GENERATED ALWAYS AS (int8range(a, a+10)),
+ f int GENERATED ALWAYS AS (a),
+ f1 oid GENERATED ALWAYS AS (tableoid));
+--index can not based on tableoid column
+CREATE INDEX gtest22c_error ON gtest22c (b, f1);
+ERROR: index creation on system columns is not supported
+CREATE INDEX gtest22c_error ON gtest22c (f1);
+ERROR: index creation on system columns is not supported
+ALTER TABLE gtest22c DROP COLUMN f1;
+--index include columns are not supported
+-- CREATE INDEX gtest22c_idx1_inc ON gtest22c USING btree(a) include (b,c);
+-- CREATE INDEX gtest22c_idx1_inc ON gtest22c USING btree(a) include (f);
+--Other index access methods are supported
+CREATE INDEX gtest22c_b_idx ON gtest22c USING btree(b, c);
+CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d);
+CREATE INDEX gtest22c_e_e1_idx ON gtest22c USING gist(e, e1);
+CREATE INDEX gtest22c_e1_idx ON gtest22c USING spgist(e1);
--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;
+\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))
+ e1 | int8range | | | generated always as (int8range(a::bigint, (a + 10)::bigint))
+ f | integer | | | generated always as (a)
+Indexes:
+ "gtest22c_b_idx" btree (b, c)
+ "gtest22c_d_idx" hash (d)
+ "gtest22c_e1_idx" spgist (e1)
+ "gtest22c_e_e1_idx" gist (e, e1)
+
+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 | e1 | f
+---+---+----+---+--------+--------+---
+ 2 | 4 | 11 | 6 | [2,12) | [2,12) | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE b = 4 and c = 11;
+ QUERY PLAN
+---------------------------------------------
+ Index Scan using gtest22c_b_idx on gtest22c
+ Index Cond: ((a * 2) = 4)
+(2 rows)
+
+SELECT * FROM gtest22c WHERE b = 4 and c = 11;
+ a | b | c | d | e | e1 | f
+---+---+----+---+--------+--------+---
+ 2 | 4 | 11 | 6 | [2,12) | [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 | e1 | f
+---+---+----+---+--------+--------+---
+ 2 | 4 | 11 | 6 | [2,12) | [2,12) | 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT count(*) FROM gtest22c WHERE e @> 12 and e1 @> 12::bigint;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Index Scan using gtest22c_e_e1_idx on gtest22c
+ Index Cond: ((int4range(a, (a + 10)) @> 12) AND (int8range((a)::bigint, ((a + 10))::bigint) @> '12'::bigint))
+(3 rows)
+
+SELECT count(*) from gtest22c where e @> 12 and e1 @> 12::bigint;
+ count
+-------
+ 2
+(1 row)
+
+EXPLAIN (COSTS OFF) SELECT count(*) FROM gtest22c WHERE e1 @> 12::bigint;
+ QUERY PLAN
+----------------------------------------------------------------------------------
+ Aggregate
+ -> Index Scan using gtest22c_e1_idx on gtest22c
+ Index Cond: (int8range((a)::bigint, ((a + 10))::bigint) @> '12'::bigint)
+(3 rows)
+
+SELECT count(*) from gtest22c where e1 @> 12::bigint;
+ count
+-------
+ 2
+(1 row)
+
+--column drop then the index over that column should also being dropped
+ALTER TABLE gtest22c DROP COLUMN e;
+\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)
+ e1 | int8range | | | generated always as (int8range(a::bigint, (a + 10)::bigint))
+ f | integer | | | generated always as (a)
+Indexes:
+ "gtest22c_b_idx" btree (b, c)
+ "gtest22c_d_idx" hash (d)
+ "gtest22c_e1_idx" spgist (e1)
+
--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;
+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 | e1 | 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;
+--test index over gin and brin index
+RESET enable_bitmapscan;
+CREATE TABLE t2(j jsonb, j1 jsonb GENERATED ALWAYS AS (j || '{"hello": "world"}'),
+ f1 interval, f2 interval GENERATED ALWAYS AS ( f1 + interval '1 day'));
+INSERT INTO t2(j, f1) SELECT i::text::jsonb, (i || ' days')::interval FROM generate_series(100, 240) s(i);
+INSERT INTO t2(f1) VALUES ('-infinity'), ('infinity');
+CREATE INDEX ON t2 USING brin (f1 interval_minmax_multi_ops, f2 interval_minmax_multi_ops) WITH (pages_per_range=1);
+CREATE INDEX t_gin_j1 ON t2 USING gin (j1);
+EXPLAIN(COSTS OFF) SELECT count(*) FROM t2 WHERE j1 @> '[{"hello":"world"}]';
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on t2
+ Recheck Cond: ((j || '{"hello": "world"}'::jsonb) @> '[{"hello": "world"}]'::jsonb)
+ -> Bitmap Index Scan on t_gin_j1
+ Index Cond: ((j || '{"hello": "world"}'::jsonb) @> '[{"hello": "world"}]'::jsonb)
+(5 rows)
+
+SELECT count(*) FROM t2 WHERE j1 @> '[{"hello":"world"}]';
+ count
+-------
+ 141
+(1 row)
+
+EXPLAIN(COSTS OFF) SELECT * FROM t2 WHERE f1 > '30 years'::interval AND f2 > '30 years'::interval;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------
+ Bitmap Heap Scan on t2
+ Recheck Cond: ((f1 > '@ 30 years'::interval) AND ((f1 + '@ 1 day'::interval) > '@ 30 years'::interval))
+ -> Bitmap Index Scan on t2_f1_f2_idx
+ Index Cond: ((f1 > '@ 30 years'::interval) AND ((f1 + '@ 1 day'::interval) > '@ 30 years'::interval))
+(4 rows)
+
+SELECT * FROM t2 WHERE f1 > '30 years'::interval AND f2 > '30 years'::interval;
+ j | j1 | f1 | f2
+---+----+----------+----------
+ | | infinity | infinity
+(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);
@@ -1292,6 +1511,7 @@ ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello2');
DROP STATISTICS gtest31_2_stat;
CREATE INDEX gtest31_2_y_idx ON gtest31_2(((y).b));
ALTER TABLE gtest31_1 ALTER COLUMN b SET EXPRESSION AS ('hello3');
+ERROR: cannot alter table "gtest31_1" because column "gtest31_2.y" uses its row type
DROP TABLE gtest31_1, gtest31_2;
-- Check it for a partitioned table, too
CREATE TABLE gtest31_1 (a int, b text GENERATED ALWAYS AS ('hello') VIRTUAL, c text) PARTITION BY LIST (a);
@@ -1636,3 +1856,17 @@ select 1 from gtest32 t1 where exists
(1 row)
drop table gtest32;
+-- sanity check of system catalog
+-- If the index is based on a virtual generated column, then the corresponding
+-- attribute's attgenerated should be 'v'.
+select pi.indrelid::regclass, pa.attnum,
+ pa.attname,
+ pa.attgenerated
+from pg_index pi, unnest(indattrgenerated) sub(a), pg_attribute pa
+where 0 <> a
+and pa.attrelid = pi.indrelid and pa.attnum = sub.a
+and (pa.attgenerated <> 'v' or pi.indnatts <> pi.indnkeyatts);
+ indrelid | attnum | attname | attgenerated
+----------+--------+---------+--------------
+(0 rows)
+
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index dbc190227d0..7b2725f8ba5 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -997,6 +997,11 @@ INSERT INTO t5 (a, b) values (1, 'D1'), (2, 'D2'), (3, 'd1');
-- rewriting.)
SELECT * FROM t5 ORDER BY c ASC, a ASC;
+CREATE INDEX t5_idx1 ON t5 USING btree((c COLLATE "POSIX"));
+CREATE INDEX t5_idx2 ON t5 USING btree((c));
+SELECT indexrelid::regclass, indrelid::regclass, indnatts, indattrgenerated, indcollation[0]::regcollation
+FROM pg_index
+WHERE indrelid = 't5'::regclass ORDER BY indexrelid;
-- cleanup
RESET search_path;
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 ba19bc4c701..76a6b70739e 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;
@@ -392,32 +390,115 @@ 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 TABLE gtestparted (b integer, c integer,a integer GENERATED ALWAYS AS (c+1))PARTITION BY RANGE (b);
+CREATE TABLE gtestpart1 (b integer, c integer, a integer GENERATED ALWAYS AS (c));
+CREATE TABLE gtestpart2 (b integer, a integer GENERATED ALWAYS AS (c+1), c integer);
+CREATE TABLE gtestpart3 (b integer, c integer, a integer GENERATED ALWAYS AS (c));
+
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart3 for values from (1) to (10);
+--error: index cannot be created if the partitioned table and its partitions have differing generation expressions.
+CREATE INDEX gtestparted_a_idx_error on gtestparted(a); --error
+ALTER TABLE gtestparted DETACH PARTITION gtestpart3;
+
+CREATE INDEX gtestpart1_a_idx on gtestpart1(a);
+CREATE INDEX gtestpart2_a_idx on gtestpart2(a);
+CREATE INDEX gtestpart2_a_idx_copy on gtestpart2(a);
+CREATE INDEX gtestparted_a_idx on gtestparted(a);
+
+--error: index cannot be created if the partitioned table and its partitions have differing generation expressions.
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart1 for values from (1) to (10); --error
+ALTER TABLE gtestparted ATTACH PARTITION gtestpart2 for values from (1) to (10); --ok
+
+SELECT * FROM pg_partition_tree('gtestparted_a_idx'::regclass);
+ALTER INDEX gtestparted_a_idx ATTACH PARTITION gtestpart2_a_idx; --ok
+ALTER INDEX gtestparted_a_idx ATTACH PARTITION gtestpart2_a_idx_copy; --error
+CREATE INDEX gtestparted_a_idx_1 on gtestparted(a);
+--now index gtestpart2_a_idx_copy should attach to the partition tree.
+SELECT * FROM pg_partition_tree('gtestparted_a_idx_1'::regclass);
+
+--test create table like copy indexes
+CREATE TABLE gtestparted_like (LIKE gtestparted including all);
+\d gtestparted_like
+
+CREATE TABLE gtest22c (a int, b int GENERATED ALWAYS AS (a * 2),
+ c int GENERATED ALWAYS AS (11),
+ d int GENERATED ALWAYS AS (a *3),
+ e int4range GENERATED ALWAYS AS (int4range(a, a+10)),
+ e1 int8range GENERATED ALWAYS AS (int8range(a, a+10)),
+ f int GENERATED ALWAYS AS (a),
+ f1 oid GENERATED ALWAYS AS (tableoid));
+--index can not based on tableoid column
+CREATE INDEX gtest22c_error ON gtest22c (b, f1);
+CREATE INDEX gtest22c_error ON gtest22c (f1);
+ALTER TABLE gtest22c DROP COLUMN f1;
+
+--index include columns are not supported
+-- CREATE INDEX gtest22c_idx1_inc ON gtest22c USING btree(a) include (b,c);
+-- CREATE INDEX gtest22c_idx1_inc ON gtest22c USING btree(a) include (f);
+
+--Other index access methods are supported
+CREATE INDEX gtest22c_b_idx ON gtest22c USING btree(b, c);
+CREATE INDEX gtest22c_d_idx ON gtest22c USING hash(d);
+CREATE INDEX gtest22c_e_e1_idx ON gtest22c USING gist(e, e1);
+CREATE INDEX gtest22c_e1_idx ON gtest22c USING spgist(e1);
+
--CREATE INDEX gtest22c_expr_idx ON gtest22c ((b * 3));
--CREATE INDEX gtest22c_pred_idx ON gtest22c (a) WHERE b > 0;
---\d gtest22c
+\d gtest22c
+
+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 b = 4 and c = 11;
+SELECT * FROM gtest22c WHERE b = 4 and c = 11;
+
+EXPLAIN (COSTS OFF) SELECT * FROM gtest22c WHERE d = 6;
+SELECT * FROM gtest22c WHERE d = 6;
+
+EXPLAIN (COSTS OFF) SELECT count(*) FROM gtest22c WHERE e @> 12 and e1 @> 12::bigint;
+SELECT count(*) from gtest22c where e @> 12 and e1 @> 12::bigint;
+
+EXPLAIN (COSTS OFF) SELECT count(*) FROM gtest22c WHERE e1 @> 12::bigint;
+SELECT count(*) from gtest22c where e1 @> 12::bigint;
+
+--column drop then the index over that column should also being dropped
+ALTER TABLE gtest22c DROP COLUMN e;
+\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;
+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;
+
+--test index over gin and brin index
+RESET enable_bitmapscan;
+CREATE TABLE t2(j jsonb, j1 jsonb GENERATED ALWAYS AS (j || '{"hello": "world"}'),
+ f1 interval, f2 interval GENERATED ALWAYS AS ( f1 + interval '1 day'));
+INSERT INTO t2(j, f1) SELECT i::text::jsonb, (i || ' days')::interval FROM generate_series(100, 240) s(i);
+INSERT INTO t2(f1) VALUES ('-infinity'), ('infinity');
+CREATE INDEX ON t2 USING brin (f1 interval_minmax_multi_ops, f2 interval_minmax_multi_ops) WITH (pages_per_range=1);
+CREATE INDEX t_gin_j1 ON t2 USING gin (j1);
+
+EXPLAIN(COSTS OFF) SELECT count(*) FROM t2 WHERE j1 @> '[{"hello":"world"}]';
+SELECT count(*) FROM t2 WHERE j1 @> '[{"hello":"world"}]';
+
+EXPLAIN(COSTS OFF) SELECT * FROM t2 WHERE f1 > '30 years'::interval AND f2 > '30 years'::interval;
+SELECT * FROM t2 WHERE f1 > '30 years'::interval AND f2 > '30 years'::interval;
+
+RESET enable_seqscan;
+RESET enable_bitmapscan;
-- foreign keys
CREATE TABLE gtest23a (x int PRIMARY KEY, y int);
@@ -868,3 +949,14 @@ select 1 from gtest32 t1 where exists
(select 1 from gtest32 t2 where t1.a > t2.a and t2.b = 2);
drop table gtest32;
+
+-- sanity check of system catalog
+-- If the index is based on a virtual generated column, then the corresponding
+-- attribute's attgenerated should be 'v'.
+select pi.indrelid::regclass, pa.attnum,
+ pa.attname,
+ pa.attgenerated
+from pg_index pi, unnest(indattrgenerated) sub(a), pg_attribute pa
+where 0 <> a
+and pa.attrelid = pi.indrelid and pa.attnum = sub.a
+and (pa.attgenerated <> 'v' or pi.indnatts <> pi.indnkeyatts);
--
2.34.1
