REINDEX backend filtering

From a6de650a776daf0013d9ad31f1c78ab2a3623771 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v2] Add a new COLLATION option to REINDEX.

---
 doc/src/sgml/ref/reindex.sgml              | 13 +++++
 src/backend/catalog/index.c                | 59 +++++++++++++++++++++-
 src/backend/commands/indexcmds.c           | 13 +++--
 src/backend/utils/cache/relcache.c         | 43 ++++++++++++++++
 src/include/catalog/index.h                |  4 ++
 src/include/utils/relcache.h               |  1 +
 src/test/regress/expected/create_index.out | 10 ++++
 src/test/regress/sql/create_index.sql      | 10 ++++
 8 files changed, 149 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 627b36300c..505102482e 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -25,6 +25,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
+    COLLATION [ <replaceable class="parameter">text</replaceable> ]
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
 </synopsis>
@@ -168,6 +169,18 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>COLLATION</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild.  The
+      only allowed value is <literal>'not_current'</literal>, which will only
+      process indexes that depend on a collation version different than the
+      current one.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>CONCURRENTLY</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b8cd35e995..11ed68b8ea 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -99,6 +99,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool deprecated;	/* depends on at least on deprected collation? */
+} IndexHasDeprecatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1349,6 +1355,57 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one deprecated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_deprecated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasDeprecatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a deprecated collation version. */
+	if (context->deprecated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->deprecated = true;
+
+	return false;
+}
+
+bool
+index_has_deprecated_collation(Oid relid)
+{
+	ObjectAddress object;
+	IndexHasDeprecatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = relid;
+	object.objectSubId = 0;
+
+	context.relid = relid;
+	context.deprecated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_deprecated_collation,
+						&context);
+
+	return context.deprecated;
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3886,7 +3943,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index f9f3ff3b62..1d9886b4e3 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2474,6 +2474,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	ListCell   *lc;
 	bool		concurrently = false;
 	bool		verbose = false;
+	bool		coll_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2484,6 +2485,9 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			verbose = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "concurrently") == 0)
 			concurrently = defGetBoolean(opt);
+		else if ((strcmp(opt->defname, "collation") == 0)
+				 && (strcmp(defGetString(opt), "not_current") == 0))
+			coll_filter = true;
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2498,7 +2502,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(coll_filter ? REINDEXOPT_COLL_NOT_CURRENT : 0);
 
 	switch (stmt->kind)
 	{
@@ -3211,7 +3216,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 											  ShareUpdateExclusiveLock);
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3263,7 +3269,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..efef6c5ae5 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,49 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and filters out the result as required by the
+ * caller.  The filters are cumulative, although for now the only supported
+ * filter is for indexes that don't depend on deprecated collation version.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (!reindexHasFilter(options) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		/*
+		 * The caller wants to discard indexes that don't depend on a
+		 * deprecated collation version.
+		 */
+		if ((options & REINDEXOPT_COLL_NOT_CURRENT) != 0)
+		{
+			if (!index_has_deprecated_collation(indexOid))
+				continue;
+		}
+
+		/* Index passed all filters, keep it */
+		result = lappend_oid(result, indexOid);
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index 266f8950dc..740c281ddd 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -40,6 +40,9 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_COLL_NOT_CURRENT 0x10/* outdated collation only */
+
+#define reindexHasFilter(x)		((x & REINDEXOPT_COLL_NOT_CURRENT) != 0)
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -134,6 +137,7 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_deprecated_collation(Oid relid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index fc6afab58a..39a5b91d1a 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,16 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+NOTICE:  table "reindex_coll" has no indexes to reindex
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 824cb9f9e8..99f45c0d02 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.20.1

From 63afe51453d4413ad7e73c66268e6ff12bfe5436 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v3] Add a new COLLATION option to REINDEX.

---
 doc/src/sgml/ref/reindex.sgml              | 13 +++++
 src/backend/catalog/index.c                | 59 +++++++++++++++++++++-
 src/backend/commands/indexcmds.c           | 13 +++--
 src/backend/utils/cache/relcache.c         | 43 ++++++++++++++++
 src/include/catalog/index.h                |  4 ++
 src/include/utils/relcache.h               |  1 +
 src/test/regress/expected/create_index.out | 10 ++++
 src/test/regress/sql/create_index.sql      | 10 ++++
 8 files changed, 149 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 07795b5737..ead2904b67 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -25,6 +25,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
+    COLLATION [ <replaceable class="parameter">text</replaceable> ]
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
@@ -169,6 +170,18 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>COLLATION</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild.  The
+      only allowed value is <literal>'not_current'</literal>, which will only
+      process indexes that depend on a collation version different than the
+      current one.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>CONCURRENTLY</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 1cb9172a5f..b74ee79d38 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -100,6 +100,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool deprecated;	/* depends on at least on deprected collation? */
+} IndexHasDeprecatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1350,6 +1356,57 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one deprecated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_deprecated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasDeprecatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a deprecated collation version. */
+	if (context->deprecated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->deprecated = true;
+
+	return false;
+}
+
+bool
+index_has_deprecated_collation(Oid relid)
+{
+	ObjectAddress object;
+	IndexHasDeprecatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = relid;
+	object.objectSubId = 0;
+
+	context.relid = relid;
+	context.deprecated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_deprecated_collation,
+						&context);
+
+	return context.deprecated;
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3930,7 +3987,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 127ba7835d..9bf69ff9d7 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2475,6 +2475,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		concurrently = false;
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
+	bool		coll_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2487,6 +2488,9 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			concurrently = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "tablespace") == 0)
 			tablespacename = defGetString(opt);
+		else if ((strcmp(opt->defname, "collation") == 0)
+				 && (strcmp(defGetString(opt), "not_current") == 0))
+			coll_filter = true;
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2501,7 +2505,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(coll_filter ? REINDEXOPT_COLL_NOT_CURRENT : 0);
 
 	/*
 	 * Assign the tablespace OID to move indexes to, with InvalidOid to do
@@ -3298,7 +3303,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 									RelationGetRelationName(heapRelation))));
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3350,7 +3356,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..efef6c5ae5 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,49 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and filters out the result as required by the
+ * caller.  The filters are cumulative, although for now the only supported
+ * filter is for indexes that don't depend on deprecated collation version.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (!reindexHasFilter(options) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		/*
+		 * The caller wants to discard indexes that don't depend on a
+		 * deprecated collation version.
+		 */
+		if ((options & REINDEXOPT_COLL_NOT_CURRENT) != 0)
+		{
+			if (!index_has_deprecated_collation(indexOid))
+				continue;
+		}
+
+		/* Index passed all filters, keep it */
+		result = lappend_oid(result, indexOid);
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e22d506436..3f08e5f454 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -42,6 +42,9 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_COLL_NOT_CURRENT 0x10/* outdated collation only */
+
+#define reindexHasFilter(x)		((x & REINDEXOPT_COLL_NOT_CURRENT) != 0)
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -137,6 +140,7 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_deprecated_collation(Oid relid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index ce734f7ef3..3339fcb831 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,16 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+NOTICE:  table "reindex_coll" has no indexes to reindex
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index fd4f30876e..01d193b9de 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.20.1

From 1470d3544cffd841305ef34443853efb4d7ca31b Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v4] Add a new COLLATION option to REINDEX.

---
 doc/src/sgml/ref/reindex.sgml              | 13 +++++
 src/backend/catalog/index.c                | 60 +++++++++++++++++++++-
 src/backend/commands/indexcmds.c           | 13 +++--
 src/backend/utils/cache/relcache.c         | 43 ++++++++++++++++
 src/include/catalog/index.h                |  4 ++
 src/include/utils/relcache.h               |  1 +
 src/test/regress/expected/create_index.out | 10 ++++
 src/test/regress/sql/create_index.sql      | 10 ++++
 8 files changed, 150 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index b22d39eba9..3ced85c95c 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -25,6 +25,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
+    COLLATION [ <replaceable class="parameter">text</replaceable> ]
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
@@ -169,6 +170,18 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>COLLATION</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild.  The
+      only allowed value is <literal>'not_current'</literal>, which will only
+      process indexes that depend on a collation version different than the
+      current one.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>CONCURRENTLY</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4ef61b5efd..36759a97b0 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -100,6 +100,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool deprecated;	/* depends on at least on deprected collation? */
+} IndexHasDeprecatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1351,6 +1357,58 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one deprecated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_deprecated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasDeprecatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a deprecated collation version. */
+	if (context->deprecated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId,
+													false);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->deprecated = true;
+
+	return false;
+}
+
+bool
+index_has_deprecated_collation(Oid relid)
+{
+	ObjectAddress object;
+	IndexHasDeprecatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = relid;
+	object.objectSubId = 0;
+
+	context.relid = relid;
+	context.deprecated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_deprecated_collation,
+						&context);
+
+	return context.deprecated;
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3991,7 +4049,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8bc652ecd3..d536854576 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2484,6 +2484,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		concurrently = false;
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
+	bool		coll_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2496,6 +2497,9 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			concurrently = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "tablespace") == 0)
 			tablespacename = defGetString(opt);
+		else if ((strcmp(opt->defname, "collation") == 0)
+				 && (strcmp(defGetString(opt), "not_current") == 0))
+			coll_filter = true;
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2510,7 +2514,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(coll_filter ? REINDEXOPT_COLL_NOT_CURRENT : 0);
 
 	/*
 	 * Assign the tablespace OID to move indexes to, with InvalidOid to do
@@ -3307,7 +3312,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 									RelationGetRelationName(heapRelation))));
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3359,7 +3365,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..efef6c5ae5 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,49 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and filters out the result as required by the
+ * caller.  The filters are cumulative, although for now the only supported
+ * filter is for indexes that don't depend on deprecated collation version.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (!reindexHasFilter(options) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		/*
+		 * The caller wants to discard indexes that don't depend on a
+		 * deprecated collation version.
+		 */
+		if ((options & REINDEXOPT_COLL_NOT_CURRENT) != 0)
+		{
+			if (!index_has_deprecated_collation(indexOid))
+				continue;
+		}
+
+		/* Index passed all filters, keep it */
+		result = lappend_oid(result, indexOid);
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e22d506436..3f08e5f454 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -42,6 +42,9 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_COLL_NOT_CURRENT 0x10/* outdated collation only */
+
+#define reindexHasFilter(x)		((x & REINDEXOPT_COLL_NOT_CURRENT) != 0)
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -137,6 +140,7 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_deprecated_collation(Oid relid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 830fdddf24..1c9f22ee37 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,16 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+NOTICE:  table "reindex_coll" has no indexes to reindex
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8bc76f7c6f..fa9e05dba4 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (COLLATION 'not_current')
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (COLLATION 'not_current') TABLE reindex_coll;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.20.1

From 5703ce209d414dd7a6fba18f581eca4671364834 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v5 1/2] Add a new OUTDATED filtering facility for REINDEX
 command.

OUTDATED is added a new unreserved keyword.

When used, REINDEX will only process indexes that have an outdated dependency.
For now, only dependency on collations are supported but we'll likely support
other kind of dependency in the future.

Also add a new pg_index_has_outdated_dependency(regclass) SQL function, so
client code can filter such indexes if needed.  This function will also be used
in a following commit to teach reindexdb to use this new OUTDATED option and
order the tables by the amount of work that will actually be done.

