reindex_not_require_database_name.v2.patch

REINDEX DATABASE omitting dbname
(also works for REINDEX SYSTEM)

Currently, REINDEX DATABASE is not tested because reindexing the catalog can cause deadlocks.

So we want a test for REINDEX DATABASE *and* a user command that doesn't cause deadlocks
on user systems, as well as one that is easy to setup maintenance commands for.

Additional syntax:
REINDEX SYSTEM - reindexes all catalog tables (only)
REINDEX DATABASE - reindexes all user tables (only) - exactly opposite set from REINDEX SYSTEM

Test for REINDEX DATABASE added. Docs updated.


diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index e6b25ee670..5d38ca786e 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
  <refsynopsisdiv>
 <synopsis>
 REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] <replaceable class="parameter">name</replaceable>
+REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { DATABASE | SYSTEM } [ CONCURRENTLY ] [ <replaceable class="parameter">name</replaceable> ]
 
 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
 
@@ -127,7 +128,8 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
     <listitem>
      <para>
       Recreate all indexes within the current database.
-      Indexes on shared system catalogs are also processed.
+      Indexes on shared system catalogs are also processed, unless the
+      database name is omitted, in which case system catalog indexes are skipped.
       This form of <command>REINDEX</command> cannot be executed inside a
       transaction block.
      </para>
@@ -155,7 +157,7 @@ REINDEX [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] { IN
       reindexed.  Index and table names can be schema-qualified.
       Presently, <command>REINDEX DATABASE</command> and <command>REINDEX SYSTEM</command>
       can only reindex the current database, so their parameter must match
-      the current database's name.
+      the current database's name, though supplying the name is optional.
      </para>
     </listitem>
    </varlistentry>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index cd30f15eba..d75abb4e13 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -2759,10 +2759,10 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	bool		concurrent_warning = false;
 	bool		tablespace_warning = false;
 
-	AssertArg(objectName);
 	Assert(objectKind == REINDEX_OBJECT_SCHEMA ||
 		   objectKind == REINDEX_OBJECT_SYSTEM ||
 		   objectKind == REINDEX_OBJECT_DATABASE);
+	AssertArg(objectName || objectKind != REINDEX_OBJECT_SCHEMA);
 
 	if (objectKind == REINDEX_OBJECT_SYSTEM &&
 		(params->options & REINDEXOPT_CONCURRENTLY) != 0)
@@ -2788,13 +2788,13 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 	{
 		objectOid = MyDatabaseId;
 
-		if (strcmp(objectName, get_database_name(objectOid)) != 0)
+		if (objectName && strcmp(objectName, get_database_name(objectOid)) != 0)
 			ereport(ERROR,
 					(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
 					 errmsg("can only reindex the currently open database")));
 		if (!pg_database_ownercheck(objectOid, GetUserId()))
 			aclcheck_error(ACLCHECK_NOT_OWNER, OBJECT_DATABASE,
-						   objectName);
+						   get_database_name(objectOid));
 	}
 
 	/*
@@ -2857,6 +2857,12 @@ ReindexMultipleTables(const char *objectName, ReindexObjectType objectKind,
 			!IsSystemClass(relid, classtuple))
 			continue;
 
+		/* Unqualified REINDEX DATABASE will skip catalog tables */
+		if (objectKind == REINDEX_OBJECT_DATABASE &&
+			objectName == NULL &&
+			IsSystemClass(relid, classtuple))
+			continue;
+
 		/*
 		 * The table can be reindexed if the user is superuser, the table
 		 * owner, or the database/schema owner (but in the latter case, only
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a03b33b53b..7ddd39ed72 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -547,7 +547,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 %type <defelt>	generic_option_elem alter_generic_option_elem
 %type <list>	generic_option_list alter_generic_option_list
 
-%type <ival>	reindex_target_type reindex_target_multitable
+%type <ival>	reindex_target_type reindex_target_multitable reindex_no_name
 
 %type <node>	copy_generic_opt_arg copy_generic_opt_arg_list_item
 %type <defelt>	copy_generic_opt_elem
@@ -8512,6 +8512,24 @@ ReindexStmt:
 								makeDefElem("concurrently", NULL, @3));
 					$$ = (Node *)n;
 				}
+			| REINDEX reindex_no_name
+				{
+					ReindexStmt *n = makeNode(ReindexStmt);
+					n->kind = $2;
+					n->name = NULL;
+					n->relation = NULL;
+					n->params = NIL;
+					$$ = (Node *)n;
+				}
+			| REINDEX '(' utility_option_list ')' reindex_no_name
+				{
+					ReindexStmt *n = makeNode(ReindexStmt);
+					n->kind = $5;
+					n->name = NULL;
+					n->relation = NULL;
+					n->params = $3;
+					$$ = (Node *)n;
+				}
 			| REINDEX '(' utility_option_list ')' reindex_target_type opt_concurrently qualified_name
 				{
 					ReindexStmt *n = makeNode(ReindexStmt);
@@ -8546,6 +8564,10 @@ reindex_target_multitable:
 			| SYSTEM_P				{ $$ = REINDEX_OBJECT_SYSTEM; }
 			| DATABASE				{ $$ = REINDEX_OBJECT_DATABASE; }
 		;
+reindex_no_name:
+			SYSTEM_P				{ $$ = REINDEX_OBJECT_SYSTEM; }
+			| DATABASE				{ $$ = REINDEX_OBJECT_DATABASE; }
+		;
 
 /*****************************************************************************
  *
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index d55aec3a1d..1f4e15557b 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -2808,6 +2808,8 @@ ERROR:  REINDEX SCHEMA cannot run inside a transaction block
 END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
+-- unqualified reindex database
+REINDEX DATABASE;
 -- Failure for unauthorized user
 CREATE ROLE regress_reindexuser NOLOGIN;
 SET SESSION ROLE regress_reindexuser;
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d8fded3d93..74c0d0f1c9 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -1235,6 +1235,9 @@ END;
 -- concurrently
 REINDEX SCHEMA CONCURRENTLY schema_to_reindex;
 
+-- unqualified reindex database
+REINDEX DATABASE;
+
 -- Failure for unauthorized user
 CREATE ROLE regress_reindexuser NOLOGIN;
 SET SESSION ROLE regress_reindexuser;