On Thu, Apr 24, 2025 at 12:45 AM jian he <jian.universal...@gmail.com> wrote:
> hi. > The following is a review of version 17. > > ATExecSetIndexVisibility > if (indexForm->indisvisible != visible) > { > indexForm->indisvisible = visible; > CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple); > CacheInvalidateRelcache(heapRel); > InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0); > CommandCounterIncrement(); > } > I am slightly confused. if we already used > CommandCounterIncrement(); > then we don't need CacheInvalidateRelcache? > > > Thank you for this catch. I misunderstood the behavior of the two and was performing both to avoid inconsistency between state within a transaction and cross session, but as you pointed out CommandCounterIncrement() helps achieve both. Updated. doc/src/sgml/ref/alter_index.sgml > <para> > <command>ALTER INDEX</command> changes the definition of an existing > index. > There are several subforms described below. Note that the lock level > required > may differ for each subform. An <literal>ACCESS EXCLUSIVE</literal> > lock is held > unless explicitly noted. When multiple subcommands are listed, the lock > held will be the strictest one required from any subcommand. > > per the above para, we need mention that ALTER INDEX SET > INVISIBLE|INVISIBLE > only use ShareUpdateExclusiveLock? > > I wasn't sure at first where to add the note about ShareUpdateExclusiveLock. But it looks like we have a precedent from RENAME in doc/src/sgml/ref/alter_index.sgml, so I have done the same for VISIBLE & INVISIBLE in doc/src/sgml/ref/alter_index.sgml as well. > index_create is called in several places, > most of the time, we use INDEX_CREATE_VISIBLE. > if we define it as INDEX_CREATE_INVISIBLE rather than INDEX_CREATE_VISIBLE > then argument flags required code changes would be less, (i didn't try > it myself) > Looks like the only change we would save is the one in src/backend/catalog/toasting.c. Rest of the code change/diffs would still be needed IIUC (if I understand correctly). This approach felt a bit ergonomical, hence opted for it, but happy to update. Let me know. > Similar to get_index_isclustered, > We can place GetIndexVisibility in > src/backend/utils/cache/lsyscache.c, > make it an extern function, so others can use it; > to align with other function names, > maybe rename it as get_index_visibility. > > I was a bit torn on this one and figured I wouldn't introduce it as it could be a bit of premature optimization, until there were more use cases (or maybe one more). Plus, I figured the next time we need this info, we could expose a more public function like get_index_visibility (given N=2, N being the number of callers). However, given you mentioned and spotted this as well, I have introduced get_index_visibility in the new patch now. > create index v2_idx on v1(data) visible; > is allowed, > doc/src/sgml/ref/create_index.sgml > <synopsis> section need to change to > [ VISIBLE | INVISIBLE ] > > ? > Updated to match the same pattern as the one in doc/src/sgml/ref/alter_index.sgml. Thank you for the feedback. I have also updated the feedback from [1] as well. Few extra notes: - Attached v18 - Rebased against master - Updated the commit message - Updated the target remote version to now be fout->remoteVersion >= 190000 - Using a UNION ALL query to show all indexes from part_tbl partitioned tables in the specs as noted in [1]. The query suggested in [1] wasn't encompassing all the indexes, hence the UNION ALL for WHERE i.indrelid = 'part_tbl'::regclass::oid. [1] https://www.postgresql.org/message-id/cacjufxfs_m7ngvfiz-duutawb7rqxrmo97wc5zezkw2zsmq...@mail.gmail.com Thank you Shayon
From ee130c6ec2555ecdc2cb8e3eda360017507c1e4f Mon Sep 17 00:00:00 2001 From: Shayon Mukherjee <shayonj@gmail.com> Date: Sun, 27 Apr 2025 16:37:41 -0400 Subject: [PATCH v18] Introduce the ability to set index visibility using ALTER INDEX This patch introduces the ability to make an index INVISIBLE ( or VISIBLE ) to the planner, making the index not eligible for planning but will continue to be maintained when the underlying data changes. This behavior is accomplished by introducing new grammar ALTER INDEX ... VISIBLE|INVISIBLE and CREATE INDEX ... VISIBLE|INVISIBLE. Additionally, there is also support for force_invisible_index GUC parameter that forces the planner to use invisible indexes. This is useful for testing and validating index behavior without changing their visibility state. Discussion: https://www.postgresql.org/message-id/flat/EF2313B8-A017-4869-9B7F-A24EDD8795DE%40gmail.com#dbe65017ffa7b65a4f3f29e64ed2fce5 --- doc/src/sgml/catalogs.sgml | 11 + doc/src/sgml/config.sgml | 16 + doc/src/sgml/indices.sgml | 21 + doc/src/sgml/ref/alter_index.sgml | 40 + doc/src/sgml/ref/create_index.sgml | 29 + src/backend/bootstrap/bootparse.y | 2 + src/backend/catalog/index.c | 31 +- src/backend/catalog/toasting.c | 2 +- src/backend/commands/indexcmds.c | 4 + src/backend/commands/tablecmds.c | 90 +- src/backend/optimizer/util/plancat.c | 30 +- src/backend/parser/gram.y | 56 +- src/backend/parser/parse_utilcmd.c | 3 + src/backend/utils/adt/ruleutils.c | 4 + src/backend/utils/cache/relcache.c | 1 + src/backend/utils/misc/guc_tables.c | 10 + src/backend/utils/misc/postgresql.conf.sample | 1 + src/bin/pg_dump/pg_dump.c | 21 +- src/bin/pg_dump/pg_dump.h | 2 + src/bin/pg_dump/t/002_pg_dump.pl | 29 + src/bin/psql/describe.c | 15 +- src/bin/psql/tab-complete.in.c | 6 +- src/include/catalog/index.h | 2 +- src/include/catalog/pg_index.h | 2 + src/include/nodes/parsenodes.h | 4 + src/include/optimizer/optimizer.h | 5 + src/include/parser/kwlist.h | 2 + src/include/utils/guc_hooks.h | 1 + src/include/utils/lsyscache.h | 1 + src/interfaces/ecpg/test/regression.diffs | 0 src/interfaces/ecpg/test/regression.out | 55 ++ src/test/modules/test_ddl_deparse/Makefile | 3 +- .../test_ddl_deparse/expected/alter_index.out | 29 + src/test/modules/test_ddl_deparse/meson.build | 1 + .../test_ddl_deparse/sql/alter_index.sql | 18 + .../test_ddl_deparse/test_ddl_deparse.c | 6 + src/test/regress/expected/create_index.out | 827 ++++++++++++++++++ src/test/regress/sql/create_index.sql | 349 ++++++++ 38 files changed, 1703 insertions(+), 26 deletions(-) create mode 100644 src/interfaces/ecpg/test/regression.diffs create mode 100644 src/interfaces/ecpg/test/regression.out create mode 100644 src/test/modules/test_ddl_deparse/expected/alter_index.out create mode 100644 src/test/modules/test_ddl_deparse/sql/alter_index.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index cbd4e40a32..3307df883b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4618,6 +4618,17 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l partial index. </para></entry> </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>indisvisible</structfield> <type>bool</type> + </para> + <para> + If true, the index is currently visible to the planner and may be used for queries. + If false, the index is invisible and may not be used for queries, + but is still updated when the table is modified. Default is true. + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 14661ac2cc..a009a16630 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -5814,6 +5814,22 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class=" </listitem> </varlistentry> + <varlistentry id="guc-force-invisible-index" xreflabel="use_invisible_index"> + <term><varname>use_invisible_index</varname> (<type>boolean</type>) + <indexterm> + <primary><varname>use_invisible_index</varname> configuration parameter</primary> + </indexterm> + </term> + <listitem> + <para> + Forces the query planner to consider indexes that have been marked as invisible using + <command>ALTER INDEX ... INVISIBLE</command>. This parameter is useful for selective + use of invisible indexes in specific application contexts. The default + is <literal>off</literal>. + </para> + </listitem> + </varlistentry> + </variablelist> </sect2> <sect2 id="runtime-config-query-constants"> diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 9c4f76abf0..97bb599a05 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1673,6 +1673,27 @@ CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y"); <productname>PostgreSQL</productname> developers to examine the issue. </para> </listitem> + + <listitem> + <para> + Invisible indexes provide a convenient way to experiment with indexes + without needing to fully drop and recreate them. You can create a new index as + invisible with <command>CREATE INDEX ... INVISIBLE</command> or mark + an existing index invisible with <command>ALTER INDEX ... INVISIBLE</command>. + When an index is invisible, the planner will ignore it by default. + To test the index's effect on performance, set the + <varname>use_invisible_index</varname> parameter to <literal>on</literal>. + This allows you to compare query performance with and without the index + before making it visible to all queries with + <command>ALTER INDEX ... VISIBLE</command>. + </para> + <para> + Similarly, before dropping an existing index that appears unused, + consider marking it invisible to verify that query performance doesn't + degrade. Check <structname>pg_stat_user_indexes</structname>.<structfield>idx_scan</structfield> + to identify potentially unused indexes. + </para> + </listitem> </itemizedlist> </sect1> </chapter> diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index 1d42d05d85..08bf400099 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -31,6 +31,7 @@ ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> ALTE SET STATISTICS <replaceable class="parameter">integer</replaceable> ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> [ OWNED BY <replaceable class="parameter">role_name</replaceable> [, ... ] ] SET TABLESPACE <replaceable class="parameter">new_tablespace</replaceable> [ NOWAIT ] +ALTER INDEX [ IF EXISTS ] <replaceable class="parameter">name</replaceable> { VISIBLE | INVISIBLE } </synopsis> </refsynopsisdiv> @@ -159,6 +160,31 @@ ALTER INDEX ALL IN TABLESPACE <replaceable class="parameter">name</replaceable> </listitem> </varlistentry> + <varlistentry> + <term><literal>VISIBLE</literal></term> + <listitem> + <para> + Make the specified index visible. The index will be used for query planning. + This operation acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>INVISIBLE</literal></term> + <listitem> + <para> + Make the specified index invisible. The index will not be used for queries. + This can be useful for testing query performance with and without specific + indexes. If performance degrades after making an index invisible, it can be easily + be made visible using <literal>VISIBLE</literal>. + </para> + <para> + This operation acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock. + </para> + </listitem> + </varlistentry> + </variablelist> </para> @@ -301,6 +327,20 @@ CREATE INDEX coord_idx ON measured (x, y, (z + t)); ALTER INDEX coord_idx ALTER COLUMN 3 SET STATISTICS 1000; </programlisting></para> + <para> + To make an index visible: +<programlisting> +ALTER INDEX idx_name VISIBLE; +</programlisting> + </para> + + <para> + To make an index invisible: +<programlisting> +ALTER INDEX idx_name INVISIBLE; +</programlisting> + </para> + </refsect1> <refsect1> diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 147a8f7587..539d06e3df 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -28,6 +28,7 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= [ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ] [ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ] [ WHERE <replaceable class="parameter">predicate</replaceable> ] + [ VISIBLE | INVISIBLE ] </synopsis> </refsynopsisdiv> @@ -380,6 +381,19 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class= </listitem> </varlistentry> + <varlistentry> + <term><literal>INVISIBLE</literal></term> + <listitem> + <para> + Creates the index in an invisible state (default visible). An invisible index is + not used by the query planner for queries, but it is still maintained + when the underlying table data changes. This can be useful when you want to create + an index without immediately impacting query planning, allowing you to make it + visible later at a more convenient time. The index can be made visible later + using <command>ALTER INDEX ... VISIBLE</command>. + </para> + </listitem> + </varlistentry> </variablelist> <refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters"> @@ -707,6 +721,14 @@ Indexes: partitioned index is a metadata only operation. </para> + <para> + When creating an index with the <literal>INVISIBLE</literal> option, the index + will be created but not used for query planning. This can be useful for + preparing an index in advance of its use, or for testing purposes. The index + will still be maintained as the table is modified, so it can be made visible + later without needing to be rebuilt. By default all new indexes are visible. + </para> + </refsect2> </refsect1> @@ -986,6 +1008,13 @@ SELECT * FROM points CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity); </programlisting></para> + <para> + To create an index on the table <literal>test_table</literal> with the default + name, but have it initially invisible: +<programlisting> +CREATE INDEX ON test_table (col1) INVISIBLE; +</programlisting> + </para> </refsect1> <refsect1> diff --git a/src/backend/bootstrap/bootparse.y b/src/backend/bootstrap/bootparse.y index 9833f52c1b..ea21511a20 100644 --- a/src/backend/bootstrap/bootparse.y +++ b/src/backend/bootstrap/bootparse.y @@ -303,6 +303,7 @@ Boot_DeclareIndexStmt: stmt->concurrent = false; stmt->if_not_exists = false; stmt->reset_default_tblspc = false; + stmt->isvisible = true; /* locks and races need not concern us in bootstrap mode */ relationId = RangeVarGetRelid(stmt->relation, NoLock, @@ -356,6 +357,7 @@ Boot_DeclareUniqueIndexStmt: stmt->concurrent = false; stmt->if_not_exists = false; stmt->reset_default_tblspc = false; + stmt->isvisible = true; /* locks and races need not concern us in bootstrap mode */ relationId = RangeVarGetRelid(stmt->relation, NoLock, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 739a92bdcc..2d8ceb60ce 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -119,7 +119,8 @@ static void UpdateIndexRelation(Oid indexoid, Oid heapoid, bool isexclusion, bool immediate, bool isvalid, - bool isready); + bool isready, + bool isvisible); static void index_update_stats(Relation rel, bool hasindex, double reltuples); @@ -571,7 +572,8 @@ UpdateIndexRelation(Oid indexoid, bool isexclusion, bool immediate, bool isvalid, - bool isready) + bool isready, + bool isvisible) { int2vector *indkey; oidvector *indcollation; @@ -649,6 +651,7 @@ UpdateIndexRelation(Oid indexoid, values[Anum_pg_index_indisready - 1] = BoolGetDatum(isready); values[Anum_pg_index_indislive - 1] = BoolGetDatum(true); values[Anum_pg_index_indisreplident - 1] = BoolGetDatum(false); + values[Anum_pg_index_indisvisible - 1] = BoolGetDatum(isvisible); values[Anum_pg_index_indkey - 1] = PointerGetDatum(indkey); values[Anum_pg_index_indcollation - 1] = PointerGetDatum(indcollation); values[Anum_pg_index_indclass - 1] = PointerGetDatum(indclass); @@ -714,6 +717,8 @@ UpdateIndexRelation(Oid indexoid, * already exists. * INDEX_CREATE_PARTITIONED: * create a partitioned index (table must be partitioned) + * INDEX_CREATE_VISIBLE: + * create the index as visible to query planner * constr_flags: flags passed to index_constraint_create * (only if INDEX_CREATE_ADD_CONSTRAINT is set) * allow_system_table_mods: allow table to be a system catalog @@ -759,6 +764,7 @@ index_create(Relation heapRelation, bool invalid = (flags & INDEX_CREATE_INVALID) != 0; bool concurrent = (flags & INDEX_CREATE_CONCURRENT) != 0; bool partitioned = (flags & INDEX_CREATE_PARTITIONED) != 0; + bool isvisible = (flags & INDEX_CREATE_VISIBLE) != 0; char relkind; TransactionId relfrozenxid; MultiXactId relminmxid; @@ -1042,13 +1048,15 @@ index_create(Relation heapRelation, * (Or, could define a rule to maintain the predicate) --Nels, Feb '92 * ---------------- */ + UpdateIndexRelation(indexRelationId, heapRelationId, parentIndexRelid, indexInfo, collationIds, opclassIds, coloptions, isprimary, is_exclusion, (constr_flags & INDEX_CONSTR_CREATE_DEFERRABLE) == 0, !concurrent && !invalid, - !concurrent); + !concurrent, + isvisible); /* * Register relcache invalidation on the indexes' heap relation, to @@ -1317,6 +1325,8 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, List *indexColNames = NIL; List *indexExprs = NIL; List *indexPreds = NIL; + Form_pg_index indexForm; + bits16 createFlags; indexRelation = index_open(oldIndexId, RowExclusiveLock); @@ -1344,6 +1354,9 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, Anum_pg_index_indoption); indcoloptions = (int2vector *) DatumGetPointer(colOptionDatum); + /* Get the visibility state of the original index */ + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + /* Fetch reloptions of index if any */ classTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(oldIndexId)); if (!HeapTupleIsValid(classTuple)) @@ -1435,6 +1448,16 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, stattargets[i].isnull = isnull; } + /* + * Determine the create flags for the new index. We always use SKIP_BUILD + * and CONCURRENT for concurrent reindexing. If the original index was + * visible, we also set the VISIBLE flag to maintain the same state in the + * new index. + */ + createFlags = INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT; + if (indexForm->indisvisible) + createFlags |= INDEX_CREATE_VISIBLE; + /* * Now create the new index. * @@ -1458,7 +1481,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId, indcoloptions->values, stattargets, reloptionsDatum, - INDEX_CREATE_SKIP_BUILD | INDEX_CREATE_CONCURRENT, + createFlags, 0, true, /* allow table to be a system catalog? */ false, /* is_internal? */ diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c index 874a8fc89a..7721d16fd3 100644 --- a/src/backend/catalog/toasting.c +++ b/src/backend/catalog/toasting.c @@ -325,7 +325,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid, BTREE_AM_OID, rel->rd_rel->reltablespace, collationIds, opclassIds, NULL, coloptions, NULL, (Datum) 0, - INDEX_CREATE_IS_PRIMARY, 0, true, true, NULL); + INDEX_CREATE_IS_PRIMARY | INDEX_CREATE_VISIBLE, 0, true, true, NULL); table_close(toast_rel, NoLock); diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c index 33c2106c17..693f23e8d6 100644 --- a/src/backend/commands/indexcmds.c +++ b/src/backend/commands/indexcmds.c @@ -1222,6 +1222,10 @@ DefineIndex(Oid tableId, flags |= INDEX_CREATE_PARTITIONED; if (stmt->primary) flags |= INDEX_CREATE_IS_PRIMARY; + if (stmt->isvisible) + flags |= INDEX_CREATE_VISIBLE; + else + flags &= ~INDEX_CREATE_VISIBLE; /* * If the table is partitioned, and recursion was declined but partitions diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 2705cf1133..adbf9ccd55 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -739,7 +739,7 @@ static List *GetParentedForeignKeyRefs(Relation partition); static void ATDetachCheckNoForeignKeyRefs(Relation partition); static char GetAttributeCompression(Oid atttypid, const char *compression); static char GetAttributeStorage(Oid atttypid, const char *storagemode); - +static void ATExecSetIndexVisibility(Relation rel, bool visible); /* ---------------------------------------------------------------- * DefineRelation @@ -4834,6 +4834,11 @@ AlterTableGetLockLevel(List *cmds) cmd_lockmode = ShareUpdateExclusiveLock; break; + case AT_SetIndexVisible: + case AT_SetIndexInvisible: + cmd_lockmode = ShareUpdateExclusiveLock; + break; + default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -5269,6 +5274,13 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* No command-specific prep needed */ pass = AT_PASS_MISC; break; + case AT_SetIndexVisible: + case AT_SetIndexInvisible: + ATSimplePermissions(cmd->subtype, rel, ATT_INDEX | ATT_PARTITIONED_INDEX); + ATSimpleRecursion(wqueue, rel, cmd, true, lockmode, context); + /* No command-specific prep needed */ + pass = AT_PASS_MISC; + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -5665,6 +5677,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, case AT_DetachPartitionFinalize: address = ATExecDetachPartitionFinalize(rel, ((PartitionCmd *) cmd->def)->name); break; + case AT_SetIndexVisible: + ATExecSetIndexVisibility(rel, true); + break; + case AT_SetIndexInvisible: + ATExecSetIndexVisibility(rel, false); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); @@ -6615,6 +6633,8 @@ alter_table_type_to_string(AlterTableType cmdtype) return "DROP COLUMN"; case AT_AddIndex: case AT_ReAddIndex: + case AT_SetIndexVisible: + case AT_SetIndexInvisible: return NULL; /* not real grammar */ case AT_AddConstraint: case AT_ReAddConstraint: @@ -15627,6 +15647,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, stmt->reset_default_tblspc = true; /* keep the index's comment */ stmt->idxcomment = GetComment(oldId, RelationRelationId, 0); + /* preserve the index's visibility status */ + stmt->isvisible = get_index_visibility(oldId); newcmd = makeNode(AlterTableCmd); newcmd->subtype = AT_ReAddIndex; @@ -15657,6 +15679,8 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd, indstmt->idxcomment = GetComment(indoid, RelationRelationId, 0); indstmt->reset_default_tblspc = true; + /* preserve the index's visibility status */ + indstmt->isvisible = get_index_visibility(indoid); cmd->subtype = AT_ReAddIndex; tab->subcmds[AT_PASS_OLD_INDEX] = @@ -22014,3 +22038,67 @@ GetAttributeStorage(Oid atttypid, const char *storagemode) return cstorage; } + +/* + * ATExecSetIndexVisibility + * Performs a catalog update to mark an index as visible or invisible in pg_index. + */ +static void +ATExecSetIndexVisibility(Relation rel, bool visible) +{ + Oid indexOid = RelationGetRelid(rel); + Oid heapOid; + Relation pg_index; + Relation heapRel; + HeapTuple indexTuple; + Form_pg_index indexForm; + + heapOid = IndexGetRelation(indexOid, false); + heapRel = table_open(heapOid, AccessShareLock); + pg_index = table_open(IndexRelationId, RowExclusiveLock); + + indexTuple = SearchSysCacheCopy1(INDEXRELID, ObjectIdGetDatum(indexOid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for index %u", indexOid); + + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + if (indexForm->indcheckxmin) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("cannot update index visibility while indcheckxmin is true"), + errhint("Wait for all transactions that might see inconsistent HOT chains to complete")); + + if (indexForm->indisvisible != visible) + { + indexForm->indisvisible = visible; + CatalogTupleUpdate(pg_index, &indexTuple->t_self, indexTuple); + + InvokeObjectPostAlterHook(IndexRelationId, indexOid, 0); + CommandCounterIncrement(); + } + + heap_freetuple(indexTuple); + table_close(pg_index, RowExclusiveLock); + table_close(heapRel, AccessShareLock); +} + +/* +* Get index visibility status from pg_index +*/ +bool +get_index_visibility(Oid indexOid) +{ + HeapTuple indexTuple; + Form_pg_index indexForm; + bool isvisible; + + indexTuple = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexOid)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for index %u", indexOid); + + indexForm = (Form_pg_index) GETSTRUCT(indexTuple); + isvisible = indexForm->indisvisible; + ReleaseSysCache(indexTuple); + + return isvisible; +} diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c index 59233b6473..6d938d3589 100644 --- a/src/backend/optimizer/util/plancat.c +++ b/src/backend/optimizer/util/plancat.c @@ -47,7 +47,9 @@ #include "storage/bufmgr.h" #include "tcop/tcopprot.h" #include "utils/builtins.h" +#include "utils/guc_hooks.h" #include "utils/lsyscache.h" +#include "utils/plancache.h" #include "utils/partcache.h" #include "utils/rel.h" #include "utils/snapmgr.h" @@ -55,6 +57,7 @@ /* GUC parameter */ int constraint_exclusion = CONSTRAINT_EXCLUSION_PARTITION; +bool use_invisible_index = false; /* Hook for plugins to get control in get_relation_info() */ get_relation_info_hook_type get_relation_info_hook = NULL; @@ -256,13 +259,13 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent, index = indexRelation->rd_index; /* - * Ignore invalid indexes, since they can't safely be used for - * queries. Note that this is OK because the data structure we - * are constructing is only used by the planner --- the executor - * still needs to insert into "invalid" indexes, if they're marked - * indisready. + * Skip invalid indexes, and invisible indexes unless + * use_invisible_index is set. This is OK because the data + * structure we are constructing is only used by the planner - the + * executor still needs to insert into these indexes if they're + * marked indisready. */ - if (!index->indisvalid) + if (!index->indisvalid || (!index->indisvisible && !use_invisible_index)) { index_close(indexRelation, NoLock); continue; @@ -2612,3 +2615,18 @@ set_baserel_partition_constraint(Relation relation, RelOptInfo *rel) rel->partition_qual = partconstr; } } + +/* + * assign_use_invisible_index + * GUC assign_hook for "use_invisible_index" GUC variable. + * Resets the plan cache when the value changes. + */ +void +assign_use_invisible_index(bool newval, void *extra) +{ + if (use_invisible_index != newval) + { + use_invisible_index = newval; + ResetPlanCache(); + } +} diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 3c4268b271..9a1b23c645 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -324,7 +324,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <ival> add_drop opt_asc_desc opt_nulls_order %type <node> alter_table_cmd alter_type_cmd opt_collate_clause - replica_identity partition_cmd index_partition_cmd + replica_identity partition_cmd index_partition_cmd index_alter_cmd %type <list> alter_table_cmds alter_type_cmds %type <list> alter_identity_column_option_list %type <defelt> alter_identity_column_option @@ -491,6 +491,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <boolean> opt_unique opt_verbose opt_full %type <boolean> opt_freeze opt_analyze opt_default %type <defelt> opt_binary copy_delimiter +%type <boolean> opt_index_visibility %type <boolean> copy_from opt_program @@ -733,7 +734,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); IDENTITY_P IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IMPORT_P IN_P INCLUDE INCLUDING INCREMENT INDENT INDEX INDEXES INHERIT INHERITS INITIALLY INLINE_P INNER_P INOUT INPUT_P INSENSITIVE INSERT INSTEAD INT_P INTEGER - INTERSECT INTERVAL INTO INVOKER IS ISNULL ISOLATION + INTERSECT INTERVAL INTO INVISIBLE INVOKER IS ISNULL ISOLATION JOIN JSON JSON_ARRAY JSON_ARRAYAGG JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG JSON_QUERY JSON_SCALAR JSON_SERIALIZE JSON_TABLE JSON_VALUE @@ -784,7 +785,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); UNLISTEN UNLOGGED UNTIL UPDATE USER USING VACUUM VALID VALIDATE VALIDATOR VALUE_P VALUES VARCHAR VARIADIC VARYING - VERBOSE VERSION_P VIEW VIEWS VIRTUAL VOLATILE + VERBOSE VERSION_P VIEW VIEWS VIRTUAL VISIBLE VOLATILE WHEN WHERE WHITESPACE_P WINDOW WITH WITHIN WITHOUT WORK WRAPPER WRITE @@ -2157,6 +2158,24 @@ AlterTableStmt: n->nowait = $13; $$ = (Node *) n; } + | ALTER INDEX qualified_name index_alter_cmd + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $3; + n->cmds = list_make1($4); + n->objtype = OBJECT_INDEX; + n->missing_ok = false; + $$ = (Node *) n; + } + | ALTER INDEX IF_P EXISTS qualified_name index_alter_cmd + { + AlterTableStmt *n = makeNode(AlterTableStmt); + n->relation = $5; + n->cmds = list_make1($6); + n->objtype = OBJECT_INDEX; + n->missing_ok = true; + $$ = (Node *) n; + } | ALTER INDEX qualified_name alter_table_cmds { AlterTableStmt *n = makeNode(AlterTableStmt); @@ -2382,6 +2401,21 @@ index_partition_cmd: } ; +index_alter_cmd: + /* ALTER INDEX <name> VISIBLE|INVISIBLE */ + VISIBLE + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetIndexVisible; + $$ = (Node *) n; + } + | INVISIBLE + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetIndexInvisible; + $$ = (Node *) n; + } + ; alter_table_cmd: /* ALTER TABLE <name> ADD <coldef> */ ADD_P columnDef @@ -8190,7 +8224,7 @@ defacl_privilege_target: IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name ON relation_expr access_method_clause '(' index_params ')' - opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause + opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_visibility { IndexStmt *n = makeNode(IndexStmt); @@ -8205,6 +8239,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name n->options = $14; n->tableSpace = $15; n->whereClause = $16; + n->isvisible = $17; n->excludeOpNames = NIL; n->idxcomment = NULL; n->indexOid = InvalidOid; @@ -8222,7 +8257,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name } | CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name ON relation_expr access_method_clause '(' index_params ')' - opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause + opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause opt_index_visibility { IndexStmt *n = makeNode(IndexStmt); @@ -8237,6 +8272,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_single_name n->options = $17; n->tableSpace = $18; n->whereClause = $19; + n->isvisible = $20; n->excludeOpNames = NIL; n->idxcomment = NULL; n->indexOid = InvalidOid; @@ -8259,6 +8295,12 @@ opt_unique: | /*EMPTY*/ { $$ = false; } ; +opt_index_visibility: + VISIBLE { $$ = true; } + | INVISIBLE { $$ = false; } + | /*EMPTY*/ { $$ = true; } + ; + access_method_clause: USING name { $$ = $2; } | /*EMPTY*/ { $$ = DEFAULT_INDEX_TYPE; } @@ -17836,6 +17878,7 @@ unreserved_keyword: | INSENSITIVE | INSERT | INSTEAD + | INVISIBLE | INVOKER | ISOLATION | KEEP @@ -18022,6 +18065,7 @@ unreserved_keyword: | VIEW | VIEWS | VIRTUAL + | VISIBLE | VOLATILE | WHITESPACE_P | WITHIN @@ -18431,6 +18475,7 @@ bare_label_keyword: | INT_P | INTEGER | INTERVAL + | INVISIBLE | INVOKER | IS | ISOLATION @@ -18679,6 +18724,7 @@ bare_label_keyword: | VIEW | VIEWS | VIRTUAL + | VISIBLE | VOLATILE | WHEN | WHITESPACE_P diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 62015431fd..0619833c09 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1745,6 +1745,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->concurrent = false; index->if_not_exists = false; index->reset_default_tblspc = false; + index->isvisible = idxrec->indisvisible; /* * We don't try to preserve the name of the source index; instead, just @@ -2366,6 +2367,8 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) index->concurrent = false; index->if_not_exists = false; index->reset_default_tblspc = constraint->reset_default_tblspc; + /* Ensure indexes for constraints are created as visible by default */ + index->isvisible = true; /* * If it's ALTER TABLE ADD CONSTRAINT USING INDEX, look up the index and diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 467b08198b..77b8f47cb2 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -1565,6 +1565,10 @@ pg_get_indexdef_worker(Oid indexrelid, int colno, else appendStringInfo(&buf, " WHERE %s", str); } + + /* Add INVISIBLE clause if the index is invisible */ + if (!idxrec->indisvisible) + appendStringInfoString(&buf, " INVISIBLE"); } /* Clean up */ diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index 68ff67de54..3256bca61c 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2360,6 +2360,7 @@ RelationReloadIndexInfo(Relation relation) relation->rd_index->indisready = index->indisready; relation->rd_index->indislive = index->indislive; relation->rd_index->indisreplident = index->indisreplident; + relation->rd_index->indisvisible = index->indisvisible; /* Copy xmin too, as that is needed to make sense of indcheckxmin */ HeapTupleHeaderSetXmin(relation->rd_indextuple->t_data, diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c index 2f8cbd8675..ea2937ac2c 100644 --- a/src/backend/utils/misc/guc_tables.c +++ b/src/backend/utils/misc/guc_tables.c @@ -2142,6 +2142,16 @@ struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, + { + {"use_invisible_index", PGC_USERSET, QUERY_TUNING_OTHER, + gettext_noop("Forces the planner to consider invisible indexes."), + NULL, + GUC_EXPLAIN + }, + &use_invisible_index, + false, + NULL, assign_use_invisible_index, NULL + }, /* End-of-list marker */ { diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index 34826d0138..87ae58022d 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -432,6 +432,7 @@ #enable_group_by_reordering = on #enable_distinct_reordering = on #enable_self_join_elimination = on +#use_invisible_index = off # forces planner to consider invisible indexes # - Planner Cost Constants - diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 105e917aa7..4be1a24d6f 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7647,7 +7647,8 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_tablespace, i_indreloptions, i_indstatcols, - i_indstatvals; + i_indstatvals, + i_indisvisible; /* * We want to perform just one query against pg_index. However, we @@ -7690,7 +7691,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) appendPQExpBufferStr(query, "pg_catalog.pg_get_indexdef(i.indexrelid) AS indexdef, " - "i.indkey, i.indisclustered, " + "i.indkey, i.indisclustered, i.indisvisible, " "c.contype, c.conname, " "c.condeferrable, c.condeferred, " "c.tableoid AS contableoid, " @@ -7712,6 +7713,13 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) appendPQExpBufferStr(query, "false AS indisreplident, "); + if (fout->remoteVersion >= 190000) + appendPQExpBufferStr(query, + "i.indisvisible, "); + else + appendPQExpBufferStr(query, + "true AS indisvisible, "); + if (fout->remoteVersion >= 110000) appendPQExpBufferStr(query, "inh.inhparent AS parentidx, " @@ -7826,6 +7834,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) i_indreloptions = PQfnumber(res, "indreloptions"); i_indstatcols = PQfnumber(res, "indstatcols"); i_indstatvals = PQfnumber(res, "indstatvals"); + i_indisvisible = PQfnumber(res, "indisvisible"); indxinfo = (IndxInfo *) pg_malloc(ntups * sizeof(IndxInfo)); @@ -7916,7 +7925,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables) &indAttNames, &nindAttNames)) pg_fatal("could not parse %s array", "indattnames"); } - + indxinfo[j].indisvisible = (PQgetvalue(res, j, i_indisvisible)[0] == 't'); relstats = getRelationStatistics(fout, &indxinfo[j].dobj, relpages, PQgetvalue(res, j, i_reltuples), relallvisible, relallfrozen, indexkind, @@ -18231,6 +18240,12 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) } appendPQExpBufferStr(q, ";\n"); + + if (!indxinfo->indisvisible) + { + appendPQExpBuffer(q, "ALTER INDEX %s INVISIBLE;\n", + fmtQualifiedDumpable(indxinfo)); + } } /* diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index b426b5e473..55d0203df3 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -427,6 +427,8 @@ typedef struct _indxInfo /* if there is an associated constraint object, its dumpId: */ DumpId indexconstraint; + + bool indisvisible; } IndxInfo; typedef struct _indexAttachInfo diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 6c03eca8e5..4bc579eb49 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2185,6 +2185,35 @@ my %tests = ( like => {}, }, + 'ALTER TABLE ONLY test_table ADD CONSTRAINT ... PRIMARY KEY INVISIBLE' => { + create_order => 97, + create_sql => 'CREATE TABLE dump_test.test_table_invisible ( + id int PRIMARY KEY + ); + ALTER INDEX dump_test.test_table_invisible_pkey INVISIBLE;', + regexp => qr/^ + \QALTER TABLE ONLY dump_test.test_table_invisible\E\n\s+ + \QADD CONSTRAINT test_table_invisible_pkey PRIMARY KEY (id);\E\n + \QALTER INDEX dump_test.test_table_invisible_pkey INVISIBLE;\E + /xm, + like => { %full_runs, %dump_test_schema_runs, section_post_data => 1 }, + unlike => { exclude_dump_test_schema => 1, only_dump_measurement => 1 }, + }, + + 'CREATE INDEX ... INVISIBLE' => { + create_order => 98, + create_sql => 'CREATE TABLE dump_test.test_table_mixed_indexes ( + id int, + value int + ); + CREATE INDEX idx_visible ON dump_test.test_table_mixed_indexes(value); + CREATE UNIQUE INDEX idx_invisible ON dump_test.test_table_mixed_indexes(value) INVISIBLE; + ALTER INDEX dump_test.idx_invisible INVISIBLE;', + regexp => qr/CREATE UNIQUE INDEX idx_invisible ON dump_test\.test_table_mixed_indexes USING btree \(value\) INVISIBLE;/m, + like => { %full_runs, %dump_test_schema_runs, section_post_data => 1 }, + unlike => { exclude_dump_test_schema => 1, only_dump_measurement => 1 }, + }, + 'CREATE AGGREGATE dump_test.newavg' => { create_order => 25, create_sql => 'CREATE AGGREGATE dump_test.newavg ( diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 1d08268393..bdb7397814 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -2329,6 +2329,11 @@ describeOneTableDetails(const char *schemaname, else appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n"); + if (pset.sversion >= 180000) + appendPQExpBufferStr(&buf, "i.indisvisible,\n"); + else + appendPQExpBufferStr(&buf, "true AS indisvisible,\n"); + appendPQExpBuffer(&buf, " a.amname, c2.relname, " "pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n" "FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n" @@ -2354,9 +2359,10 @@ describeOneTableDetails(const char *schemaname, char *deferred = PQgetvalue(result, 0, 5); char *indisreplident = PQgetvalue(result, 0, 6); char *indnullsnotdistinct = PQgetvalue(result, 0, 7); - char *indamname = PQgetvalue(result, 0, 8); - char *indtable = PQgetvalue(result, 0, 9); - char *indpred = PQgetvalue(result, 0, 10); + char *indisvisible = PQgetvalue(result, 0, 8); + char *indamname = PQgetvalue(result, 0, 9); + char *indtable = PQgetvalue(result, 0, 10); + char *indpred = PQgetvalue(result, 0, 11); if (strcmp(indisprimary, "t") == 0) printfPQExpBuffer(&tmpbuf, _("primary key, ")); @@ -2393,6 +2399,9 @@ describeOneTableDetails(const char *schemaname, if (strcmp(indisreplident, "t") == 0) appendPQExpBufferStr(&tmpbuf, _(", replica identity")); + if (strcmp(indisvisible, "t") != 0) + appendPQExpBufferStr(&tmpbuf, _(", invisible")); + printTableAddFooter(&cont, tmpbuf.data); /* diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c index c916b9299a..8bafcb0740 100644 --- a/src/bin/psql/tab-complete.in.c +++ b/src/bin/psql/tab-complete.in.c @@ -2419,7 +2419,8 @@ match_previous_words(int pattern_id, else if (Matches("ALTER", "INDEX", MatchAny)) COMPLETE_WITH("ALTER COLUMN", "OWNER TO", "RENAME TO", "SET", "RESET", "ATTACH PARTITION", - "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION"); + "DEPENDS ON EXTENSION", "NO DEPENDS ON EXTENSION", + "INVISIBLE", "VISIBLE"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH")) COMPLETE_WITH("PARTITION"); else if (Matches("ALTER", "INDEX", MatchAny, "ATTACH", "PARTITION")) @@ -3433,6 +3434,9 @@ match_previous_words(int pattern_id, !TailMatches("POLICY", MatchAny, MatchAny, MatchAny, MatchAny, MatchAny) && !TailMatches("FOR", MatchAny, MatchAny, MatchAny)) COMPLETE_WITH("("); + else if (TailMatches("INDEX|CONCURRENTLY", "ON", MatchAny, "USING", MatchAny, "(*)") || + TailMatches("INDEX|CONCURRENTLY", "ON", MatchAny, "(*)")) + COMPLETE_WITH("VISIBLE", "INVISIBLE"); /* CREATE OR REPLACE */ else if (Matches("CREATE", "OR")) diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h index 4daa8bef5e..75bef5bd88 100644 --- a/src/include/catalog/index.h +++ b/src/include/catalog/index.h @@ -65,6 +65,7 @@ extern void index_check_primary_key(Relation heapRel, #define INDEX_CREATE_IF_NOT_EXISTS (1 << 4) #define INDEX_CREATE_PARTITIONED (1 << 5) #define INDEX_CREATE_INVALID (1 << 6) +#define INDEX_CREATE_VISIBLE (1 << 7) extern Oid index_create(Relation heapRelation, const char *indexRelationName, @@ -175,7 +176,6 @@ extern void RestoreReindexState(const void *reindexstate); extern void IndexSetParentIndex(Relation partitionIdx, Oid parentOid); - /* * itemptr_encode - Encode ItemPointer as int64/int8 * diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h index 4392b9d221..ec8e5dc4bf 100644 --- a/src/include/catalog/pg_index.h +++ b/src/include/catalog/pg_index.h @@ -44,6 +44,8 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO bool indisready; /* is this index ready for inserts? */ bool indislive; /* is this index alive at all? */ bool indisreplident; /* is this index the identity for replication? */ + bool indisvisible BKI_DEFAULT(t); /* is this index visible for + * use by queries? */ /* variable-length fields start here, but we allow direct access to indkey */ int2vector indkey BKI_FORCE_NOT_NULL; /* column numbers of indexed cols, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 4610fc6129..92a14b82f5 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -2472,6 +2472,8 @@ typedef enum AlterTableType AT_SetIdentity, /* SET identity column options */ AT_DropIdentity, /* DROP IDENTITY */ AT_ReAddStatistics, /* internal to commands/tablecmds.c */ + AT_SetIndexVisible, /* Set INDEX visible */ + AT_SetIndexInvisible, /* Set INDEX invisible */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ @@ -3470,6 +3472,8 @@ typedef struct IndexStmt bool if_not_exists; /* just do nothing if index already exists? */ bool reset_default_tblspc; /* reset default_tablespace prior to * executing */ + bool isvisible; /* true if VISIBLE (default), false if + * INVISIBLE */ } IndexStmt; /* ---------------------- diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h index 546828b54b..3b5ca55bae 100644 --- a/src/include/optimizer/optimizer.h +++ b/src/include/optimizer/optimizer.h @@ -212,4 +212,9 @@ extern List *pull_var_clause(Node *node, int flags); extern Node *flatten_join_alias_vars(PlannerInfo *root, Query *query, Node *node); extern Node *flatten_group_exprs(PlannerInfo *root, Query *query, Node *node); +/* + * GUC to tell planner to consider invisible indexes. + */ +extern bool use_invisible_index; + #endif /* OPTIMIZER_H */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index a4af3f717a..77da5117cb 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -229,6 +229,7 @@ PG_KEYWORD("integer", INTEGER, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("intersect", INTERSECT, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("interval", INTERVAL, COL_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("into", INTO, RESERVED_KEYWORD, AS_LABEL) +PG_KEYWORD("invisible", INVISIBLE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("invoker", INVOKER, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("is", IS, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) PG_KEYWORD("isnull", ISNULL, TYPE_FUNC_NAME_KEYWORD, AS_LABEL) @@ -493,6 +494,7 @@ PG_KEYWORD("version", VERSION_P, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("view", VIEW, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("views", VIEWS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("virtual", VIRTUAL, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("visible", VISIBLE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("volatile", VOLATILE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("when", WHEN, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("where", WHERE, RESERVED_KEYWORD, AS_LABEL) diff --git a/src/include/utils/guc_hooks.h b/src/include/utils/guc_hooks.h index 799fa7ace6..dd5128fd87 100644 --- a/src/include/utils/guc_hooks.h +++ b/src/include/utils/guc_hooks.h @@ -178,5 +178,6 @@ extern bool check_synchronized_standby_slots(char **newval, void **extra, extern void assign_synchronized_standby_slots(const char *newval, void *extra); extern bool check_idle_replication_slot_timeout(int *newval, void **extra, GucSource source); +extern void assign_use_invisible_index(bool newval, void *extra); #endif /* GUC_HOOKS_H */ diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index fa7c7e0323..b716b94c38 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -210,6 +210,7 @@ extern Oid get_publication_oid(const char *pubname, bool missing_ok); extern char *get_publication_name(Oid pubid, bool missing_ok); extern Oid get_subscription_oid(const char *subname, bool missing_ok); extern char *get_subscription_name(Oid subid, bool missing_ok); +extern bool get_index_visibility(Oid indexOid); #define type_is_array(typid) (get_element_type(typid) != InvalidOid) /* type_is_array_domain accepts both plain arrays and domains over arrays */ diff --git a/src/interfaces/ecpg/test/regression.diffs b/src/interfaces/ecpg/test/regression.diffs new file mode 100644 index 0000000000..e69de29bb2 diff --git a/src/interfaces/ecpg/test/regression.out b/src/interfaces/ecpg/test/regression.out new file mode 100644 index 0000000000..cb633f4d71 --- /dev/null +++ b/src/interfaces/ecpg/test/regression.out @@ -0,0 +1,55 @@ +# initializing database system by copying initdb template +# using temp instance on port 65312 with PID 30031 +ok 1 - compat_informix/dec_test 563 ms +ok 2 - compat_informix/charfuncs 255 ms +ok 3 - compat_informix/rfmtdate 355 ms +ok 4 - compat_informix/rfmtlong 294 ms +ok 5 - compat_informix/rnull 337 ms +ok 6 - compat_informix/sqlda 293 ms +ok 7 - compat_informix/describe 289 ms +ok 8 - compat_informix/test_informix 350 ms +ok 9 - compat_informix/test_informix2 334 ms +ok 10 - compat_informix/intoasc 264 ms +ok 11 - compat_oracle/char_array 283 ms +ok 12 - connect/test2 354 ms +ok 13 - connect/test3 346 ms +ok 14 - connect/test4 312 ms +ok 15 - connect/test5 337 ms +ok 16 - pgtypeslib/dt_test 332 ms +ok 17 - pgtypeslib/dt_test2 323 ms +ok 18 - pgtypeslib/num_test 323 ms +ok 19 - pgtypeslib/num_test2 319 ms +ok 20 - pgtypeslib/nan_test 324 ms +ok 21 - preproc/array_of_struct 279 ms +ok 22 - preproc/pointer_to_struct 351 ms +ok 23 - preproc/autoprep 346 ms +ok 24 - preproc/comment 295 ms +ok 25 - preproc/cursor 379 ms +ok 26 - preproc/define 349 ms +ok 27 - preproc/init 296 ms +ok 28 - preproc/strings 317 ms +ok 29 - preproc/type 306 ms +ok 30 - preproc/variable 360 ms +ok 31 - preproc/outofscope 340 ms +ok 32 - preproc/whenever 335 ms +ok 33 - preproc/whenever_do_continue 308 ms +ok 34 - sql/array 383 ms +ok 35 - sql/binary 329 ms +ok 36 - sql/bytea 335 ms +ok 37 - sql/code100 351 ms +ok 38 - sql/copystdout 320 ms +ok 39 - sql/createtableas 340 ms +ok 40 - sql/define 335 ms +ok 41 - sql/desc 339 ms +ok 42 - sql/sqlda 341 ms +ok 43 - sql/describe 354 ms +ok 44 - sql/dynalloc 316 ms +ok 45 - sql/dynalloc2 340 ms +ok 46 - sql/dyntest 343 ms +ok 47 - sql/execute 328 ms +ok 48 - sql/fetch 324 ms +ok 49 - sql/func 333 ms +ok 50 - sql/indicators 346 ms +ok 51 - sql/oldexec 314 ms +ok 52 - sql/quote 311 ms +ok 53 - sql/show 392 ms diff --git a/src/test/modules/test_ddl_deparse/Makefile b/src/test/modules/test_ddl_deparse/Makefile index 3a57a95c84..747d8b5835 100644 --- a/src/test/modules/test_ddl_deparse/Makefile +++ b/src/test/modules/test_ddl_deparse/Makefile @@ -27,7 +27,8 @@ REGRESS = test_ddl_deparse \ alter_type_enum \ opfamily \ defprivs \ - matviews + matviews \ + alter_index EXTRA_INSTALL = contrib/pg_stat_statements diff --git a/src/test/modules/test_ddl_deparse/expected/alter_index.out b/src/test/modules/test_ddl_deparse/expected/alter_index.out new file mode 100644 index 0000000000..f502baf1c5 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/expected/alter_index.out @@ -0,0 +1,29 @@ +-- Test index visibility commands +CREATE TABLE visibility_test (id int); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE INDEX idx_vis ON visibility_test(id); +NOTICE: DDL test: type simple, tag CREATE INDEX +ALTER INDEX idx_vis INVISIBLE; +NOTICE: DDL test: type alter table, tag ALTER INDEX +NOTICE: subcommand: type SET INVISIBLE desc <NULL> +ALTER INDEX idx_vis VISIBLE; +NOTICE: DDL test: type alter table, tag ALTER INDEX +NOTICE: subcommand: type SET VISIBLE desc <NULL> +ALTER INDEX idx_vis INVISIBLE; +NOTICE: DDL test: type alter table, tag ALTER INDEX +NOTICE: subcommand: type SET INVISIBLE desc <NULL> +REINDEX INDEX idx_vis; +NOTICE: DDL test: type simple, tag REINDEX +CREATE SCHEMA visibility_schema; +NOTICE: DDL test: type simple, tag CREATE SCHEMA +CREATE TABLE visibility_schema.test2 (id int); +NOTICE: DDL test: type simple, tag CREATE TABLE +CREATE INDEX idx_vis2 ON visibility_schema.test2(id); +NOTICE: DDL test: type simple, tag CREATE INDEX +ALTER INDEX visibility_schema.idx_vis2 INVISIBLE; +NOTICE: DDL test: type alter table, tag ALTER INDEX +NOTICE: subcommand: type SET INVISIBLE desc <NULL> +-- Clean up +DROP SCHEMA visibility_schema CASCADE; +NOTICE: drop cascades to table visibility_schema.test2 +DROP TABLE visibility_test CASCADE; diff --git a/src/test/modules/test_ddl_deparse/meson.build b/src/test/modules/test_ddl_deparse/meson.build index bff65ba633..6654b5e46d 100644 --- a/src/test/modules/test_ddl_deparse/meson.build +++ b/src/test/modules/test_ddl_deparse/meson.build @@ -48,6 +48,7 @@ tests += { 'opfamily', 'defprivs', 'matviews', + 'alter_index', ], }, } diff --git a/src/test/modules/test_ddl_deparse/sql/alter_index.sql b/src/test/modules/test_ddl_deparse/sql/alter_index.sql new file mode 100644 index 0000000000..7a34dc6692 --- /dev/null +++ b/src/test/modules/test_ddl_deparse/sql/alter_index.sql @@ -0,0 +1,18 @@ +-- Test index visibility commands +CREATE TABLE visibility_test (id int); +CREATE INDEX idx_vis ON visibility_test(id); + +ALTER INDEX idx_vis INVISIBLE; +ALTER INDEX idx_vis VISIBLE; + +ALTER INDEX idx_vis INVISIBLE; +REINDEX INDEX idx_vis; + +CREATE SCHEMA visibility_schema; +CREATE TABLE visibility_schema.test2 (id int); +CREATE INDEX idx_vis2 ON visibility_schema.test2(id); +ALTER INDEX visibility_schema.idx_vis2 INVISIBLE; + +-- Clean up +DROP SCHEMA visibility_schema CASCADE; +DROP TABLE visibility_test CASCADE; diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c index 193669f2bc..8d7c7e5894 100644 --- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c +++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c @@ -308,6 +308,12 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS) case AT_ReAddStatistics: strtype = "(re) ADD STATS"; break; + case AT_SetIndexVisible: + strtype = "SET VISIBLE"; + break; + case AT_SetIndexInvisible: + strtype = "SET INVISIBLE"; + break; } if (subcmd->recurse) diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out index 9ade7b835e..32eb1cd76b 100644 --- a/src/test/regress/expected/create_index.out +++ b/src/test/regress/expected/create_index.out @@ -3542,6 +3542,833 @@ ERROR: REINDEX SCHEMA cannot run inside a transaction block END; -- concurrently REINDEX SCHEMA CONCURRENTLY schema_to_reindex; +-- Test index visibility functionality +CREATE TABLE index_test( + id INT PRIMARY KEY, + data TEXT, + num INT, + vector INT[], + range INT4RANGE +); +INSERT INTO index_test +SELECT + g, + 'data ' || g, + g % 100, + ARRAY[g, g+1, g+2], + int4range(g, g+10) +FROM generate_series(1, 1000) g; +-- Function for testing +CREATE FUNCTION get_data_length(text) RETURNS INT AS $$ + SELECT length($1); +$$ LANGUAGE SQL IMMUTABLE; +-- Helper function to show index status +CREATE OR REPLACE FUNCTION show_index_status(index_name text) +RETURNS TABLE ( + indexrelid regclass, + indisvalid boolean, + indisready boolean, + indislive boolean, + indisvisible boolean +) AS $$ +BEGIN + RETURN QUERY EXECUTE format(' + SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible + FROM pg_index + WHERE indexrelid = %L::regclass', index_name); +END; +$$ LANGUAGE plpgsql; +-- Create and test each index type +-- 1. Basic single-column index +CREATE INDEX idx_single ON index_test(data); +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; + QUERY PLAN +----------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (data = 'data 500'::text) + -> Bitmap Index Scan on idx_single + Index Cond: (data = 'data 500'::text) +(4 rows) + +ALTER INDEX idx_single INVISIBLE; +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; + QUERY PLAN +------------------------------------- + Seq Scan on index_test + Filter: (data = 'data 500'::text) +(2 rows) + +ALTER INDEX idx_single VISIBLE; +SELECT show_index_status('idx_single'); + show_index_status +---------------------- + (idx_single,t,t,t,t) +(1 row) + +-- 2. Multi-column index +CREATE INDEX idx_multi ON index_test(num, data); +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; + QUERY PLAN +---------------------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: ((num = 50) AND (data > 'data 500'::text)) + -> Bitmap Index Scan on idx_multi + Index Cond: ((num = 50) AND (data > 'data 500'::text)) +(4 rows) + +ALTER INDEX idx_multi INVISIBLE; +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; + QUERY PLAN +------------------------------------------------------ + Seq Scan on index_test + Filter: ((data > 'data 500'::text) AND (num = 50)) +(2 rows) + +ALTER INDEX idx_multi VISIBLE; +SELECT show_index_status('idx_multi'); + show_index_status +--------------------- + (idx_multi,t,t,t,t) +(1 row) + +-- 3. Partial index +CREATE INDEX idx_partial ON index_test(num) WHERE num < 50; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; + QUERY PLAN +---------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 25) + -> Bitmap Index Scan on idx_partial + Index Cond: (num = 25) +(4 rows) + +ALTER INDEX idx_partial INVISIBLE; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 25) + -> Bitmap Index Scan on idx_multi + Index Cond: (num = 25) +(4 rows) + +ALTER INDEX idx_partial VISIBLE; +SELECT show_index_status('idx_partial'); + show_index_status +----------------------- + (idx_partial,t,t,t,t) +(1 row) + +-- 4. Expression index +CREATE INDEX idx_expression ON index_test((lower(data))); +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; + QUERY PLAN +------------------------------------------------------ + Bitmap Heap Scan on index_test + Recheck Cond: (lower(data) = 'data 500'::text) + -> Bitmap Index Scan on idx_expression + Index Cond: (lower(data) = 'data 500'::text) +(4 rows) + +ALTER INDEX idx_expression INVISIBLE; +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; + QUERY PLAN +-------------------------------------------- + Seq Scan on index_test + Filter: (lower(data) = 'data 500'::text) +(2 rows) + +ALTER INDEX idx_expression VISIBLE; +SELECT show_index_status('idx_expression'); + show_index_status +-------------------------- + (idx_expression,t,t,t,t) +(1 row) + +-- 5. GIN index +CREATE INDEX idx_gin ON index_test USING gin(vector); +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; + QUERY PLAN +---------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (vector @> '{500}'::integer[]) + -> Bitmap Index Scan on idx_gin + Index Cond: (vector @> '{500}'::integer[]) +(4 rows) + +ALTER INDEX idx_gin INVISIBLE; +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; + QUERY PLAN +------------------------------------------ + Seq Scan on index_test + Filter: (vector @> '{500}'::integer[]) +(2 rows) + +ALTER INDEX idx_gin VISIBLE; +SELECT show_index_status('idx_gin'); + show_index_status +------------------- + (idx_gin,t,t,t,t) +(1 row) + +-- 6. GiST index +CREATE INDEX idx_gist ON index_test USING gist(range); +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); + QUERY PLAN +------------------------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (range && '[100,110)'::int4range) + -> Bitmap Index Scan on idx_gist + Index Cond: (range && '[100,110)'::int4range) +(4 rows) + +ALTER INDEX idx_gist INVISIBLE; +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); + QUERY PLAN +--------------------------------------------- + Seq Scan on index_test + Filter: (range && '[100,110)'::int4range) +(2 rows) + +ALTER INDEX idx_gist VISIBLE; +SELECT show_index_status('idx_gist'); + show_index_status +-------------------- + (idx_gist,t,t,t,t) +(1 row) + +-- 7. Covering index +CREATE INDEX idx_covering ON index_test(num) INCLUDE (data); +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; + QUERY PLAN +----------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 50) + -> Bitmap Index Scan on idx_covering + Index Cond: (num = 50) +(4 rows) + +ALTER INDEX idx_covering INVISIBLE; +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; + QUERY PLAN +-------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (num = 50) + -> Bitmap Index Scan on idx_multi + Index Cond: (num = 50) +(4 rows) + +ALTER INDEX idx_covering VISIBLE; +SELECT show_index_status('idx_covering'); + show_index_status +------------------------ + (idx_covering,t,t,t,t) +(1 row) + +-- 8. Unique index +CREATE UNIQUE INDEX idx_unique ON index_test(id, data); +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; + QUERY PLAN +---------------------------------------------------------- + Index Scan using idx_unique on index_test + Index Cond: ((id = 500) AND (data = 'data 500'::text)) +(2 rows) + +ALTER INDEX idx_unique INVISIBLE; +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; + QUERY PLAN +------------------------------------------------ + Index Scan using index_test_pkey on index_test + Index Cond: (id = 500) + Filter: (data = 'data 500'::text) +(3 rows) + +ALTER INDEX idx_unique VISIBLE; +SELECT show_index_status('idx_unique'); + show_index_status +---------------------- + (idx_unique,t,t,t,t) +(1 row) + +-- 9. Function-based index +CREATE INDEX idx_func ON index_test(get_data_length(data)); +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; + QUERY PLAN +----------------------------------------- + Bitmap Heap Scan on index_test + Recheck Cond: (length(data) = 10) + -> Bitmap Index Scan on idx_func + Index Cond: (length(data) = 10) +(4 rows) + +ALTER INDEX idx_func INVISIBLE; +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; + QUERY PLAN +------------------------------- + Seq Scan on index_test + Filter: (length(data) = 10) +(2 rows) + +ALTER INDEX idx_func VISIBLE; +SELECT show_index_status('idx_func'); + show_index_status +-------------------- + (idx_func,t,t,t,t) +(1 row) + +-- 10. Join index +CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT); +INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g; +CREATE INDEX idx_join ON join_test(ref_id); +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + QUERY PLAN +--------------------------------------------------------------- + Hash Join + Hash Cond: (it.num = jt.ref_id) + -> Seq Scan on index_test it + -> Hash + -> Bitmap Heap Scan on join_test jt + Recheck Cond: ((id >= 100) AND (id <= 200)) + -> Bitmap Index Scan on join_test_pkey + Index Cond: ((id >= 100) AND (id <= 200)) +(8 rows) + +-- Set all indexes to invisible to force seq scan +ALTER INDEX idx_join INVISIBLE; +ALTER INDEX join_test_pkey INVISIBLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +SELECT show_index_status('join_test_pkey'); + show_index_status +-------------------------- + (join_test_pkey,t,t,t,f) +(1 row) + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + QUERY PLAN +----------------------------------------------------- + Hash Join + Hash Cond: (it.num = jt.ref_id) + -> Seq Scan on index_test it + -> Hash + -> Seq Scan on join_test jt + Filter: ((id >= 100) AND (id <= 200)) +(6 rows) + +ALTER INDEX idx_join VISIBLE; +ALTER INDEX join_test_pkey VISIBLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +SELECT show_index_status('join_test_pkey'); + show_index_status +-------------------------- + (join_test_pkey,t,t,t,t) +(1 row) + +-- Test REINDEX CONCURRENTLY +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,t) +(1 row) + +ALTER INDEX idx_join INVISIBLE; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + show_index_status +-------------------- + (idx_join,t,t,t,f) +(1 row) + +SELECT pg_get_indexdef('idx_join'::regclass); + pg_get_indexdef +------------------------------------------------------------------------------------- + CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) INVISIBLE +(1 row) + +ALTER INDEX idx_join VISIBLE; +SELECT pg_get_indexdef('idx_join'::regclass); + pg_get_indexdef +--------------------------------------------------------------------------- + CREATE INDEX idx_join ON schema_to_reindex.join_test USING btree (ref_id) +(1 row) + +-- Test index visibility with indcheckxmin being true +CREATE TABLE indcheckxmin_test(id int PRIMARY KEY); +INSERT INTO indcheckxmin_test VALUES (1), (2), (3); +CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id); +BEGIN; +-- Simulate indcheckxmin being true +UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass; +ALTER INDEX idx_checkxmin INVISIBLE; -- expect fail +ERROR: cannot update index visibility while indcheckxmin is true +HINT: Wait for all transactions that might see inconsistent HOT chains to complete +ROLLBACK; +-- Test prepared statements with index visibility +CREATE TABLE prep_idx_test (id int, data text); +CREATE INDEX prep_idx1 ON prep_idx_test(data); +INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g; +-- Test with visible index +PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + QUERY PLAN +--------------------------------------------- + Bitmap Heap Scan on prep_idx_test + Recheck Cond: (data = 'data 1'::text) + -> Bitmap Index Scan on prep_idx1 + Index Cond: (data = 'data 1'::text) +(4 rows) + +EXECUTE idx_plan('data 1'); + id | data +----+-------- + 1 | data 1 +(1 row) + +-- Test with invisible index +ALTER INDEX prep_idx1 INVISIBLE; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + QUERY PLAN +----------------------------------- + Seq Scan on prep_idx_test + Filter: (data = 'data 1'::text) +(2 rows) + +EXECUTE idx_plan('data 1'); + id | data +----+-------- + 1 | data 1 +(1 row) + +-- Test with use_invisible_index +SET use_invisible_index TO on; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + QUERY PLAN +--------------------------------------------- + Bitmap Heap Scan on prep_idx_test + Recheck Cond: (data = 'data 1'::text) + -> Bitmap Index Scan on prep_idx1 + Index Cond: (data = 'data 1'::text) +(4 rows) + +EXECUTE idx_plan('data 1'); + id | data +----+-------- + 1 | data 1 +(1 row) + +SET use_invisible_index TO off; +-- Assert plan cache is reset and index is not used +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + QUERY PLAN +----------------------------------- + Seq Scan on prep_idx_test + Filter: (data = 'data 1'::text) +(2 rows) + +-- Make index visible and test again +ALTER INDEX prep_idx1 VISIBLE; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + QUERY PLAN +--------------------------------------------- + Bitmap Heap Scan on prep_idx_test + Recheck Cond: (data = 'data 1'::text) + -> Bitmap Index Scan on prep_idx1 + Index Cond: (data = 'data 1'::text) +(4 rows) + +EXECUTE idx_plan('data 1'); + id | data +----+-------- + 1 | data 1 +(1 row) + +-- Test index visibility with partitioned tables +CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id); +CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100); +CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200); +INSERT INTO part_tbl +SELECT g, 'data ' || g +FROM generate_series(1, 199) g; +CREATE INDEX idx_part_tbl ON part_tbl(data); +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +JOIN pg_inherits inh ON i.indrelid = inh.inhrelid +WHERE inh.inhparent = 'part_tbl'::regclass::oid +UNION ALL +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +WHERE i.indrelid = 'part_tbl'::regclass::oid +ORDER BY index_name; + index_name | table_name | indisvisible +----------------+------------+-------------- + idx_part_tbl | part_tbl | t + part1_data_idx | part1 | t + part2_data_idx | part2 | t +(3 rows) + +-- Force use of indexes to avoid flaky test results +SET enable_seqscan = off; +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + QUERY PLAN +----------------------------------------------------------- + Append + -> Index Scan using part1_data_idx on part1 part_tbl_1 + Index Cond: (data = 'data 50'::text) + -> Index Scan using part2_data_idx on part2 part_tbl_2 + Index Cond: (data = 'data 50'::text) +(5 rows) + +ALTER INDEX idx_part_tbl INVISIBLE; +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +JOIN pg_inherits inh ON i.indrelid = inh.inhrelid +WHERE inh.inhparent = 'part_tbl'::regclass::oid +UNION ALL +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +WHERE i.indrelid = 'part_tbl'::regclass::oid +ORDER BY index_name; + index_name | table_name | indisvisible +----------------+------------+-------------- + idx_part_tbl | part_tbl | f + part1_data_idx | part1 | f + part2_data_idx | part2 | f +(3 rows) + +-- Check query plan after setting invisible (should use seq scan) +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + QUERY PLAN +------------------------------------------ + Append + -> Seq Scan on part1 part_tbl_1 + Disabled: true + Filter: (data = 'data 50'::text) + -> Seq Scan on part2 part_tbl_2 + Disabled: true + Filter: (data = 'data 50'::text) +(7 rows) + +SET use_invisible_index TO on; +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + QUERY PLAN +----------------------------------------------------------- + Append + -> Index Scan using part1_data_idx on part1 part_tbl_1 + Index Cond: (data = 'data 50'::text) + -> Index Scan using part2_data_idx on part2 part_tbl_2 + Index Cond: (data = 'data 50'::text) +(5 rows) + +SET use_invisible_index TO off; +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + QUERY PLAN +------------------------------------------ + Append + -> Seq Scan on part1 part_tbl_1 + Disabled: true + Filter: (data = 'data 50'::text) + -> Seq Scan on part2 part_tbl_2 + Disabled: true + Filter: (data = 'data 50'::text) +(7 rows) + +ALTER INDEX idx_part_tbl VISIBLE; +SELECT c.relname, i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +WHERE c.relname LIKE 'idx_part_tbl%' +ORDER BY c.relname; + relname | indisvisible +--------------+-------------- + idx_part_tbl | t +(1 row) + +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + QUERY PLAN +----------------------------------------------------------- + Append + -> Index Scan using part1_data_idx on part1 part_tbl_1 + Index Cond: (data = 'data 50'::text) + -> Index Scan using part2_data_idx on part2 part_tbl_2 + Index Cond: (data = 'data 50'::text) +(5 rows) + +SET enable_seqscan = on; +-- Test REINDEX maintains visibility setting +ALTER INDEX idx_part_tbl INVISIBLE; +REINDEX INDEX idx_part_tbl; +SELECT c.relname, i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +WHERE c.relname LIKE 'idx_part_tbl%' +ORDER BY c.relname; + relname | indisvisible +--------------+-------------- + idx_part_tbl | f +(1 row) + +-- Test that index visibility is preserved after ALTER TABLE +CREATE TABLE vis_test(id INT PRIMARY KEY, data text); +\d vis_test + Table "schema_to_reindex.vis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + data | text | | | +Indexes: + "vis_test_pkey" PRIMARY KEY, btree (id) + +ALTER INDEX vis_test_pkey INVISIBLE; +\d vis_test + Table "schema_to_reindex.vis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + data | text | | | +Indexes: + "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE + +ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE bigint; +\d vis_test + Table "schema_to_reindex.vis_test" + Column | Type | Collation | Nullable | Default +--------+--------+-----------+----------+--------- + id | bigint | | not null | + data | text | | | +Indexes: + "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE + +ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE int; +\d vis_test + Table "schema_to_reindex.vis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + data | text | | | +Indexes: + "vis_test_pkey" PRIMARY KEY, btree (id) INVISIBLE + +ALTER INDEX vis_test_pkey VISIBLE; +\d vis_test + Table "schema_to_reindex.vis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | not null | + data | text | | | +Indexes: + "vis_test_pkey" PRIMARY KEY, btree (id) + +-- Test CREATE INDEX with INVISIBLE option +CREATE TABLE invis_test(id int, data text); +INSERT INTO invis_test SELECT g, 'data ' || g FROM generate_series(1,100) g; +CREATE INDEX idx_invis1 ON invis_test(data) INVISIBLE; +\d invis_test + Table "schema_to_reindex.invis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | | + data | text | | | +Indexes: + "idx_invis1" btree (data) INVISIBLE + +\d idx_invis1 +Index "schema_to_reindex.idx_invis1" + Column | Type | Key? | Definition +--------+------+------+------------ + data | text | yes | data +btree, for table "schema_to_reindex.invis_test", invisible + +ALTER INDEX idx_invis1 VISIBLE; +\d invis_test + Table "schema_to_reindex.invis_test" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + id | integer | | | + data | text | | | +Indexes: + "idx_invis1" btree (data) + +\d idx_invis1 +Index "schema_to_reindex.idx_invis1" + Column | Type | Key? | Definition +--------+------+------+------------ + data | text | yes | data +btree, for table "schema_to_reindex.invis_test" + +-- Clean up +DROP TABLE index_test; +DROP TABLE join_test; +DROP FUNCTION get_data_length; +DROP FUNCTION show_index_status; +DROP TABLE indcheckxmin_test CASCADE; +DEALLOCATE idx_plan; +DROP TABLE prep_idx_test; +DROP TABLE part_tbl CASCADE; +DROP TABLE vis_test; +DROP TABLE invis_test; -- Failure for unauthorized user CREATE ROLE regress_reindexuser NOLOGIN; SET SESSION ROLE regress_reindexuser; diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql index e21ff42651..7b6fe83390 100644 --- a/src/test/regress/sql/create_index.sql +++ b/src/test/regress/sql/create_index.sql @@ -1496,6 +1496,355 @@ END; -- concurrently REINDEX SCHEMA CONCURRENTLY schema_to_reindex; +-- Test index visibility functionality + +CREATE TABLE index_test( + id INT PRIMARY KEY, + data TEXT, + num INT, + vector INT[], + range INT4RANGE +); + +INSERT INTO index_test +SELECT + g, + 'data ' || g, + g % 100, + ARRAY[g, g+1, g+2], + int4range(g, g+10) +FROM generate_series(1, 1000) g; + +-- Function for testing +CREATE FUNCTION get_data_length(text) RETURNS INT AS $$ + SELECT length($1); +$$ LANGUAGE SQL IMMUTABLE; + +-- Helper function to show index status +CREATE OR REPLACE FUNCTION show_index_status(index_name text) +RETURNS TABLE ( + indexrelid regclass, + indisvalid boolean, + indisready boolean, + indislive boolean, + indisvisible boolean +) AS $$ +BEGIN + RETURN QUERY EXECUTE format(' + SELECT indexrelid::regclass, indisvalid, indisready, indislive, indisvisible + FROM pg_index + WHERE indexrelid = %L::regclass', index_name); +END; +$$ LANGUAGE plpgsql; + +-- Create and test each index type +-- 1. Basic single-column index +CREATE INDEX idx_single ON index_test(data); +SELECT show_index_status('idx_single'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; +ALTER INDEX idx_single INVISIBLE; +SELECT show_index_status('idx_single'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE data = 'data 500'; +ALTER INDEX idx_single VISIBLE; +SELECT show_index_status('idx_single'); + +-- 2. Multi-column index +CREATE INDEX idx_multi ON index_test(num, data); +SELECT show_index_status('idx_multi'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; +ALTER INDEX idx_multi INVISIBLE; +SELECT show_index_status('idx_multi'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 50 AND data > 'data 500'; +ALTER INDEX idx_multi VISIBLE; +SELECT show_index_status('idx_multi'); + +-- 3. Partial index +CREATE INDEX idx_partial ON index_test(num) WHERE num < 50; +SELECT show_index_status('idx_partial'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; +ALTER INDEX idx_partial INVISIBLE; +SELECT show_index_status('idx_partial'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE num = 25; +ALTER INDEX idx_partial VISIBLE; +SELECT show_index_status('idx_partial'); + +-- 4. Expression index +CREATE INDEX idx_expression ON index_test((lower(data))); +SELECT show_index_status('idx_expression'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; +ALTER INDEX idx_expression INVISIBLE; +SELECT show_index_status('idx_expression'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE lower(data) = 'data 500'; +ALTER INDEX idx_expression VISIBLE; +SELECT show_index_status('idx_expression'); + +-- 5. GIN index +CREATE INDEX idx_gin ON index_test USING gin(vector); +SELECT show_index_status('idx_gin'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; +ALTER INDEX idx_gin INVISIBLE; +SELECT show_index_status('idx_gin'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE vector @> ARRAY[500]; +ALTER INDEX idx_gin VISIBLE; +SELECT show_index_status('idx_gin'); + +-- 6. GiST index +CREATE INDEX idx_gist ON index_test USING gist(range); +SELECT show_index_status('idx_gist'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); +ALTER INDEX idx_gist INVISIBLE; +SELECT show_index_status('idx_gist'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE range && int4range(100, 110); +ALTER INDEX idx_gist VISIBLE; +SELECT show_index_status('idx_gist'); + +-- 7. Covering index +CREATE INDEX idx_covering ON index_test(num) INCLUDE (data); +SELECT show_index_status('idx_covering'); +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; +ALTER INDEX idx_covering INVISIBLE; +SELECT show_index_status('idx_covering'); +EXPLAIN (COSTS OFF) SELECT num, data FROM index_test WHERE num = 50; +ALTER INDEX idx_covering VISIBLE; +SELECT show_index_status('idx_covering'); + +-- 8. Unique index +CREATE UNIQUE INDEX idx_unique ON index_test(id, data); +SELECT show_index_status('idx_unique'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; +ALTER INDEX idx_unique INVISIBLE; +SELECT show_index_status('idx_unique'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE id = 500 AND data = 'data 500'; +ALTER INDEX idx_unique VISIBLE; +SELECT show_index_status('idx_unique'); + +-- 9. Function-based index +CREATE INDEX idx_func ON index_test(get_data_length(data)); +SELECT show_index_status('idx_func'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; +ALTER INDEX idx_func INVISIBLE; +SELECT show_index_status('idx_func'); +EXPLAIN (COSTS OFF) SELECT * FROM index_test WHERE get_data_length(data) = 10; +ALTER INDEX idx_func VISIBLE; +SELECT show_index_status('idx_func'); + +-- 10. Join index +CREATE TABLE join_test (id INT PRIMARY KEY, ref_id INT); +INSERT INTO join_test SELECT g, g % 100 FROM generate_series(1, 1000) g; +CREATE INDEX idx_join ON join_test(ref_id); +SELECT show_index_status('idx_join'); + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + +-- Set all indexes to invisible to force seq scan +ALTER INDEX idx_join INVISIBLE; +ALTER INDEX join_test_pkey INVISIBLE; +SELECT show_index_status('idx_join'); +SELECT show_index_status('join_test_pkey'); + +EXPLAIN (COSTS OFF) +SELECT jt.id, it.data +FROM join_test jt +JOIN index_test it ON jt.ref_id = it.num +WHERE jt.id BETWEEN 100 AND 200; + +ALTER INDEX idx_join VISIBLE; +ALTER INDEX join_test_pkey VISIBLE; +SELECT show_index_status('idx_join'); +SELECT show_index_status('join_test_pkey'); + +-- Test REINDEX CONCURRENTLY +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + +ALTER INDEX idx_join INVISIBLE; +SELECT show_index_status('idx_join'); +REINDEX INDEX CONCURRENTLY idx_join; +SELECT show_index_status('idx_join'); + +SELECT pg_get_indexdef('idx_join'::regclass); +ALTER INDEX idx_join VISIBLE; +SELECT pg_get_indexdef('idx_join'::regclass); + +-- Test index visibility with indcheckxmin being true + +CREATE TABLE indcheckxmin_test(id int PRIMARY KEY); +INSERT INTO indcheckxmin_test VALUES (1), (2), (3); +CREATE INDEX CONCURRENTLY idx_checkxmin ON indcheckxmin_test(id); +BEGIN; +-- Simulate indcheckxmin being true +UPDATE pg_index SET indcheckxmin = true WHERE indexrelid = 'idx_checkxmin'::regclass; +ALTER INDEX idx_checkxmin INVISIBLE; -- expect fail +ROLLBACK; + +-- Test prepared statements with index visibility +CREATE TABLE prep_idx_test (id int, data text); +CREATE INDEX prep_idx1 ON prep_idx_test(data); +INSERT INTO prep_idx_test SELECT g, 'data ' || g FROM generate_series(1,100) g; + +-- Test with visible index +PREPARE idx_plan AS SELECT * FROM prep_idx_test WHERE data = $1; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); +EXECUTE idx_plan('data 1'); + +-- Test with invisible index +ALTER INDEX prep_idx1 INVISIBLE; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); +EXECUTE idx_plan('data 1'); + +-- Test with use_invisible_index +SET use_invisible_index TO on; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); +EXECUTE idx_plan('data 1'); +SET use_invisible_index TO off; +-- Assert plan cache is reset and index is not used +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); + +-- Make index visible and test again +ALTER INDEX prep_idx1 VISIBLE; +EXPLAIN (COSTS OFF) EXECUTE idx_plan('data 1'); +EXECUTE idx_plan('data 1'); + +-- Test index visibility with partitioned tables +CREATE TABLE part_tbl(id int, data text) PARTITION BY RANGE(id); +CREATE TABLE part1 PARTITION OF part_tbl FOR VALUES FROM (1) TO (100); +CREATE TABLE part2 PARTITION OF part_tbl FOR VALUES FROM (100) TO (200); + +INSERT INTO part_tbl +SELECT g, 'data ' || g +FROM generate_series(1, 199) g; + +CREATE INDEX idx_part_tbl ON part_tbl(data); + +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +JOIN pg_inherits inh ON i.indrelid = inh.inhrelid +WHERE inh.inhparent = 'part_tbl'::regclass::oid +UNION ALL +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +WHERE i.indrelid = 'part_tbl'::regclass::oid +ORDER BY index_name; + +-- Force use of indexes to avoid flaky test results +SET enable_seqscan = off; + +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + +ALTER INDEX idx_part_tbl INVISIBLE; + +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +JOIN pg_inherits inh ON i.indrelid = inh.inhrelid +WHERE inh.inhparent = 'part_tbl'::regclass::oid +UNION ALL +SELECT + c.relname AS index_name, + ct.relname AS table_name, + i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +JOIN pg_class ct ON i.indrelid = ct.oid +WHERE i.indrelid = 'part_tbl'::regclass::oid +ORDER BY index_name; + +-- Check query plan after setting invisible (should use seq scan) +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + +SET use_invisible_index TO on; + +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + +SET use_invisible_index TO off; + +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + +ALTER INDEX idx_part_tbl VISIBLE; + +SELECT c.relname, i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +WHERE c.relname LIKE 'idx_part_tbl%' +ORDER BY c.relname; + +EXPLAIN (COSTS OFF) +SELECT * FROM part_tbl WHERE data = 'data 50'; + +SET enable_seqscan = on; + +-- Test REINDEX maintains visibility setting +ALTER INDEX idx_part_tbl INVISIBLE; +REINDEX INDEX idx_part_tbl; + +SELECT c.relname, i.indisvisible +FROM pg_index i +JOIN pg_class c ON i.indexrelid = c.oid +WHERE c.relname LIKE 'idx_part_tbl%' +ORDER BY c.relname; + +-- Test that index visibility is preserved after ALTER TABLE +CREATE TABLE vis_test(id INT PRIMARY KEY, data text); +\d vis_test + +ALTER INDEX vis_test_pkey INVISIBLE; +\d vis_test + +ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE bigint; +\d vis_test + +ALTER TABLE vis_test ALTER COLUMN id SET DATA TYPE int; +\d vis_test + +ALTER INDEX vis_test_pkey VISIBLE; +\d vis_test + +-- Test CREATE INDEX with INVISIBLE option +CREATE TABLE invis_test(id int, data text); +INSERT INTO invis_test SELECT g, 'data ' || g FROM generate_series(1,100) g; + +CREATE INDEX idx_invis1 ON invis_test(data) INVISIBLE; +\d invis_test +\d idx_invis1 + +ALTER INDEX idx_invis1 VISIBLE; +\d invis_test +\d idx_invis1 + +-- Clean up +DROP TABLE index_test; +DROP TABLE join_test; +DROP FUNCTION get_data_length; +DROP FUNCTION show_index_status; +DROP TABLE indcheckxmin_test CASCADE; +DEALLOCATE idx_plan; +DROP TABLE prep_idx_test; +DROP TABLE part_tbl CASCADE; +DROP TABLE vis_test; +DROP TABLE invis_test; + -- Failure for unauthorized user CREATE ROLE regress_reindexuser NOLOGIN; SET SESSION ROLE regress_reindexuser; -- 2.39.5 (Apple Git-154)