Catversion (should be) bumped.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by:
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 doc/src/sgml/func.sgml                     |  27 ++++--
 doc/src/sgml/ref/reindex.sgml              |  12 +++
 src/backend/catalog/index.c                | 107 ++++++++++++++++++++-
 src/backend/commands/indexcmds.c           |  12 ++-
 src/backend/parser/gram.y                  |   4 +-
 src/backend/utils/cache/relcache.c         |  40 ++++++++
 src/bin/psql/tab-complete.c                |   2 +-
 src/include/catalog/index.h                |   3 +
 src/include/catalog/pg_proc.dat            |   4 +
 src/include/parser/kwlist.h                |   1 +
 src/include/utils/relcache.h               |   1 +
 src/test/regress/expected/create_index.out |  10 ++
 src/test/regress/sql/create_index.sql      |  10 ++
 13 files changed, 221 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index bf99f82149..2cf6e66234 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26381,12 +26381,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
 
    <para>
     <xref linkend="functions-admin-index-table"/> shows the functions
-    available for index maintenance tasks.  (Note that these maintenance
-    tasks are normally done automatically by autovacuum; use of these
-    functions is only required in special cases.)
-    These functions cannot be executed during recovery.
-    Use of these functions is restricted to superusers and the owner
-    of the given index.
+    available for index maintenance tasks.  (Note that the maintenance
+    tasks performing actions on indexes are normally done automatically by
+    autovacuum; use of these functions is only required in special cases.)
+    The functions performing actions on indexes cannot be executed during
+    recovery.
+    Use of the functions performing actions on indexes is restricted to
+    superusers and the owner of the given index.
    </para>
 
    <table id="functions-admin-index-table">
@@ -26471,6 +26472,20 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         option.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_index_has_outdated_dependency</primary>
+        </indexterm>
+        <function>pg_index_has_outdated_dependency</function> ( <parameter>index</parameter> <type>regclass</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Check if the specified index has any outdated dependency.  For now only
+        dependency on collations are supported.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index b22d39eba9..2d94d49cde 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
+    OUTDATED [ <replaceable class="parameter">boolean</replaceable> ]
     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
 </synopsis>
@@ -188,6 +189,17 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>OUTDATED</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild and only
+      process indexes that have outdated dependencies.  Fow now, the only
+      handle dependency is for the collation provider version.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TABLESPACE</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4ef61b5efd..571feac5db 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -100,6 +100,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool outdated;	/* depends on at least on deprected collation? */
+} IndexHasOutdatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1351,6 +1357,105 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one outdated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_outdated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasOutdatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a outdated collation version. */
+	if (context->outdated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId,
+													false);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->outdated = true;
+
+	return false;
+}
+
+Datum
+pg_index_has_outdated_dependency(PG_FUNCTION_ARGS)
+{
+	Oid			indexOid = PG_GETARG_OID(0);
+	Relation	rel;
+	bool		isIndex;
+	bool		res;
+
+	rel = try_relation_open(indexOid, AccessShareLock);
+
+	if (rel == NULL)
+		PG_RETURN_NULL();
+
+	isIndex = rel->rd_rel->relkind == RELKIND_INDEX;
+
+	if (!isIndex)
+	{
+		relation_close(rel, AccessShareLock);
+		PG_RETURN_NULL();
+	}
+
+	res = index_has_outdated_dependency(indexOid);
+
+	relation_close(rel, AccessShareLock);
+
+	PG_RETURN_BOOL(res);
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * collation version.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ */
+bool
+index_has_outdated_collation(Oid indexOid)
+{
+	ObjectAddress object;
+	IndexHasOutdatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = indexOid;
+	object.objectSubId = 0;
+
+	context.relid = indexOid;
+	context.outdated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_outdated_collation,
+						&context);
+
+	return context.outdated;
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * refobjversion.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ * For now, only dependency on collations are supported.
+ */
+bool
+index_has_outdated_dependency(Oid indexOid)
+{
+	return index_has_outdated_collation(indexOid);
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3991,7 +4096,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8bc652ecd3..0e1bcad101 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2484,6 +2484,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		concurrently = false;
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
+	bool		outdated_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2496,6 +2497,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			concurrently = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "tablespace") == 0)
 			tablespacename = defGetString(opt);
+		else if (strcmp(opt->defname, "outdated") == 0)
+			outdated_filter = defGetBoolean(opt);
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2510,7 +2513,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(outdated_filter ? REINDEXOPT_OUTDATED : 0);
 
 	/*
 	 * Assign the tablespace OID to move indexes to, with InvalidOid to do
@@ -3307,7 +3311,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 									RelationGetRelationName(heapRelation))));
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3359,7 +3364,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 652be0b96d..a67cfa867c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -674,7 +674,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	NULLS_P NUMERIC
 
 	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
-	ORDER ORDINALITY OTHERS OUT_P OUTER_P
+	ORDER ORDINALITY OTHERS OUT_P OUTDATED OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
 	PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
@@ -15430,6 +15430,7 @@ unreserved_keyword:
 			| OPTIONS
 			| ORDINALITY
 			| OTHERS
+			| OUTDATED
 			| OVER
 			| OVERRIDING
 			| OWNED
@@ -16002,6 +16003,7 @@ bare_label_keyword:
 			| ORDINALITY
 			| OTHERS
 			| OUT_P
+			| OUTDATED
 			| OUTER_P
 			| OVERLAY
 			| OVERRIDING
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..06545ee550 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,46 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and only keep indexes that have an outdated
+ * dependency.  For now, only collation version dependency is supported.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (((options & REINDEXOPT_OUTDATED) == 0) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		/*
+		 * Check for outdated collation version dependency.
+		 */
+		if (index_has_outdated_collation(indexOid))
+		{
+			result = lappend_oid(result, indexOid);
+			continue;
+		}
+
+		/* Didn't find any outdated dependency, index will be ignored. */
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 9f0208ac49..fac3e0476a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3674,7 +3674,7 @@ psql_completion(const char *text, int start, int end)
 		 * one word, so the above test is correct.
 		 */
 		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
-			COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+			COMPLETE_WITH("CONCURRENTLY", "OUTDATED'", "TABLESPACE", "VERBOSE");
 		else if (TailMatches("TABLESPACE"))
 			COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
 	}
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e22d506436..298c0c633c 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -42,6 +42,7 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_OUTDATED		0x10/* outdated collation only */
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -137,6 +138,8 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_outdated_collation(Oid indexOid);
+extern bool index_has_outdated_dependency(Oid indexOid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 3d3974f467..4874d33996 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -949,6 +949,10 @@
   proname => 'pg_indexam_has_property', provolatile => 's',
   prorettype => 'bool', proargtypes => 'oid text',
   prosrc => 'pg_indexam_has_property' },
+{ oid => '8102', descr => 'test property of an index',
+  proname => 'pg_index_has_outdated_dependency', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'regclass',
+  prosrc => 'pg_index_has_outdated_dependency' },
 { oid => '637', descr => 'test property of an index',
   proname => 'pg_index_has_property', provolatile => 's', prorettype => 'bool',
   proargtypes => 'regclass text', prosrc => 'pg_index_has_property' },
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 28083aaac9..e6c725d9a6 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -295,6 +295,7 @@ PG_KEYWORD("order", ORDER, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("outdated", OUTDATED, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 830fdddf24..5f7c49c650 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,16 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (OUTDATED) TABLE reindex_coll;
+NOTICE:  table "reindex_coll" has no indexes to reindex
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8bc76f7c6f..808cacee5d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (OUTDATED) TABLE reindex_coll;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.30.1

From 2f1388251a84badbc888fddfacb7c5357fcec873 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 25 Feb 2021 01:33:58 +0800
Subject: [PATCH v5 2/2] Add a --outdated option to reindexdb

This uses the new OUTDATED option for REINDEX.  If user asks for multiple job,
the list of tables to process will be sorted by the total size of underlying
indexes that have outdated dependency.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by:
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 src/bin/scripts/reindexdb.c        | 143 ++++++++++++++++++++++++-----
 src/bin/scripts/t/090_reindexdb.pl |  34 ++++++-
 2 files changed, 151 insertions(+), 26 deletions(-)

diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index cf28176243..369d164e65 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -35,20 +35,23 @@ typedef enum ReindexType
 static SimpleStringList *get_parallel_object_list(PGconn *conn,
 												  ReindexType type,
 												  SimpleStringList *user_list,
+												  bool outdated,
 												  bool echo);
 static void reindex_one_database(const ConnParams *cparams, ReindexType type,
 								 SimpleStringList *user_list,
 								 const char *progname,
 								 bool echo, bool verbose, bool concurrently,
-								 int concurrentCons, const char *tablespace);
+								 int concurrentCons, const char *tablespace,
+								 bool outdated);
 static void reindex_all_databases(ConnParams *cparams,
 								  const char *progname, bool echo,
 								  bool quiet, bool verbose, bool concurrently,
-								  int concurrentCons, const char *tablespace);
+								  int concurrentCons, const char *tablespace,
+								  bool outdated);
 static void run_reindex_command(PGconn *conn, ReindexType type,
 								const char *name, bool echo, bool verbose,
 								bool concurrently, bool async,
-								const char *tablespace);
+								const char *tablespace, bool outdated);
 
 static void help(const char *progname);
 
@@ -74,6 +77,7 @@ main(int argc, char *argv[])
 		{"concurrently", no_argument, NULL, 1},
 		{"maintenance-db", required_argument, NULL, 2},
 		{"tablespace", required_argument, NULL, 3},
+		{"outdated", no_argument, NULL, 4},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -95,6 +99,7 @@ main(int argc, char *argv[])
 	bool		quiet = false;
 	bool		verbose = false;
 	bool		concurrently = false;
+	bool		outdated = false;
 	SimpleStringList indexes = {NULL, NULL};
 	SimpleStringList tables = {NULL, NULL};
 	SimpleStringList schemas = {NULL, NULL};
@@ -170,6 +175,9 @@ main(int argc, char *argv[])
 			case 3:
 				tablespace = pg_strdup(optarg);
 				break;
+			case 4:
+				outdated = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -234,7 +242,8 @@ main(int argc, char *argv[])
 		cparams.dbname = maintenance_db;
 
 		reindex_all_databases(&cparams, progname, echo, quiet, verbose,
-							  concurrently, concurrentCons, tablespace);
+							  concurrently, concurrentCons, tablespace,
+							  outdated);
 	}
 	else if (syscatalog)
 	{
@@ -253,12 +262,17 @@ main(int argc, char *argv[])
 			pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
 			exit(1);
 		}
-
 		if (concurrentCons > 1)
 		{
 			pg_log_error("cannot use multiple jobs to reindex system catalogs");
 			exit(1);
 		}
