Vik Fearing wrote:
> Here are two patches for this.
>
> The first one, reindex_user_tables.v1.patch, implements the variant that
> only hits user tables, as suggested by you.
>
> The second one, reindex_no_dbname.v1.patch, allows the three
> database-wide variants to omit the database name (voted for by Daniel
> Migowski, Bruce, and myself; voted against by you). This patch is to be
> applied on top of the first one.
Not a fan. Here's a revised version that provides REINDEX USER TABLES,
which can only be used without a database name; other modes are not
affected i.e. they continue to require a database name. I also renamed
your proposed reindexdb's --usertables to --user-tables.
Oh, I just noticed that if you say reindexdb --all --user-tables, the
latter is not honored. Must fix before commit.
Makes sense?
Note: I don't like the reindexdb UI; if you just run "reindexdb -d
foobar" it will reindex everything, including system catalogs. I think
USER TABLES should be the default operation mode for reindex. If you
want plain old "REINDEX DATABASE foobar" which also hits the catalogs,
you should request that separately (how?). This patch doesn't change
this.
Also note: if you say "user tables", information_schema is reindexed too,
which kinda sucks.
Further note: this command is probably pointless in the majority of
cases. Somebody should spend some serious time with REINDEX
CONCURRENTLY ..
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index cabae19..d05e1ac 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">name</replaceable> [ FORCE ]
+REINDEX USER TABLES
</synopsis>
</refsynopsisdiv>
@@ -126,14 +127,26 @@ REINDEX { INDEX | TABLE | DATABASE | SYSTEM } <replaceable class="PARAMETER">nam
</varlistentry>
<varlistentry>
+ <term><literal>USER TABLES</literal></term>
+ <listitem>
+ <para>
+ Recreate all indexes on user tables within the current database.
+ Indexes on system catalogs are not processed.
+ This form of <command>REINDEX</command> cannot be executed inside a
+ transaction block.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="PARAMETER">name</replaceable></term>
<listitem>
<para>
The name of the specific index, table, or database to be
reindexed. Index and table names can be schema-qualified.
- Presently, <command>REINDEX DATABASE</> and <command>REINDEX SYSTEM</>
- can only reindex the current database, so their parameter must match
- the current database's name.
+ Presently, <command>REINDEX DATABASE</>, <command>REINDEX SYSTEM</>,
+ and <command>REINDEX USER TABLES</> can only reindex the current
+ database, so their parameter must match the current database's name.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/reindexdb.sgml b/doc/src/sgml/ref/reindexdb.sgml
index 486f5c9..f69d84b 100644
--- a/doc/src/sgml/ref/reindexdb.sgml
+++ b/doc/src/sgml/ref/reindexdb.sgml
@@ -65,6 +65,15 @@ PostgreSQL documentation
</group>
<arg choice="opt"><replaceable>dbname</replaceable></arg>
</cmdsynopsis>
+
+ <cmdsynopsis>
+ <command>reindexdb</command>
+ <arg rep="repeat"><replaceable>connection-option</replaceable></arg>
+ <group choice="plain">
+ <arg choice="plain"><option>--user-tables</option></arg>
+ <arg choice="plain"><option>-u</option></arg>
+ </group>
+ </cmdsynopsis>
</refsynopsisdiv>
@@ -173,6 +182,16 @@ PostgreSQL documentation
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><option>-u</></term>
+ <term><option>--user-tables</></term>
+ <listitem>
+ <para>
+ Reindex database's user tables.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><option>-V</></term>
<term><option>--version</></term>
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index fdfa6ca..23e13f0 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1772,6 +1772,9 @@ ReindexTable(RangeVar *relation)
* To reduce the probability of deadlocks, each table is reindexed in a
* separate transaction, so we can release the lock on it right away.
* That means this must not be called within a user transaction block!
+ *
+ * databaseName can be NULL when do_user is set and do_system isn't; this
+ * is the REINDEX USER TABLES case.
*/
Oid
ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
@@ -1784,9 +1787,10 @@ ReindexDatabase(const char *databaseName, bool do_system, bool do_user)
List *relids = NIL;
ListCell *l;
- AssertArg(databaseName);
+ AssertArg(databaseName || (do_user && !do_system));
- if (strcmp(databaseName, get_database_name(MyDatabaseId)) != 0)
+ if (databaseName &&
+ strcmp(databaseName, get_database_name(MyDatabaseId)) != 0)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("can only reindex the currently open database")));
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 6f4d645..0baef83 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7040,6 +7040,7 @@ opt_if_exists: IF_P EXISTS { $$ = TRUE; }
* QUERY:
*
* REINDEX type <name> [FORCE]
+ * REINDEX USER TABLES
*
* FORCE no longer does anything, but we accept it for backwards compatibility
*****************************************************************************/
@@ -7073,6 +7074,16 @@ ReindexStmt:
n->do_user = true;
$$ = (Node *)n;
}
+ | REINDEX USER TABLES
+ {
+ ReindexStmt *n = makeNode(ReindexStmt);
+ n->kind = OBJECT_DATABASE;
+ n->name = NULL;
+ n->relation = NULL;
+ n->do_system = false;
+ n->do_user = true;
+ $$ = (Node *)n;
+ }
;
reindex_type:
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 76b2b04..490d200 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3148,7 +3148,7 @@ psql_completion(const char *text, int start, int end)
else if (pg_strcasecmp(prev_wd, "REINDEX") == 0)
{
static const char *const list_REINDEX[] =
- {"TABLE", "INDEX", "SYSTEM", "DATABASE", NULL};
+ {"TABLE", "INDEX", "SYSTEM", "DATABASE", "USER TABLES", NULL};
COMPLETE_WITH_LIST(list_REINDEX);
}
@@ -3161,6 +3161,8 @@ psql_completion(const char *text, int start, int end)
else if (pg_strcasecmp(prev_wd, "SYSTEM") == 0 ||
pg_strcasecmp(prev_wd, "DATABASE") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_databases);
+ else if (pg_strcasecmp(prev_wd, "USER") == 0)
+ COMPLETE_WITH_CONST("TABLES");
}
/* SECURITY LABEL */
diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c
index 561bbce..8e1b52d 100644
--- a/src/bin/scripts/reindexdb.c
+++ b/src/bin/scripts/reindexdb.c
@@ -28,8 +28,14 @@ static void reindex_system_catalogs(const char *dbname,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo);
+static void reindex_user_tables(const char *dbname,
+ const char *host, const char *port,
+ const char *username, enum trivalue prompt_password,
+ const char *progname, bool echo);
static void help(const char *progname);
+#define exit_nicely(code) exit(code)
+
int
main(int argc, char *argv[])
{
@@ -46,6 +52,7 @@ main(int argc, char *argv[])
{"system", no_argument, NULL, 's'},
{"table", required_argument, NULL, 't'},
{"index", required_argument, NULL, 'i'},
+ {"user-tables", no_argument, NULL, 'u'},
{"maintenance-db", required_argument, NULL, 2},
{NULL, 0, NULL, 0}
};
@@ -61,6 +68,7 @@ main(int argc, char *argv[])
const char *username = NULL;
enum trivalue prompt_password = TRI_DEFAULT;
bool syscatalog = false;
+ bool usertables = false;
bool alldb = false;
bool echo = false;
bool quiet = false;
@@ -73,7 +81,7 @@ main(int argc, char *argv[])
handle_help_version_opts(argc, argv, "reindexdb", help);
/* process command-line options */
- while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:ast:i:", long_options, &optindex)) != -1)
+ while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:ast:i:u", long_options, &optindex)) != -1)
{
switch (c)
{
@@ -113,6 +121,9 @@ main(int argc, char *argv[])
case 'i':
simple_string_list_append(&indexes, optarg);
break;
+ case 'u':
+ usertables = true;
+ break;
case 2:
maintenance_db = pg_strdup(optarg);
break;
@@ -194,6 +205,32 @@ main(int argc, char *argv[])
reindex_system_catalogs(dbname, host, port, username, prompt_password,
progname, echo);
}
+ else if (usertables)
+ {
+ if (tables.head != NULL)
+ {
+ fprintf(stderr, _("%s: cannot reindex specific table(s) and all user tables at the same time\n"), progname);
+ exit(1);
+ }
+ if (indexes.head != NULL)
+ {
+ fprintf(stderr, _("%s: cannot reindex specific index(es) and all user tables at the same time\n"), progname);
+ exit(1);
+ }
+
+ if (dbname == NULL)
+ {
+ if (getenv("PGDATABASE"))
+ dbname = getenv("PGDATABASE");
+ else if (getenv("PGUSER"))
+ dbname = getenv("PGUSER");
+ else
+ dbname = get_user_name_or_exit(progname);
+ }
+
+ reindex_user_tables(dbname, host, port, username, prompt_password,
+ progname, echo);
+ }
else
{
if (dbname == NULL)
@@ -336,6 +373,41 @@ reindex_system_catalogs(const char *dbname, const char *host, const char *port,
}
static void
+reindex_user_tables(const char *dbname, const char *host, const char *port,
+ const char *username, enum trivalue prompt_password,
+ const char *progname, bool echo)
+{
+ PQExpBufferData sql;
+
+ PGconn *conn;
+
+ initPQExpBuffer(&sql);
+
+ appendPQExpBuffer(&sql, "REINDEX USER TABLES;");
+
+ conn = connectDatabase(dbname, host, port, username, prompt_password,
+ progname, false);
+
+ /* This feature is only available starting in 9.5 */
+ if (PQserverVersion(conn) < 90500)
+ {
+ fprintf(stderr, _("%s: REINDEX USER TABLES is only available in server versions 9.5 and newer\n"),
+ progname);
+ exit_nicely(1);
+ }
+
+ if (!executeMaintenanceCommand(conn, sql.data, echo))
+ {
+ fprintf(stderr, _("%s: reindexing of user tables failed: %s"),
+ progname, PQerrorMessage(conn));
+ PQfinish(conn);
+ exit(1);
+ }
+ PQfinish(conn);
+ termPQExpBuffer(&sql);
+}
+
+static void
help(const char *progname)
{
printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
@@ -349,6 +421,7 @@ help(const char *progname)
printf(_(" -q, --quiet don't write any messages\n"));
printf(_(" -s, --system reindex system catalogs\n"));
printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
+ printf(_(" -u, --user-tables reindex all user tables\n"));
printf(_(" -V, --version output version information, then exit\n"));
printf(_(" -?, --help show this help, then exit\n"));
printf(_("\nConnection options:\n"));
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index d2c0b29..226fffd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2689,9 +2689,9 @@ typedef struct ConstraintsSetStmt
typedef struct ReindexStmt
{
NodeTag type;
- ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, etc. */
- RangeVar *relation; /* Table or index to reindex */
- const char *name; /* name of database to reindex */
+ ObjectType kind; /* OBJECT_INDEX, OBJECT_TABLE, OBJECT_DATABASE */
+ RangeVar *relation; /* Table or index to reindex; NULL for all */
+ const char *name; /* name of database to reindex, or NULL */
bool do_system; /* include system tables in database case */
bool do_user; /* include user tables in database case */
} ReindexStmt;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