+		if (outdated)
+		{
+			pg_log_error("cannot filter indexes having outdated dependencies "
+						 "and reindex system catalogs at the same time");
+			exit(1);
+		}
 
 		if (dbname == NULL)
 		{
@@ -274,7 +288,7 @@ main(int argc, char *argv[])
 
 		reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
 							 progname, echo, verbose,
-							 concurrently, 1, tablespace);
+							 concurrently, 1, tablespace, outdated);
 	}
 	else
 	{
@@ -304,17 +318,20 @@ main(int argc, char *argv[])
 		if (schemas.head != NULL)
 			reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		if (indexes.head != NULL)
 			reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
 								 progname, echo, verbose,
-								 concurrently, 1, tablespace);
+								 concurrently, 1, tablespace,
+								 outdated);
 
 		if (tables.head != NULL)
 			reindex_one_database(&cparams, REINDEX_TABLE, &tables,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		/*
 		 * reindex database only if neither index nor table nor schema is
@@ -323,7 +340,8 @@ main(int argc, char *argv[])
 		if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
 			reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 	}
 
 	exit(0);
@@ -334,7 +352,7 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
 					 SimpleStringList *user_list,
 					 const char *progname, bool echo,
 					 bool verbose, bool concurrently, int concurrentCons,
-					 const char *tablespace)
+					 const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	SimpleStringListCell *cell;
@@ -363,6 +381,14 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
 		exit(1);
 	}
 
+	if (outdated && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "outdated", "14");
+		exit(1);
+	}
+
 	if (!parallel)
 	{
 		switch (process_type)
@@ -399,14 +425,24 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
 				 */
 				if (concurrently)
 					pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
+				else if (outdated)
+				{
+					/*
+					 * The only supported kind of object that can be outdated
+					 * is collation.  No system catalog has any index that can
+					 * depend on an outdated collation, so skip system
+					 * catalogs.
+					 */
+				}
 				else
 					run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo,
 										verbose, concurrently, false,
-										tablespace);
+										tablespace, outdated);
 
 				/* Build a list of relations from the database */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -419,7 +455,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
 
 				/* Build a list of relations from all the schemas */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -484,7 +521,8 @@ reindex_one_database(const ConnParams *cparams, ReindexType type,
 
 		ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
 		run_reindex_command(free_slot->connection, process_type, objname,
-							echo, verbose, concurrently, true, tablespace);
+							echo, verbose, concurrently, true, tablespace,
+							outdated);
 
 		cell = cell->next;
 	} while (cell != NULL);
@@ -509,7 +547,7 @@ finish:
 static void
 run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 					bool echo, bool verbose, bool concurrently, bool async,
-					const char *tablespace)
+					const char *tablespace, bool outdated)
 {
 	const char *paren = "(";
 	const char *comma = ", ";
@@ -536,6 +574,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 		sep = comma;
 	}
 
+	if (outdated)
+	{
+		appendPQExpBuffer(&sql, "%sOUTDATED", sep);
+		sep = comma;
+	}
+
 	if (sep != paren)
 		appendPQExpBufferStr(&sql, ") ");
 
@@ -641,7 +685,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
  */
 static SimpleStringList *
 get_parallel_object_list(PGconn *conn, ReindexType type,
-						 SimpleStringList *user_list, bool echo)
+						 SimpleStringList *user_list, bool outdated, bool echo)
 {
 	PQExpBufferData catalog_query;
 	PQExpBufferData buf;
@@ -660,16 +704,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 	{
 		case REINDEX_DATABASE:
 			Assert(user_list == NULL);
+
 			appendPQExpBufferStr(&catalog_query,
 								 "SELECT c.relname, ns.nspname\n"
 								 " FROM pg_catalog.pg_class c\n"
 								 " JOIN pg_catalog.pg_namespace ns"
-								 " ON c.relnamespace = ns.oid\n"
+								 " ON c.relnamespace = ns.oid\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " JOIN pg_catalog.pg_index i"
+									 " ON c.oid = i.indrelid\n"
+									 " JOIN pg_catalog.pg_class ci"
+									 " ON i.indexrelid = ci.oid\n");
+			}
+
+			appendPQExpBufferStr(&catalog_query,
 								 " WHERE ns.nspname != 'pg_catalog'\n"
 								 "   AND c.relkind IN ("
 								 CppAsString2(RELKIND_RELATION) ", "
-								 CppAsString2(RELKIND_MATVIEW) ")\n"
-								 " ORDER BY c.relpages DESC;");
+								 CppAsString2(RELKIND_MATVIEW) ")\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " GROUP BY c.relname, ns.nspname\n"
+									 " ORDER BY sum(ci.relpages)"
+									 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+			}
+			else
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " ORDER BY c.relpages DESC;");
+			}
+
 			break;
 
 		case REINDEX_SCHEMA:
@@ -687,7 +756,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 									 "SELECT c.relname, ns.nspname\n"
 									 " FROM pg_catalog.pg_class c\n"
 									 " JOIN pg_catalog.pg_namespace ns"
-									 " ON c.relnamespace = ns.oid\n"
+									 " ON c.relnamespace = ns.oid\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " JOIN pg_catalog.pg_index i"
+										 " ON c.oid = i.indrelid\n"
+										 " JOIN pg_catalog.pg_class ci"
+										 " ON i.indexrelid = ci.oid\n");
+				}
+
+				appendPQExpBufferStr(&catalog_query,
 									 " WHERE c.relkind IN ("
 									 CppAsString2(RELKIND_RELATION) ", "
 									 CppAsString2(RELKIND_MATVIEW) ")\n"
@@ -705,8 +785,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 					appendStringLiteralConn(&catalog_query, nspname, conn);
 				}
 
-				appendPQExpBufferStr(&catalog_query, ")\n"
-									 " ORDER BY c.relpages DESC;");
+				appendPQExpBufferStr(&catalog_query, ")\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " GROUP BY c.relname, ns.nspname\n"
+										 " ORDER BY sum(ci.relpages)"
+										 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+				}
+				else
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " ORDER BY c.relpages DESC;");
+				}
 			}
 			break;
 
@@ -754,7 +846,7 @@ static void
 reindex_all_databases(ConnParams *cparams,
 					  const char *progname, bool echo, bool quiet, bool verbose,
 					  bool concurrently, int concurrentCons,
-					  const char *tablespace)
+					  const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	PGresult   *result;
@@ -778,7 +870,7 @@ reindex_all_databases(ConnParams *cparams,
 
 		reindex_one_database(cparams, REINDEX_DATABASE, NULL,
 							 progname, echo, verbose, concurrently,
-							 concurrentCons, tablespace);
+							 concurrentCons, tablespace, outdated);
 	}
 
 	PQclear(result);
@@ -797,6 +889,7 @@ help(const char *progname)
 	printf(_("  -e, --echo                   show the commands being sent to the server\n"));
 	printf(_("  -i, --index=INDEX            recreate specific index(es) only\n"));
 	printf(_("  -j, --jobs=NUM               use this many concurrent connections to reindex\n"));
+	printf(_("      --outdated               only process indexes having outdated depencies\n"));
 	printf(_("  -q, --quiet                  don't write any messages\n"));
 	printf(_("  -s, --system                 reindex system catalogs\n"));
 	printf(_("  -S, --schema=SCHEMA          reindex specific schema(s) only\n"));
diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl
index 159b637230..b60cac6081 100644
--- a/src/bin/scripts/t/090_reindexdb.pl
+++ b/src/bin/scripts/t/090_reindexdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 58;
+use Test::More tests => 70;
 
 program_help_ok('reindexdb');
 program_version_ok('reindexdb');
@@ -174,6 +174,9 @@ $node->command_fails(
 $node->command_fails(
 	[ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ],
 	'parallel reindexdb cannot process indexes');
+$node->command_fails(
+	[ 'reindexdb', '-s', '--outdated' ],
+	'cannot reindex system catalog and filter indexes having outdated dependencies');
 $node->issues_sql_like(
 	[ 'reindexdb', '-j', '2', 'postgres' ],
 	qr/statement:\ REINDEX SYSTEM postgres;
@@ -196,3 +199,32 @@ $node->command_checks_all(
 		qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s
 	],
 	'parallel reindexdb for system with --concurrently skips catalogs');
+
+# Temporarily downgrade client-min-message to get the no-op report
+$ENV{PGOPTIONS} = '--client-min-messages=NOTICE';
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-t', 's1.t1', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'verbose reindexdb for outdated dependencies on a specific table reports no-op tables');
+
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/^$/],
+	'verbose reindexdb for outdated dependencies database wide silently ignore all tables');
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-j', '2', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables');
+
+# Switch back to WARNING client-min-message
+$ENV{PGOPTIONS} = '--client-min-messages=WARNING';
+$node->issues_sql_like(
+	[ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ],
+	qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/,
+	'reindexdb for outdated dependencies specify the OUTDATED keyword');
-- 
2.30.1

From d2e05e6f64c88b0d5074b9963586bf6999276762 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v6 1/2] Add a new OUTDATED filtering facility for REINDEX
 command.

OUTDATED is added a new unreserved keyword.

When used, REINDEX will only process indexes that have an outdated dependency.
For now, only dependency on collations are supported but we'll likely support
other kind of dependency in the future.

Also add a new pg_index_has_outdated_dependency(regclass) SQL function, so
client code can filter such indexes if needed.  This function will also be used
in a following commit to teach reindexdb to use this new OUTDATED option and
order the tables by the amount of work that will actually be done.

Catversion (should be) bumped.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by:
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 doc/src/sgml/func.sgml                     |  27 ++++--
 doc/src/sgml/ref/reindex.sgml              |  12 +++
 src/backend/catalog/index.c                | 107 ++++++++++++++++++++-
 src/backend/commands/indexcmds.c           |  12 ++-
 src/backend/parser/gram.y                  |   4 +-
 src/backend/utils/cache/relcache.c         |  40 ++++++++
 src/bin/psql/tab-complete.c                |   2 +-
 src/include/catalog/index.h                |   3 +
 src/include/catalog/pg_proc.dat            |   4 +
 src/include/parser/kwlist.h                |   1 +
 src/include/utils/relcache.h               |   1 +
 src/test/regress/expected/create_index.out |  10 ++
 src/test/regress/sql/create_index.sql      |  10 ++
 13 files changed, 221 insertions(+), 12 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9492a3c6b9..0eda6678ac 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26448,12 +26448,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
 
    <para>
     <xref linkend="functions-admin-index-table"/> shows the functions
-    available for index maintenance tasks.  (Note that these maintenance
-    tasks are normally done automatically by autovacuum; use of these
-    functions is only required in special cases.)
-    These functions cannot be executed during recovery.
-    Use of these functions is restricted to superusers and the owner
-    of the given index.
+    available for index maintenance tasks.  (Note that the maintenance
+    tasks performing actions on indexes are normally done automatically by
+    autovacuum; use of these functions is only required in special cases.)
+    The functions performing actions on indexes cannot be executed during
+    recovery.
+    Use of the functions performing actions on indexes is restricted to
+    superusers and the owner of the given index.
    </para>
 
    <table id="functions-admin-index-table">
@@ -26538,6 +26539,20 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         option.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_index_has_outdated_dependency</primary>
+        </indexterm>
+        <function>pg_index_has_outdated_dependency</function> ( <parameter>index</parameter> <type>regclass</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Check if the specified index has any outdated dependency.  For now only
+        dependency on collations are supported.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index ff4dba8c36..aa66e6461f 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
+    OUTDATED [ <replaceable class="parameter">boolean</replaceable> ]
     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
 </synopsis>
@@ -188,6 +189,17 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>OUTDATED</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild and only
+      process indexes that have outdated dependencies.  Fow now, the only
+      handle dependency is for the collation provider version.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TABLESPACE</literal></term>
     <listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4ef61b5efd..571feac5db 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -100,6 +100,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool outdated;	/* depends on at least on deprected collation? */
+} IndexHasOutdatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1351,6 +1357,105 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one outdated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_outdated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasOutdatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a outdated collation version. */
+	if (context->outdated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId,
+													false);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->outdated = true;
+
+	return false;
+}
+
+Datum
+pg_index_has_outdated_dependency(PG_FUNCTION_ARGS)
+{
+	Oid			indexOid = PG_GETARG_OID(0);
+	Relation	rel;
+	bool		isIndex;
+	bool		res;
+
+	rel = try_relation_open(indexOid, AccessShareLock);
+
+	if (rel == NULL)
+		PG_RETURN_NULL();
+
+	isIndex = rel->rd_rel->relkind == RELKIND_INDEX;
+
+	if (!isIndex)
+	{
+		relation_close(rel, AccessShareLock);
+		PG_RETURN_NULL();
+	}
+
+	res = index_has_outdated_dependency(indexOid);
+
+	relation_close(rel, AccessShareLock);
+
+	PG_RETURN_BOOL(res);
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * collation version.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ */
+bool
+index_has_outdated_collation(Oid indexOid)
+{
+	ObjectAddress object;
+	IndexHasOutdatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = indexOid;
+	object.objectSubId = 0;
+
+	context.relid = indexOid;
+	context.outdated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_outdated_collation,
+						&context);
+
+	return context.outdated;
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * refobjversion.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ * For now, only dependency on collations are supported.
+ */
+bool
+index_has_outdated_dependency(Oid indexOid)
+{
+	return index_has_outdated_collation(indexOid);
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3991,7 +4096,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8bc652ecd3..0e1bcad101 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2484,6 +2484,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		concurrently = false;
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
+	bool		outdated_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2496,6 +2497,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			concurrently = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "tablespace") == 0)
 			tablespacename = defGetString(opt);
+		else if (strcmp(opt->defname, "outdated") == 0)
+			outdated_filter = defGetBoolean(opt);
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2510,7 +2513,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(outdated_filter ? REINDEXOPT_OUTDATED : 0);
 
 	/*
 	 * Assign the tablespace OID to move indexes to, with InvalidOid to do
@@ -3307,7 +3311,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 									RelationGetRelationName(heapRelation))));
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3359,7 +3364,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 652be0b96d..a67cfa867c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -674,7 +674,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	NULLS_P NUMERIC
 
 	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
-	ORDER ORDINALITY OTHERS OUT_P OUTER_P
+	ORDER ORDINALITY OTHERS OUT_P OUTDATED OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
 	PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
@@ -15430,6 +15430,7 @@ unreserved_keyword:
 			| OPTIONS
 			| ORDINALITY
 			| OTHERS
+			| OUTDATED
 			| OVER
 			| OVERRIDING
 			| OWNED
@@ -16002,6 +16003,7 @@ bare_label_keyword:
 			| ORDINALITY
 			| OTHERS
 			| OUT_P
+			| OUTDATED
 			| OUTER_P
 			| OVERLAY
 			| OVERRIDING
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..06545ee550 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,46 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and only keep indexes that have an outdated
+ * dependency.  For now, only collation version dependency is supported.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (((options & REINDEXOPT_OUTDATED) == 0) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		/*
+		 * Check for outdated collation version dependency.
+		 */
+		if (index_has_outdated_collation(indexOid))
+		{
+			result = lappend_oid(result, indexOid);
+			continue;
+		}
+
+		/* Didn't find any outdated dependency, index will be ignored. */
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ecdb8d752b..0843f36580 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3674,7 +3674,7 @@ psql_completion(const char *text, int start, int end)
 		 * one word, so the above test is correct.
 		 */
 		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
-			COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+			COMPLETE_WITH("CONCURRENTLY", "OUTDATED'", "TABLESPACE", "VERBOSE");
 		else if (TailMatches("TABLESPACE"))
 			COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
 	}
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e22d506436..298c0c633c 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -42,6 +42,7 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_OUTDATED		0x10/* outdated collation only */
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -137,6 +138,8 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_outdated_collation(Oid indexOid);
+extern bool index_has_outdated_dependency(Oid indexOid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 93393fcfd4..911c12ee2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -949,6 +949,10 @@
   proname => 'pg_indexam_has_property', provolatile => 's',
   prorettype => 'bool', proargtypes => 'oid text',
   prosrc => 'pg_indexam_has_property' },
+{ oid => '8102', descr => 'test property of an index',
+  proname => 'pg_index_has_outdated_dependency', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'regclass',
+  prosrc => 'pg_index_has_outdated_dependency' },
 { oid => '637', descr => 'test property of an index',
   proname => 'pg_index_has_property', provolatile => 's', prorettype => 'bool',
   proargtypes => 'regclass text', prosrc => 'pg_index_has_property' },
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 28083aaac9..e6c725d9a6 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -295,6 +295,7 @@ PG_KEYWORD("order", ORDER, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("outdated", OUTDATED, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 830fdddf24..5f7c49c650 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,16 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (OUTDATED) TABLE reindex_coll;
+NOTICE:  table "reindex_coll" has no indexes to reindex
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8bc76f7c6f..808cacee5d 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,16 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (OUTDATED) TABLE reindex_coll;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.30.1

From 6fe675dbe236d6fae1fbd2ec0b1f9b83607014e9 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 25 Feb 2021 01:33:58 +0800
Subject: [PATCH v6 2/2] Add a --outdated option to reindexdb

This uses the new OUTDATED option for REINDEX.  If user asks for multiple job,
the list of tables to process will be sorted by the total size of underlying
indexes that have outdated dependency.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by:
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 src/bin/scripts/reindexdb.c        | 143 ++++++++++++++++++++++++-----
 src/bin/scripts/t/090_reindexdb.pl |  34 ++++++-
 2 files changed, 151 insertions(+), 26 deletions(-)

diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index fc0681538a..addea5c1ed 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -35,20 +35,23 @@ typedef enum ReindexType
 static SimpleStringList *get_parallel_object_list(PGconn *conn,
 												  ReindexType type,
 												  SimpleStringList *user_list,
+												  bool outdated,
 												  bool echo);
 static void reindex_one_database(ConnParams *cparams, ReindexType type,
 								 SimpleStringList *user_list,
 								 const char *progname,
 								 bool echo, bool verbose, bool concurrently,
-								 int concurrentCons, const char *tablespace);
+								 int concurrentCons, const char *tablespace,
+								 bool outdated);
 static void reindex_all_databases(ConnParams *cparams,
 								  const char *progname, bool echo,
 								  bool quiet, bool verbose, bool concurrently,
-								  int concurrentCons, const char *tablespace);
+								  int concurrentCons, const char *tablespace,
+								  bool outdated);
 static void run_reindex_command(PGconn *conn, ReindexType type,
 								const char *name, bool echo, bool verbose,
 								bool concurrently, bool async,
-								const char *tablespace);
+								const char *tablespace, bool outdated);
 
 static void help(const char *progname);
 
@@ -74,6 +77,7 @@ main(int argc, char *argv[])
 		{"concurrently", no_argument, NULL, 1},
 		{"maintenance-db", required_argument, NULL, 2},
 		{"tablespace", required_argument, NULL, 3},
+		{"outdated", no_argument, NULL, 4},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -95,6 +99,7 @@ main(int argc, char *argv[])
 	bool		quiet = false;
 	bool		verbose = false;
 	bool		concurrently = false;
+	bool		outdated = false;
 	SimpleStringList indexes = {NULL, NULL};
 	SimpleStringList tables = {NULL, NULL};
 	SimpleStringList schemas = {NULL, NULL};
@@ -170,6 +175,9 @@ main(int argc, char *argv[])
 			case 3:
 				tablespace = pg_strdup(optarg);
 				break;
+			case 4:
+				outdated = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -234,7 +242,8 @@ main(int argc, char *argv[])
 		cparams.dbname = maintenance_db;
 
 		reindex_all_databases(&cparams, progname, echo, quiet, verbose,
-							  concurrently, concurrentCons, tablespace);
+							  concurrently, concurrentCons, tablespace,
+							  outdated);
 	}
 	else if (syscatalog)
 	{
@@ -253,12 +262,17 @@ main(int argc, char *argv[])
 			pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
 			exit(1);
 		}
-
 		if (concurrentCons > 1)
 		{
 			pg_log_error("cannot use multiple jobs to reindex system catalogs");
 			exit(1);
 		}
+		if (outdated)
+		{
+			pg_log_error("cannot filter indexes having outdated dependencies "
+						 "and reindex system catalogs at the same time");
+			exit(1);
+		}
 
 		if (dbname == NULL)
 		{
@@ -274,7 +288,7 @@ main(int argc, char *argv[])
 
 		reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
 							 progname, echo, verbose,
-							 concurrently, 1, tablespace);
+							 concurrently, 1, tablespace, outdated);
 	}
 	else
 	{
@@ -304,17 +318,20 @@ main(int argc, char *argv[])
 		if (schemas.head != NULL)
 			reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		if (indexes.head != NULL)
 			reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
 								 progname, echo, verbose,
-								 concurrently, 1, tablespace);
+								 concurrently, 1, tablespace,
+								 outdated);
 
 		if (tables.head != NULL)
 			reindex_one_database(&cparams, REINDEX_TABLE, &tables,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		/*
 		 * reindex database only if neither index nor table nor schema is
@@ -323,7 +340,8 @@ main(int argc, char *argv[])
 		if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
 			reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 	}
 
 	exit(0);
@@ -334,7 +352,7 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 					 SimpleStringList *user_list,
 					 const char *progname, bool echo,
 					 bool verbose, bool concurrently, int concurrentCons,
-					 const char *tablespace)
+					 const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	SimpleStringListCell *cell;
@@ -363,6 +381,14 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 		exit(1);
 	}
 
+	if (outdated && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "outdated", "14");
+		exit(1);
+	}
+
 	if (!parallel)
 	{
 		switch (process_type)
@@ -399,14 +425,24 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 				 */
 				if (concurrently)
 					pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
+				else if (outdated)
+				{
+					/*
+					 * The only supported kind of object that can be outdated
+					 * is collation.  No system catalog has any index that can
+					 * depend on an outdated collation, so skip system
+					 * catalogs.
+					 */
+				}
 				else
 					run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo,
 										verbose, concurrently, false,
-										tablespace);
+										tablespace, outdated);
 
 				/* Build a list of relations from the database */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -419,7 +455,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 
 				/* Build a list of relations from all the schemas */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -485,7 +522,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 
 		ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
 		run_reindex_command(free_slot->connection, process_type, objname,
-							echo, verbose, concurrently, true, tablespace);
+							echo, verbose, concurrently, true, tablespace,
+							outdated);
 
 		cell = cell->next;
 	} while (cell != NULL);
@@ -510,7 +548,7 @@ finish:
 static void
 run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 					bool echo, bool verbose, bool concurrently, bool async,
-					const char *tablespace)
+					const char *tablespace, bool outdated)
 {
 	const char *paren = "(";
 	const char *comma = ", ";
@@ -537,6 +575,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 		sep = comma;
 	}
 
+	if (outdated)
+	{
+		appendPQExpBuffer(&sql, "%sOUTDATED", sep);
+		sep = comma;
+	}
+
 	if (sep != paren)
 		appendPQExpBufferStr(&sql, ") ");
 
@@ -642,7 +686,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
  */
 static SimpleStringList *
 get_parallel_object_list(PGconn *conn, ReindexType type,
-						 SimpleStringList *user_list, bool echo)
+						 SimpleStringList *user_list, bool outdated, bool echo)
 {
 	PQExpBufferData catalog_query;
 	PQExpBufferData buf;
@@ -661,16 +705,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 	{
 		case REINDEX_DATABASE:
 			Assert(user_list == NULL);
+
 			appendPQExpBufferStr(&catalog_query,
 								 "SELECT c.relname, ns.nspname\n"
 								 " FROM pg_catalog.pg_class c\n"
 								 " JOIN pg_catalog.pg_namespace ns"
-								 " ON c.relnamespace = ns.oid\n"
+								 " ON c.relnamespace = ns.oid\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " JOIN pg_catalog.pg_index i"
+									 " ON c.oid = i.indrelid\n"
+									 " JOIN pg_catalog.pg_class ci"
+									 " ON i.indexrelid = ci.oid\n");
+			}
+
+			appendPQExpBufferStr(&catalog_query,
 								 " WHERE ns.nspname != 'pg_catalog'\n"
 								 "   AND c.relkind IN ("
 								 CppAsString2(RELKIND_RELATION) ", "
-								 CppAsString2(RELKIND_MATVIEW) ")\n"
-								 " ORDER BY c.relpages DESC;");
+								 CppAsString2(RELKIND_MATVIEW) ")\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " GROUP BY c.relname, ns.nspname\n"
+									 " ORDER BY sum(ci.relpages)"
+									 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+			}
+			else
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " ORDER BY c.relpages DESC;");
+			}
+
 			break;
 
 		case REINDEX_SCHEMA:
@@ -688,7 +757,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 									 "SELECT c.relname, ns.nspname\n"
 									 " FROM pg_catalog.pg_class c\n"
 									 " JOIN pg_catalog.pg_namespace ns"
-									 " ON c.relnamespace = ns.oid\n"
+									 " ON c.relnamespace = ns.oid\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " JOIN pg_catalog.pg_index i"
+										 " ON c.oid = i.indrelid\n"
+										 " JOIN pg_catalog.pg_class ci"
+										 " ON i.indexrelid = ci.oid\n");
+				}
+
+				appendPQExpBufferStr(&catalog_query,
 									 " WHERE c.relkind IN ("
 									 CppAsString2(RELKIND_RELATION) ", "
 									 CppAsString2(RELKIND_MATVIEW) ")\n"
@@ -706,8 +786,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 					appendStringLiteralConn(&catalog_query, nspname, conn);
 				}
 
-				appendPQExpBufferStr(&catalog_query, ")\n"
-									 " ORDER BY c.relpages DESC;");
+				appendPQExpBufferStr(&catalog_query, ")\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " GROUP BY c.relname, ns.nspname\n"
+										 " ORDER BY sum(ci.relpages)"
+										 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+				}
+				else
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " ORDER BY c.relpages DESC;");
+				}
 			}
 			break;
 
@@ -755,7 +847,7 @@ static void
 reindex_all_databases(ConnParams *cparams,
 					  const char *progname, bool echo, bool quiet, bool verbose,
 					  bool concurrently, int concurrentCons,
-					  const char *tablespace)
+					  const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	PGresult   *result;
@@ -779,7 +871,7 @@ reindex_all_databases(ConnParams *cparams,
 
 		reindex_one_database(cparams, REINDEX_DATABASE, NULL,
 							 progname, echo, verbose, concurrently,
-							 concurrentCons, tablespace);
+							 concurrentCons, tablespace, outdated);
 	}
 
 	PQclear(result);
@@ -798,6 +890,7 @@ help(const char *progname)
 	printf(_("  -e, --echo                   show the commands being sent to the server\n"));
 	printf(_("  -i, --index=INDEX            recreate specific index(es) only\n"));
 	printf(_("  -j, --jobs=NUM               use this many concurrent connections to reindex\n"));
+	printf(_("      --outdated               only process indexes having outdated depencies\n"));
 	printf(_("  -q, --quiet                  don't write any messages\n"));
 	printf(_("  -s, --system                 reindex system catalogs\n"));
 	printf(_("  -S, --schema=SCHEMA          reindex specific schema(s) only\n"));
diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl
index 159b637230..b60cac6081 100644
--- a/src/bin/scripts/t/090_reindexdb.pl
+++ b/src/bin/scripts/t/090_reindexdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 58;
+use Test::More tests => 70;
 
 program_help_ok('reindexdb');
 program_version_ok('reindexdb');
@@ -174,6 +174,9 @@ $node->command_fails(
 $node->command_fails(
 	[ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ],
 	'parallel reindexdb cannot process indexes');
+$node->command_fails(
+	[ 'reindexdb', '-s', '--outdated' ],
+	'cannot reindex system catalog and filter indexes having outdated dependencies');
 $node->issues_sql_like(
 	[ 'reindexdb', '-j', '2', 'postgres' ],
 	qr/statement:\ REINDEX SYSTEM postgres;
@@ -196,3 +199,32 @@ $node->command_checks_all(
 		qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s
 	],
 	'parallel reindexdb for system with --concurrently skips catalogs');
+
+# Temporarily downgrade client-min-message to get the no-op report
+$ENV{PGOPTIONS} = '--client-min-messages=NOTICE';
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-t', 's1.t1', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'verbose reindexdb for outdated dependencies on a specific table reports no-op tables');
+
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/^$/],
+	'verbose reindexdb for outdated dependencies database wide silently ignore all tables');
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-j', '2', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables');
+
+# Switch back to WARNING client-min-message
+$ENV{PGOPTIONS} = '--client-min-messages=WARNING';
+$node->issues_sql_like(
+	[ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ],
+	qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/,
+	'reindexdb for outdated dependencies specify the OUTDATED keyword');
-- 
2.30.1

From 91bcd6e4565164314eb6444635ca274695de3748 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 3 Dec 2020 15:54:42 +0800
Subject: [PATCH v7 1/2] Add a new OUTDATED filtering facility for REINDEX
 command.

OUTDATED is added a new unreserved keyword.

When used, REINDEX will only process indexes that have an outdated dependency.
For now, only dependency on collations are supported but we'll likely support
other kind of dependency in the future.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by: Michael Paquier, Mark Dilger
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 doc/src/sgml/ref/reindex.sgml                 | 12 +++
 src/backend/access/index/indexam.c            | 59 ++++++++++++++
 src/backend/catalog/index.c                   | 79 ++++++++++++++++++-
 src/backend/commands/indexcmds.c              | 37 ++++++++-
 src/backend/parser/gram.y                     |  4 +-
 src/backend/utils/cache/relcache.c            | 47 +++++++++++
 src/bin/psql/tab-complete.c                   |  2 +-
 src/include/access/genam.h                    |  1 +
 src/include/catalog/index.h                   |  3 +
 src/include/parser/kwlist.h                   |  1 +
 src/include/utils/relcache.h                  |  1 +
 .../regress/expected/collate.icu.utf8.out     | 12 ++-
 src/test/regress/expected/create_index.out    | 27 +++++++
 src/test/regress/sql/collate.icu.utf8.sql     | 12 ++-
 src/test/regress/sql/create_index.sql         | 18 +++++
 15 files changed, 301 insertions(+), 14 deletions(-)

diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index ff4dba8c36..c4749c338b 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -26,6 +26,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
     CONCURRENTLY [ <replaceable class="parameter">boolean</replaceable> ]
+    OUTDATED [ <replaceable class="parameter">boolean</replaceable> ]
     TABLESPACE <replaceable class="parameter">new_tablespace</replaceable>
     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
 </synopsis>
@@ -188,6 +189,17 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term><literal>OUTDATED</literal></term>
+    <listitem>
+     <para>
+      This option can be used to filter the list of indexes to rebuild and only
+      process indexes that have outdated dependencies.  Fow now, the only
+      dependency handled currently is the collation provider version.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term><literal>TABLESPACE</literal></term>
     <listitem>
diff --git a/src/backend/access/index/indexam.c b/src/backend/access/index/indexam.c
index 3d2dbed708..dc1c85cf0d 100644
--- a/src/backend/access/index/indexam.c
+++ b/src/backend/access/index/indexam.c
@@ -145,6 +145,65 @@ index_open(Oid relationId, LOCKMODE lockmode)
 	return r;
 }
 
+/* ----------------
+ *		try_index_open - open an index relation by relation OID
+ *
+ *		Same as index_open, except return NULL instead of failing
+ *		if the index does not exist.
+ * ----------------
+ */
+Relation
+try_index_open(Oid relationId, LOCKMODE lockmode)
+{
+	Relation	r;
+
+	Assert(lockmode >= NoLock && lockmode < MAX_LOCKMODES);
+
+	/* Get the lock first */
+	if (lockmode != NoLock)
+		LockRelationOid(relationId, lockmode);
+
+	/*
+	 * Now that we have the lock, probe to see if the relation really exists
+	 * or not.
+	 */
+	if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(relationId)))
+	{
+		/* Release useless lock */
+		if (lockmode != NoLock)
+			UnlockRelationOid(relationId, lockmode);
+
+		return NULL;
+	}
+
+	/* Should be safe to do a relcache load */
+	r = RelationIdGetRelation(relationId);
+
+	if (!RelationIsValid(r))
+		elog(ERROR, "could not open relation with OID %u", relationId);
+
+	/* If we didn't get the lock ourselves, assert that caller holds one */
+	Assert(lockmode != NoLock ||
+		   CheckRelationLockedByMe(r, AccessShareLock, true));
+
+	if (r->rd_rel->relkind != RELKIND_INDEX &&
+		r->rd_rel->relkind != RELKIND_PARTITIONED_INDEX)
+	{
+		ereport(ERROR,
+				(errcode(ERRCODE_WRONG_OBJECT_TYPE),
+				 errmsg("\"%s\" is not an index",
+						RelationGetRelationName(r))));
+	}
+
+	/* Make note that we've accessed a temporary relation */
+	if (RelationUsesLocalBuffers(r))
+		MyXactFlags |= XACT_FLAGS_ACCESSEDTEMPNAMESPACE;
+
+	pgstat_initstats(r);
+
+	return r;
+}
+
 /* ----------------
  *		index_close - close an index relation
  *
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 4ef61b5efd..47e6a54149 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -100,6 +100,12 @@ typedef struct
 	Oid			pendingReindexedIndexes[FLEXIBLE_ARRAY_MEMBER];
 } SerializedReindexState;
 
+typedef struct
+{
+	Oid relid;	/* targetr index oid */
+	bool outdated;	/* depends on at least on deprecated collation? */
+} IndexHasOutdatedColl;
+
 /* non-export function prototypes */
 static bool relationHasPrimaryKey(Relation rel);
 static TupleDesc ConstructTupleDescriptor(Relation heapRelation,
@@ -1351,6 +1357,77 @@ index_check_collation_versions(Oid relid)
 	list_free(context.warned_colls);
 }
 
+/*
+ * Detect if an index depends on at least one outdated collation.
+ * This is a callback for visitDependenciesOf().
+ */
+static bool
+do_check_index_has_outdated_collation(const ObjectAddress *otherObject,
+										const char *version,
+										char **new_version,
+										void *data)
+{
+	IndexHasOutdatedColl *context = data;
+	char *current_version;
+
+	/* We only care about dependencies on collations. */
+	if (otherObject->classId != CollationRelationId)
+		return false;
+
+	/* Fast exit if we already found a outdated collation version. */
+	if (context->outdated)
+		return false;
+
+	/* Ask the provider for the current version.  Give up if unsupported. */
+	current_version = get_collation_version_for_oid(otherObject->objectId,
+													false);
+	if (!current_version)
+		return false;
+
+	if (!version || strcmp(version, current_version) != 0)
+		context->outdated = true;
+
+	return false;
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * collation version.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ */
+bool
+index_has_outdated_collation(Oid indexOid)
+{
+	ObjectAddress object;
+	IndexHasOutdatedColl context;
+
+	object.classId = RelationRelationId;
+	object.objectId = indexOid;
+	object.objectSubId = 0;
+
+	context.relid = indexOid;
+	context.outdated = false;
+
+	visitDependenciesOf(&object, &do_check_index_has_outdated_collation,
+						&context);
+
+	return context.outdated;
+}
+
+/*
+ * Check whether the given index has a dependency with an outdated
+ * refobjversion.
+ * Caller must hold a suitable lock and make sure that the given Oid belongs to
+ * an index.
+ * For now, only dependency on collations are supported.
+ */
+bool
+index_has_outdated_dependency(Oid indexOid)
+{
+	return index_has_outdated_collation(indexOid);
+}
+
 /*
  * Update the version for collations.  A callback for visitDependenciesOf().
  */
@@ -3991,7 +4068,7 @@ reindex_relation(Oid relid, int flags, ReindexParams *params)
 	 * relcache to get this with a sequential scan if ignoring system
 	 * indexes.)
 	 */
-	indexIds = RelationGetIndexList(rel);
+	indexIds = RelationGetIndexListFiltered(rel, params->options);
 
 	if (flags & REINDEX_REL_SUPPRESS_INDEX_USE)
 	{
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8bc652ecd3..90ea95f40d 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2484,6 +2484,7 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 	bool		concurrently = false;
 	bool		verbose = false;
 	char	   *tablespacename = NULL;
+	bool		outdated_filter = false;
 
 	/* Parse option list */
 	foreach(lc, stmt->params)
@@ -2496,6 +2497,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 			concurrently = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "tablespace") == 0)
 			tablespacename = defGetString(opt);
+		else if (strcmp(opt->defname, "outdated") == 0)
+			outdated_filter = defGetBoolean(opt);
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -2510,7 +2513,8 @@ ExecReindex(ParseState *pstate, ReindexStmt *stmt, bool isTopLevel)
 
 	params.options =
 		(verbose ? REINDEXOPT_VERBOSE : 0) |
-		(concurrently ? REINDEXOPT_CONCURRENTLY : 0);
+		(concurrently ? REINDEXOPT_CONCURRENTLY : 0) |
+		(outdated_filter ? REINDEXOPT_OUTDATED : 0);
 
 	/*
 	 * Assign the tablespace OID to move indexes to, with InvalidOid to do
@@ -2605,6 +2609,20 @@ ReindexIndex(RangeVar *indexRelation, ReindexParams *params, bool isTopLevel)
 	persistence = get_rel_persistence(indOid);
 	relkind = get_rel_relkind(indOid);
 
+	/*
+	 * If the index isn't partitioned, we can detect here if it has any oudated
+	 * dependency.
+	 */
+	if (relkind == RELKIND_INDEX &&
+		(params->options & REINDEXOPT_OUTDATED) != 0 &&
+		!index_has_outdated_dependency(indOid))
+	{
+		ereport(NOTICE,
+				(errmsg("index \"%s\" has no outdated dependency",
+						get_rel_name(indOid))));
+		return;
+	}
+
 	if (relkind == RELKIND_PARTITIONED_INDEX)
 		ReindexPartitions(indOid, params, isTopLevel);
 	else if ((params->options & REINDEXOPT_CONCURRENTLY) != 0 &&
@@ -3049,6 +3067,17 @@ ReindexPartitions(Oid relid, ReindexParams *params, bool isTopLevel)
 		Assert(partkind == RELKIND_INDEX ||
 			   partkind == RELKIND_RELATION);
 
+		/* Ignore indexes that don't depend on outdated dependency if needed */
+		if (partkind == RELKIND_INDEX &&
+			(params->options & REINDEXOPT_OUTDATED) != 0 &&
+			!index_has_outdated_dependency(partoid))
+		{
+			ereport(NOTICE,
+					(errmsg("index \"%s\" has no outdated dependency",
+							get_rel_name(partoid))));
+			continue;
+		}
+
 		/* Save partition OID */
 		old_context = MemoryContextSwitchTo(reindex_context);
 		partitions = lappend_oid(partitions, partoid);
@@ -3307,7 +3336,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 									RelationGetRelationName(heapRelation))));
 
 				/* Add all the valid indexes of relation to list */
-				foreach(lc, RelationGetIndexList(heapRelation))
+				foreach(lc, RelationGetIndexListFiltered(heapRelation,
+														 params->options))
 				{
 					Oid			cellOid = lfirst_oid(lc);
 					Relation	indexRelation = index_open(cellOid,
@@ -3359,7 +3389,8 @@ ReindexRelationConcurrently(Oid relationOid, ReindexParams *params)
 
 					MemoryContextSwitchTo(oldcontext);
 
-					foreach(lc2, RelationGetIndexList(toastRelation))
+					foreach(lc2, RelationGetIndexListFiltered(toastRelation,
+															  params->options))
 					{
 						Oid			cellOid = lfirst_oid(lc2);
 						Relation	indexRelation = index_open(cellOid,
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 652be0b96d..a67cfa867c 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -674,7 +674,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	NULLS_P NUMERIC
 
 	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
-	ORDER ORDINALITY OTHERS OUT_P OUTER_P
+	ORDER ORDINALITY OTHERS OUT_P OUTDATED OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
 	PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
@@ -15430,6 +15430,7 @@ unreserved_keyword:
 			| OPTIONS
 			| ORDINALITY
 			| OTHERS
+			| OUTDATED
 			| OVER
 			| OVERRIDING
 			| OWNED
@@ -16002,6 +16003,7 @@ bare_label_keyword:
 			| ORDINALITY
 			| OTHERS
 			| OUT_P
+			| OUTDATED
 			| OUTER_P
 			| OVERLAY
 			| OVERRIDING
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 7ef510cd01..94fc7b1c9e 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4620,6 +4620,53 @@ RelationGetIndexList(Relation relation)
 	return result;
 }
 
+/*
+ * RelationGetIndexListFiltered -- get a filtered list of indexes on this
+ * relation.
+ *
+ * Calls RelationGetIndexList and only keep indexes that have an outdated
+ * dependency.  For now, only collation version dependency is supported.
+ */
+List *
+RelationGetIndexListFiltered(Relation relation, bits32 options)
+{
+	List	   *result,
+			   *full_list;
+	ListCell   *lc;
+
+	full_list = RelationGetIndexList(relation);
+
+	/* Fast exit if no filtering was asked, or if the list if empty. */
+	if (((options & REINDEXOPT_OUTDATED) == 0) || full_list == NIL)
+		return full_list;
+
+	result = NIL;
+	foreach(lc, full_list)
+	{
+		Oid		indexOid = lfirst_oid(lc);
+
+		Assert(get_rel_relkind(indexOid) == RELKIND_INDEX ||
+			   get_rel_relkind(indexOid) == RELKIND_PARTITIONED_INDEX);
+
+		/*
+		 * Check for any oudated dependency.
+		 */
+		if (index_has_outdated_dependency(indexOid))
+		{
+			result = lappend_oid(result, indexOid);
+			continue;
+		}
+
+		/* Didn't find any outdated dependency, index will be ignored. */
+		if (((options & REINDEXOPT_OUTDATED) != 0))
+			ereport(NOTICE,
+					(errmsg("index \"%s\" has no outdated dependency",
+							get_rel_name(indexOid))));
+	}
+
+	return result;
+}
+
 /*
  * RelationGetStatExtList
  *		get a list of OIDs of statistics objects on this relation
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ecdb8d752b..0843f36580 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3674,7 +3674,7 @@ psql_completion(const char *text, int start, int end)
 		 * one word, so the above test is correct.
 		 */
 		if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
-			COMPLETE_WITH("CONCURRENTLY", "TABLESPACE", "VERBOSE");
+			COMPLETE_WITH("CONCURRENTLY", "OUTDATED'", "TABLESPACE", "VERBOSE");
 		else if (TailMatches("TABLESPACE"))
 			COMPLETE_WITH_QUERY(Query_for_list_of_tablespaces);
 	}
diff --git a/src/include/access/genam.h b/src/include/access/genam.h
index 4515401869..fab5dbc101 100644
--- a/src/include/access/genam.h
+++ b/src/include/access/genam.h
@@ -138,6 +138,7 @@ typedef struct IndexOrderByDistance
 #define IndexScanIsValid(scan) PointerIsValid(scan)
 
 extern Relation index_open(Oid relationId, LOCKMODE lockmode);
+extern Relation try_index_open(Oid relationId, LOCKMODE lockmode);
 extern void index_close(Relation relation, LOCKMODE lockmode);
 
 extern bool index_insert(Relation indexRelation,
diff --git a/src/include/catalog/index.h b/src/include/catalog/index.h
index e22d506436..298c0c633c 100644
--- a/src/include/catalog/index.h
+++ b/src/include/catalog/index.h
@@ -42,6 +42,7 @@ typedef struct ReindexParams
 #define REINDEXOPT_REPORT_PROGRESS 0x02 /* report pgstat progress */
 #define REINDEXOPT_MISSING_OK 	0x04	/* skip missing relations */
 #define REINDEXOPT_CONCURRENTLY	0x08	/* concurrent mode */
+#define REINDEXOPT_OUTDATED		0x10/* outdated collation only */
 
 /* state info for validate_index bulkdelete callback */
 typedef struct ValidateIndexState
@@ -137,6 +138,8 @@ extern void FormIndexDatum(IndexInfo *indexInfo,
 						   bool *isnull);
 
 extern void index_check_collation_versions(Oid relid);
+extern bool index_has_outdated_collation(Oid indexOid);
+extern bool index_has_outdated_dependency(Oid indexOid);
 extern void index_update_collation_versions(Oid relid, Oid coll);
 
 extern void index_build(Relation heapRelation,
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 28083aaac9..e6c725d9a6 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -295,6 +295,7 @@ PG_KEYWORD("order", ORDER, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("ordinality", ORDINALITY, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("others", OTHERS, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("out", OUT_P, COL_NAME_KEYWORD, BARE_LABEL)
+PG_KEYWORD("outdated", OUTDATED, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL)
 PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD, AS_LABEL)
diff --git a/src/include/utils/relcache.h b/src/include/utils/relcache.h
index 2fcdf79323..a7a2272abd 100644
--- a/src/include/utils/relcache.h
+++ b/src/include/utils/relcache.h
@@ -45,6 +45,7 @@ extern void RelationClose(Relation relation);
  */
 extern List *RelationGetFKeyList(Relation relation);
 extern List *RelationGetIndexList(Relation relation);
+extern List *RelationGetIndexListFiltered(Relation relation, bits32 options);
 extern List *RelationGetStatExtList(Relation relation);
 extern Oid	RelationGetPrimaryKeyIndex(Relation relation);
 extern Oid	RelationGetReplicaIndex(Relation relation);
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out
index de70cb1212..001f091fb3 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -2094,9 +2094,11 @@ UPDATE pg_depend SET refobjversion = 'not a version'
 WHERE refclassid = 'pg_collation'::regclass
 AND objid::regclass::text LIKE 'icuidx%'
 AND refobjversion IS NOT NULL;
-REINDEX TABLE collate_test;
-REINDEX TABLE collate_part_0;
+SET client_min_messages to WARNING; -- pg_toast index names aren't stable
+REINDEX (OUTDATED) TABLE collate_test;
+REINDEX (OUTDATED) TABLE collate_part_0;
 REINDEX TABLE collate_part_1;
+RESET client_min_messages;
 SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
  objid 
 -------
@@ -2107,9 +2109,11 @@ UPDATE pg_depend SET refobjversion = 'not a version'
 WHERE refclassid = 'pg_collation'::regclass
 AND objid::regclass::text LIKE 'icuidx%'
 AND refobjversion IS NOT NULL;
-REINDEX TABLE CONCURRENTLY collate_test;
+SET client_min_messages to WARNING; -- pg_toast index names aren't stable
+REINDEX (OUTDATED) TABLE CONCURRENTLY collate_test;
 REINDEX TABLE CONCURRENTLY collate_part_0;
-REINDEX INDEX CONCURRENTLY icuidx17_part;
+REINDEX (OUTDATED) INDEX CONCURRENTLY icuidx17_part;
+RESET client_min_messages;
 SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
  objid 
 -------
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 830fdddf24..959fad6b71 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2018,6 +2018,33 @@ INFO:  index "reindex_verbose_pkey" was reindexed
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 --
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key) PARTITION BY LIST (id);
+CREATE TABLE reindex_coll_1 PARTITION OF reindex_coll FOR VALUES IN (1);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (VERBOSE, OUTDATED) TABLE reindex_coll;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+REINDEX (VERBOSE, OUTDATED) INDEX reindex_coll_pkey;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+REINDEX (VERBOSE, OUTDATED) TABLE reindex_coll_1;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+NOTICE:  table "reindex_coll_1" has no indexes to reindex
+REINDEX (VERBOSE, OUTDATED) INDEX reindex_coll_1_pkey;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) TABLE reindex_coll;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) INDEX reindex_coll_pkey;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) TABLE reindex_coll_1;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+NOTICE:  table "reindex_coll_1" has no indexes that can be reindexed concurrently
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) INDEX reindex_coll_1_pkey;
+NOTICE:  index "reindex_coll_1_pkey" has no outdated dependency
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+--
 -- REINDEX CONCURRENTLY
 --
 CREATE TABLE concur_reindex_tab (c1 int);
diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql
index dd5d208854..a6504512f7 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -836,9 +836,11 @@ WHERE refclassid = 'pg_collation'::regclass
 AND objid::regclass::text LIKE 'icuidx%'
 AND refobjversion IS NOT NULL;
 
-REINDEX TABLE collate_test;
-REINDEX TABLE collate_part_0;
+SET client_min_messages to WARNING; -- pg_toast index names aren't stable
+REINDEX (OUTDATED) TABLE collate_test;
+REINDEX (OUTDATED) TABLE collate_part_0;
 REINDEX TABLE collate_part_1;
+RESET client_min_messages;
 
 SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
 
@@ -847,9 +849,11 @@ UPDATE pg_depend SET refobjversion = 'not a version'
 WHERE refclassid = 'pg_collation'::regclass
 AND objid::regclass::text LIKE 'icuidx%'
 AND refobjversion IS NOT NULL;
-REINDEX TABLE CONCURRENTLY collate_test;
+SET client_min_messages to WARNING; -- pg_toast index names aren't stable
+REINDEX (OUTDATED) TABLE CONCURRENTLY collate_test;
 REINDEX TABLE CONCURRENTLY collate_part_0;
-REINDEX INDEX CONCURRENTLY icuidx17_part;
+REINDEX (OUTDATED) INDEX CONCURRENTLY icuidx17_part;
+RESET client_min_messages;
 
 SELECT objid::regclass FROM pg_depend WHERE refobjversion = 'not a version';
 
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8bc76f7c6f..4da6e2b5e8 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -790,6 +790,24 @@ REINDEX (VERBOSE) TABLE reindex_verbose;
 \set VERBOSITY default
 DROP TABLE reindex_verbose;
 
+--
+-- REINDEX (OUTDATED)
+--
+CREATE TABLE reindex_coll(id integer primary key) PARTITION BY LIST (id);
+CREATE TABLE reindex_coll_1 PARTITION OF reindex_coll FOR VALUES IN (1);
+\set VERBOSITY terse \\ -- suppress machine-dependent details
+-- no suitable index should be found
+REINDEX (VERBOSE, OUTDATED) TABLE reindex_coll;
+REINDEX (VERBOSE, OUTDATED) INDEX reindex_coll_pkey;
+REINDEX (VERBOSE, OUTDATED) TABLE reindex_coll_1;
+REINDEX (VERBOSE, OUTDATED) INDEX reindex_coll_1_pkey;
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) TABLE reindex_coll;
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) INDEX reindex_coll_pkey;
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) TABLE reindex_coll_1;
+REINDEX (VERBOSE, OUTDATED, CONCURRENTLY) INDEX reindex_coll_1_pkey;
+\set VERBOSITY default
+DROP TABLE reindex_coll;
+
 --
 -- REINDEX CONCURRENTLY
 --
-- 
2.30.1

From 28c12ae6f353abec9e4bcb141d287ab981298353 Mon Sep 17 00:00:00 2001
From: Julien Rouhaud <julien.rouhaud@free.fr>
Date: Thu, 25 Feb 2021 01:33:58 +0800
Subject: [PATCH v7 2/2] Add a --outdated option to reindexdb

This uses the new OUTDATED option for REINDEX.  If user asks for multiple job,
the list of tables to process will be sorted by the total size of underlying
indexes that have outdated dependency using a new
pg_index_has_outdated_dependency(regclass) SQL function.

Catversion (should be) bumped.

Author: Julien Rouhaud <rjuju123@gmail.com>
Reviewed-by: Michael Paquier, Mark Dilger
Discussion: https://postgr.es/m/20201203093143.GA64934%40nol
---
 doc/src/sgml/func.sgml             |  27 ++++--
 src/backend/catalog/index.c        |  22 +++++
 src/bin/scripts/reindexdb.c        | 143 ++++++++++++++++++++++++-----
 src/bin/scripts/t/090_reindexdb.pl |  34 ++++++-
 src/include/catalog/pg_proc.dat    |   4 +
 5 files changed, 198 insertions(+), 32 deletions(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 9492a3c6b9..0eda6678ac 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -26448,12 +26448,13 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
 
    <para>
     <xref linkend="functions-admin-index-table"/> shows the functions
-    available for index maintenance tasks.  (Note that these maintenance
-    tasks are normally done automatically by autovacuum; use of these
-    functions is only required in special cases.)
-    These functions cannot be executed during recovery.
-    Use of these functions is restricted to superusers and the owner
-    of the given index.
+    available for index maintenance tasks.  (Note that the maintenance
+    tasks performing actions on indexes are normally done automatically by
+    autovacuum; use of these functions is only required in special cases.)
+    The functions performing actions on indexes cannot be executed during
+    recovery.
+    Use of the functions performing actions on indexes is restricted to
+    superusers and the owner of the given index.
    </para>
 
    <table id="functions-admin-index-table">
@@ -26538,6 +26539,20 @@ SELECT pg_size_pretty(sum(pg_relation_size(relid))) AS total_size
         option.
        </para></entry>
       </row>
+
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_index_has_outdated_dependency</primary>
+        </indexterm>
+        <function>pg_index_has_outdated_dependency</function> ( <parameter>index</parameter> <type>regclass</type> )
+        <returnvalue>boolean</returnvalue>
+       </para>
+       <para>
+        Check if the specified index has any outdated dependency.  For now only
+        dependency on collations are supported.
+       </para></entry>
+      </row>
      </tbody>
     </tgroup>
    </table>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 47e6a54149..6848e61df3 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -1390,6 +1390,28 @@ do_check_index_has_outdated_collation(const ObjectAddress *otherObject,
 	return false;
 }
 
+/*
+ * SQL wrapper around index_has_outdated_dependency.
+ */
+Datum
+pg_index_has_outdated_dependency(PG_FUNCTION_ARGS)
+{
+	Oid			indexOid = PG_GETARG_OID(0);
+	Relation	rel;
+	bool		res;
+
+	rel = try_index_open(indexOid, AccessShareLock);
+
+	if (rel == NULL)
+		PG_RETURN_NULL();
+
+	res = index_has_outdated_dependency(indexOid);
+
+	relation_close(rel, AccessShareLock);
+
+	PG_RETURN_BOOL(res);
+}
+
 /*
  * Check whether the given index has a dependency with an outdated
  * collation version.
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index fc0681538a..d252b1454f 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -35,20 +35,23 @@ typedef enum ReindexType
 static SimpleStringList *get_parallel_object_list(PGconn *conn,
 												  ReindexType type,
 												  SimpleStringList *user_list,
+												  bool outdated,
 												  bool echo);
 static void reindex_one_database(ConnParams *cparams, ReindexType type,
 								 SimpleStringList *user_list,
 								 const char *progname,
 								 bool echo, bool verbose, bool concurrently,
-								 int concurrentCons, const char *tablespace);
+								 int concurrentCons, const char *tablespace,
+								 bool outdated);
 static void reindex_all_databases(ConnParams *cparams,
 								  const char *progname, bool echo,
 								  bool quiet, bool verbose, bool concurrently,
-								  int concurrentCons, const char *tablespace);
+								  int concurrentCons, const char *tablespace,
+								  bool outdated);
 static void run_reindex_command(PGconn *conn, ReindexType type,
 								const char *name, bool echo, bool verbose,
 								bool concurrently, bool async,
-								const char *tablespace);
+								const char *tablespace, bool outdated);
 
 static void help(const char *progname);
 
@@ -74,6 +77,7 @@ main(int argc, char *argv[])
 		{"concurrently", no_argument, NULL, 1},
 		{"maintenance-db", required_argument, NULL, 2},
 		{"tablespace", required_argument, NULL, 3},
+		{"outdated", no_argument, NULL, 4},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -95,6 +99,7 @@ main(int argc, char *argv[])
 	bool		quiet = false;
 	bool		verbose = false;
 	bool		concurrently = false;
+	bool		outdated = false;
 	SimpleStringList indexes = {NULL, NULL};
 	SimpleStringList tables = {NULL, NULL};
 	SimpleStringList schemas = {NULL, NULL};
@@ -170,6 +175,9 @@ main(int argc, char *argv[])
 			case 3:
 				tablespace = pg_strdup(optarg);
 				break;
+			case 4:
+				outdated = true;
+				break;
 			default:
 				fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
 				exit(1);
@@ -234,7 +242,8 @@ main(int argc, char *argv[])
 		cparams.dbname = maintenance_db;
 
 		reindex_all_databases(&cparams, progname, echo, quiet, verbose,
-							  concurrently, concurrentCons, tablespace);
+							  concurrently, concurrentCons, tablespace,
+							  outdated);
 	}
 	else if (syscatalog)
 	{
@@ -253,12 +262,17 @@ main(int argc, char *argv[])
 			pg_log_error("cannot reindex specific index(es) and system catalogs at the same time");
 			exit(1);
 		}
-
 		if (concurrentCons > 1)
 		{
 			pg_log_error("cannot use multiple jobs to reindex system catalogs");
 			exit(1);
 		}
+		if (outdated)
+		{
+			pg_log_error("cannot filter indexes having outdated dependencies "
+						 "and reindex system catalogs at the same time");
+			exit(1);
+		}
 
 		if (dbname == NULL)
 		{
@@ -274,7 +288,7 @@ main(int argc, char *argv[])
 
 		reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
 							 progname, echo, verbose,
-							 concurrently, 1, tablespace);
+							 concurrently, 1, tablespace, outdated);
 	}
 	else
 	{
@@ -304,17 +318,20 @@ main(int argc, char *argv[])
 		if (schemas.head != NULL)
 			reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		if (indexes.head != NULL)
 			reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
 								 progname, echo, verbose,
-								 concurrently, 1, tablespace);
+								 concurrently, 1, tablespace,
+								 outdated);
 
 		if (tables.head != NULL)
 			reindex_one_database(&cparams, REINDEX_TABLE, &tables,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 
 		/*
 		 * reindex database only if neither index nor table nor schema is
@@ -323,7 +340,8 @@ main(int argc, char *argv[])
 		if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
 			reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
 								 progname, echo, verbose,
-								 concurrently, concurrentCons, tablespace);
+								 concurrently, concurrentCons, tablespace,
+								 outdated);
 	}
 
 	exit(0);
@@ -334,7 +352,7 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 					 SimpleStringList *user_list,
 					 const char *progname, bool echo,
 					 bool verbose, bool concurrently, int concurrentCons,
-					 const char *tablespace)
+					 const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	SimpleStringListCell *cell;
@@ -363,6 +381,14 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 		exit(1);
 	}
 
+	if (outdated && PQserverVersion(conn) < 140000)
+	{
+		PQfinish(conn);
+		pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
+					 "outdated", "14");
+		exit(1);
+	}
+
 	if (!parallel)
 	{
 		switch (process_type)
@@ -399,14 +425,24 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 				 */
 				if (concurrently)
 					pg_log_warning("cannot reindex system catalogs concurrently, skipping all");
+				else if (outdated)
+				{
+					/*
+					 * The only supported kind of object that can be outdated
+					 * is collation.  No system catalog has any index that can
+					 * depend on an outdated collation, so skip system
+					 * catalogs.
+					 */
+				}
 				else
 					run_reindex_command(conn, REINDEX_SYSTEM, PQdb(conn), echo,
 										verbose, concurrently, false,
-										tablespace);
+										tablespace, outdated);
 
 				/* Build a list of relations from the database */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -419,7 +455,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 
 				/* Build a list of relations from all the schemas */
 				process_list = get_parallel_object_list(conn, process_type,
-														user_list, echo);
+														user_list, outdated,
+														echo);
 				process_type = REINDEX_TABLE;
 
 				/* Bail out if nothing to process */
@@ -485,7 +522,8 @@ reindex_one_database(ConnParams *cparams, ReindexType type,
 
 		ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
 		run_reindex_command(free_slot->connection, process_type, objname,
-							echo, verbose, concurrently, true, tablespace);
+							echo, verbose, concurrently, true, tablespace,
+							outdated);
 
 		cell = cell->next;
 	} while (cell != NULL);
@@ -510,7 +548,7 @@ finish:
 static void
 run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 					bool echo, bool verbose, bool concurrently, bool async,
-					const char *tablespace)
+					const char *tablespace, bool outdated)
 {
 	const char *paren = "(";
 	const char *comma = ", ";
@@ -537,6 +575,12 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
 		sep = comma;
 	}
 
+	if (outdated)
+	{
+		appendPQExpBuffer(&sql, "%sOUTDATED", sep);
+		sep = comma;
+	}
+
 	if (sep != paren)
 		appendPQExpBufferStr(&sql, ") ");
 
@@ -642,7 +686,7 @@ run_reindex_command(PGconn *conn, ReindexType type, const char *name,
  */
 static SimpleStringList *
 get_parallel_object_list(PGconn *conn, ReindexType type,
-						 SimpleStringList *user_list, bool echo)
+						 SimpleStringList *user_list, bool outdated, bool echo)
 {
 	PQExpBufferData catalog_query;
 	PQExpBufferData buf;
@@ -661,16 +705,41 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 	{
 		case REINDEX_DATABASE:
 			Assert(user_list == NULL);
+
 			appendPQExpBufferStr(&catalog_query,
 								 "SELECT c.relname, ns.nspname\n"
 								 " FROM pg_catalog.pg_class c\n"
 								 " JOIN pg_catalog.pg_namespace ns"
-								 " ON c.relnamespace = ns.oid\n"
+								 " ON c.relnamespace = ns.oid\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " JOIN pg_catalog.pg_index i"
+									 " ON c.oid = i.indrelid\n"
+									 " JOIN pg_catalog.pg_class ci"
+									 " ON i.indexrelid = ci.oid\n");
+			}
+
+			appendPQExpBufferStr(&catalog_query,
 								 " WHERE ns.nspname != 'pg_catalog'\n"
 								 "   AND c.relkind IN ("
 								 CppAsString2(RELKIND_RELATION) ", "
-								 CppAsString2(RELKIND_MATVIEW) ")\n"
-								 " ORDER BY c.relpages DESC;");
+								 CppAsString2(RELKIND_MATVIEW) ")\n");
+
+			if (outdated)
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " GROUP BY c.relname, ns.nspname\n"
+									 " ORDER BY pg_catalog.sum(ci.relpages)"
+									 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+			}
+			else
+			{
+				appendPQExpBufferStr(&catalog_query,
+									 " ORDER BY c.relpages DESC;");
+			}
+
 			break;
 
 		case REINDEX_SCHEMA:
@@ -688,7 +757,18 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 									 "SELECT c.relname, ns.nspname\n"
 									 " FROM pg_catalog.pg_class c\n"
 									 " JOIN pg_catalog.pg_namespace ns"
-									 " ON c.relnamespace = ns.oid\n"
+									 " ON c.relnamespace = ns.oid\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " JOIN pg_catalog.pg_index i"
+										 " ON c.oid = i.indrelid\n"
+										 " JOIN pg_catalog.pg_class ci"
+										 " ON i.indexrelid = ci.oid\n");
+				}
+
+				appendPQExpBufferStr(&catalog_query,
 									 " WHERE c.relkind IN ("
 									 CppAsString2(RELKIND_RELATION) ", "
 									 CppAsString2(RELKIND_MATVIEW) ")\n"
@@ -706,8 +786,20 @@ get_parallel_object_list(PGconn *conn, ReindexType type,
 					appendStringLiteralConn(&catalog_query, nspname, conn);
 				}
 
-				appendPQExpBufferStr(&catalog_query, ")\n"
-									 " ORDER BY c.relpages DESC;");
+				appendPQExpBufferStr(&catalog_query, ")\n");
+
+				if (outdated)
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " GROUP BY c.relname, ns.nspname\n"
+										 " ORDER BY pg_catalog.sum(ci.relpages)"
+										 " FILTER (WHERE pg_catalog.pg_index_has_outdated_dependency(ci.oid)) DESC;");
+				}
+				else
+				{
+					appendPQExpBufferStr(&catalog_query,
+										 " ORDER BY c.relpages DESC;");
+				}
 			}
 			break;
 
@@ -755,7 +847,7 @@ static void
 reindex_all_databases(ConnParams *cparams,
 					  const char *progname, bool echo, bool quiet, bool verbose,
 					  bool concurrently, int concurrentCons,
-					  const char *tablespace)
+					  const char *tablespace, bool outdated)
 {
 	PGconn	   *conn;
 	PGresult   *result;
@@ -779,7 +871,7 @@ reindex_all_databases(ConnParams *cparams,
 
 		reindex_one_database(cparams, REINDEX_DATABASE, NULL,
 							 progname, echo, verbose, concurrently,
-							 concurrentCons, tablespace);
+							 concurrentCons, tablespace, outdated);
 	}
 
 	PQclear(result);
@@ -798,6 +890,7 @@ help(const char *progname)
 	printf(_("  -e, --echo                   show the commands being sent to the server\n"));
 	printf(_("  -i, --index=INDEX            recreate specific index(es) only\n"));
 	printf(_("  -j, --jobs=NUM               use this many concurrent connections to reindex\n"));
+	printf(_("      --outdated               only process indexes having outdated dependencies\n"));
 	printf(_("  -q, --quiet                  don't write any messages\n"));
 	printf(_("  -s, --system                 reindex system catalogs\n"));
 	printf(_("  -S, --schema=SCHEMA          reindex specific schema(s) only\n"));
diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_reindexdb.pl
index 159b637230..91fd602041 100644
--- a/src/bin/scripts/t/090_reindexdb.pl
+++ b/src/bin/scripts/t/090_reindexdb.pl
@@ -3,7 +3,7 @@ use warnings;
 
 use PostgresNode;
 use TestLib;
-use Test::More tests => 58;
+use Test::More tests => 70;
 
 program_help_ok('reindexdb');
 program_version_ok('reindexdb');
@@ -174,6 +174,9 @@ $node->command_fails(
 $node->command_fails(
 	[ 'reindexdb', '-j', '2', '-i', 'i1', 'postgres' ],
 	'parallel reindexdb cannot process indexes');
+$node->command_fails(
+	[ 'reindexdb', '-s', '--outdated' ],
+	'cannot reindex system catalog and filter indexes having outdated dependencies');
 $node->issues_sql_like(
 	[ 'reindexdb', '-j', '2', 'postgres' ],
 	qr/statement:\ REINDEX SYSTEM postgres;
@@ -196,3 +199,32 @@ $node->command_checks_all(
 		qr/^reindexdb: warning: cannot reindex system catalogs concurrently, skipping all/s
 	],
 	'parallel reindexdb for system with --concurrently skips catalogs');
+
+# Temporarily downgrade client-min-message to get the no-op report
+$ENV{PGOPTIONS} = '--client-min-messages=NOTICE';
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-t', 's1.t1', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'verbose reindexdb for outdated dependencies on a specific table reports no-op tables');
+
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/index "t2_id_idx" has no outdated dependency/],
+	'verbose reindexdb for outdated dependencies database wide reports all ignored indexes');
+$node->command_checks_all(
+	[ 'reindexdb',  '--outdated', '-v', '-j', '2', '-d', 'postgres' ],
+	0,
+	[qr/^$/],
+	[qr/table "t1" has no indexes to reindex/],
+	'parallel verbose reindexdb for outdated dependencies database wide reports no-op tables');
+
+# Switch back to WARNING client-min-message
+$ENV{PGOPTIONS} = '--client-min-messages=WARNING';
+$node->issues_sql_like(
+	[ 'reindexdb', '--outdated', '-t', 's1.t1', 'postgres' ],
+	qr/.*statement: REINDEX \(OUTDATED\) TABLE s1\.t1;/,
+	'reindexdb for outdated dependencies specify the OUTDATED keyword');
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 93393fcfd4..911c12ee2c 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -949,6 +949,10 @@
   proname => 'pg_indexam_has_property', provolatile => 's',
   prorettype => 'bool', proargtypes => 'oid text',
   prosrc => 'pg_indexam_has_property' },
+{ oid => '8102', descr => 'test property of an index',
+  proname => 'pg_index_has_outdated_dependency', provolatile => 's',
+  prorettype => 'bool', proargtypes => 'regclass',
+  prosrc => 'pg_index_has_outdated_dependency' },
 { oid => '637', descr => 'test property of an index',
   proname => 'pg_index_has_property', provolatile => 's', prorettype => 'bool',
   proargtypes => 'regclass text', prosrc => 'pg_index_has_property' },
-- 
2.30.1