*** a/doc/src/sgml/ref/reindexdb.sgml
--- b/doc/src/sgml/ref/reindexdb.sgml
***************
*** 104,109 **** PostgreSQL documentation
--- 104,120 ----
+
+
+
+
+ Reindex concurrently.
+ Note that we cannot specify with -s (--system) option.
+
+
+
+
+
*** a/src/bin/scripts/reindexdb.c
--- b/src/bin/scripts/reindexdb.c
***************
*** 13,28 ****
#include "common.h"
#include "dumputils.h"
static void reindex_one_database(const char *name, const char *dbname,
const char *type, const char *host,
const char *port, const char *username,
enum trivalue prompt_password, const char *progname,
! bool echo);
static void reindex_all_databases(const char *maintenance_db,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
! const char *progname, bool echo,
bool quiet);
static void reindex_system_catalogs(const char *dbname,
const char *host, const char *port,
--- 13,49 ----
#include "common.h"
#include "dumputils.h"
+ #define IsToken(c) \
+ (IS_HIGHBIT_SET((c)) || isalnum((unsigned char) (c)) || (c) == '_')
+ /*
+ * Parsed CREATE INDEX statement. You can rebuild sql using
+ * sprintf(buf, "%s %s ON %s USING %s (%s)%s",
+ * create, index, table type, columns, options)
+ */
+ typedef struct IndexDef
+ {
+ char *create; /* CREATE INDEX or CREATE UNIQUE INDEX */
+ char *index; /* index name including schema */
+ char *table; /* table name including schema */
+ char *type; /* btree, hash, gist or gin */
+ char *columns; /* column definition */
+ char *options; /* options after columns, before TABLESPACE (e.g. COLLATE) */
+ char *tablespace; /* tablespace if specified */
+ char *where; /* WHERE content if specified */
+ } IndexDef;
+
+ static void parse_indexdef(IndexDef *stmt, const char *indexdef, const char *indexname, const char *tablename, Oid index, Oid table);
+ static bool reindex_indexes(PGconn *conn, PGresult *res, const char *progname, bool echo);
static void reindex_one_database(const char *name, const char *dbname,
const char *type, const char *host,
const char *port, const char *username,
enum trivalue prompt_password, const char *progname,
! bool echo, bool concurrently);
static void reindex_all_databases(const char *maintenance_db,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
! const char *progname, bool echo, bool concurrently,
bool quiet);
static void reindex_system_catalogs(const char *dbname,
const char *host, const char *port,
***************
*** 30,35 **** static void reindex_system_catalogs(const char *dbname,
--- 51,58 ----
const char *progname, bool echo);
static void help(const char *progname);
+
+
int
main(int argc, char *argv[])
{
***************
*** 47,52 **** main(int argc, char *argv[])
--- 70,76 ----
{"table", required_argument, NULL, 't'},
{"index", required_argument, NULL, 'i'},
{"maintenance-db", required_argument, NULL, 2},
+ {"concurrently", no_argument, NULL, 'C'},
{NULL, 0, NULL, 0}
};
***************
*** 64,69 **** main(int argc, char *argv[])
--- 88,94 ----
bool alldb = false;
bool echo = false;
bool quiet = false;
+ bool concurrently = false;
SimpleStringList indexes = {NULL, NULL};
SimpleStringList tables = {NULL, NULL};
***************
*** 73,79 **** 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)
{
switch (c)
{
--- 98,104 ----
handle_help_version_opts(argc, argv, "reindexdb", help);
/* process command-line options */
! while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:ast:i:C", long_options, &optindex)) != -1)
{
switch (c)
{
***************
*** 116,121 **** main(int argc, char *argv[])
--- 141,149 ----
case 2:
maintenance_db = pg_strdup(optarg);
break;
+ case 'C':
+ concurrently = true;
+ break;
default:
fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
exit(1);
***************
*** 166,172 **** main(int argc, char *argv[])
}
reindex_all_databases(maintenance_db, host, port, username,
! prompt_password, progname, echo, quiet);
}
else if (syscatalog)
{
--- 194,200 ----
}
reindex_all_databases(maintenance_db, host, port, username,
! prompt_password, progname, echo, concurrently, quiet);
}
else if (syscatalog)
{
***************
*** 181,186 **** main(int argc, char *argv[])
--- 209,220 ----
exit(1);
}
+ if (concurrently)
+ {
+ fprintf(stderr, _("%s: cannot reindex specific system catalogs and concurrenlty option at the same time\n"),progname);
+ exit(1);
+ }
+
if (dbname == NULL)
{
if (getenv("PGDATABASE"))
***************
*** 213,219 **** main(int argc, char *argv[])
for (cell = indexes.head; cell; cell = cell->next)
{
reindex_one_database(cell->val, dbname, "INDEX", host, port,
! username, prompt_password, progname, echo);
}
}
if (tables.head != NULL)
--- 247,253 ----
for (cell = indexes.head; cell; cell = cell->next)
{
reindex_one_database(cell->val, dbname, "INDEX", host, port,
! username, prompt_password, progname, echo, concurrently);
}
}
if (tables.head != NULL)
***************
*** 223,244 **** main(int argc, char *argv[])
for (cell = tables.head; cell; cell = cell->next)
{
reindex_one_database(cell->val, dbname, "TABLE", host, port,
! username, prompt_password, progname, echo);
}
}
/* reindex database only if neither index nor table is specified */
if (indexes.head == NULL && tables.head == NULL)
reindex_one_database(dbname, dbname, "DATABASE", host, port,
! username, prompt_password, progname, echo);
}
exit(0);
}
static void
reindex_one_database(const char *name, const char *dbname, const char *type,
const char *host, const char *port, const char *username,
! enum trivalue prompt_password, const char *progname, bool echo)
{
PQExpBufferData sql;
--- 257,659 ----
for (cell = tables.head; cell; cell = cell->next)
{
reindex_one_database(cell->val, dbname, "TABLE", host, port,
! username, prompt_password, progname, echo, concurrently);
}
}
/* reindex database only if neither index nor table is specified */
if (indexes.head == NULL && tables.head == NULL)
reindex_one_database(dbname, dbname, "DATABASE", host, port,
! username, prompt_password, progname, echo, concurrently);
}
exit(0);
}
+ static Oid
+ getoid(PGresult *res, int row, int col)
+ {
+ if (PQgetisnull(res, row, col))
+ return InvalidOid;
+ else
+ return (Oid)strtoul(PQgetvalue(res, row, col), NULL, 10);
+ }
+
+ static char *
+ getstr(PGresult *res, int row, int col)
+ {
+ if (PQgetisnull(res, row, col))
+ return NULL;
+ else
+ return PQgetvalue(res, row, col);
+ }
+
+ static char *
+ parse_error(Oid index)
+ {
+ fprintf(stderr, "unexpected index definition: %d", index);
+ return NULL;
+ }
+
+ /*
+ * Skip until 'end' character found. The 'end' character is replaced with \0.
+ * Returns the next character of the 'end', or NULL if 'end' is not found.
+ */
+ static char *
+ skip_until(Oid index, char *sql, char end)
+ {
+ char instr = 0;
+ int nopen = 0;
+
+ for (; *sql && (nopen > 0 || instr != 0 || *sql != end); sql++)
+ {
+ if (instr)
+ {
+ if (sql[0] == instr)
+ {
+ if (sql[1] == instr)
+ sql++;
+ else
+ instr = 0;
+ }
+ else if (sql[0] == '\\')
+ sql++; /* next char is always string */
+ }
+ else
+ {
+ switch (sql[0])
+ {
+ case '(':
+ nopen++;
+ break;
+ case ')':
+ nopen--;
+ break;
+ case '\'':
+ case '"':
+ instr = sql[0];
+ break;
+ }
+ }
+ }
+
+ if (nopen == 0 && instr == 0)
+ {
+ if (*sql)
+ {
+ *sql = '\0';
+ return sql + 1;
+ }
+ else
+ return NULL;
+ }
+
+ /* error */
+ return parse_error(index);
+ }
+
+ static char *
+ skip_const(Oid index, char *sql, const char *arg1, const char *arg2)
+ {
+ size_t len;
+
+ if ((arg1 && strncmp(sql, arg1, (len = strlen(arg1))) == 0) ||
+ (arg2 && strncmp(sql, arg2, (len = strlen(arg2))) == 0))
+ {
+ sql[len] = '\0';
+ return sql + len + 1;
+ }
+
+ /* error */
+ return parse_error(index);
+ }
+
+ static char *
+ skip_ident(Oid index, char *sql)
+ {
+ while (*sql && isspace((unsigned char) *sql))
+ sql++;
+
+ if (*sql == '"')
+ {
+ sql++;
+ for (;;)
+ {
+ char *end = strchr(sql, '"');
+ if (end == NULL)
+ return parse_error(index);
+ else if (end[1] != '"')
+ {
+ end[1] = '\0';
+ return end + 2;
+ }
+ else /* escaped quote ("") */
+ sql = end + 2;
+ }
+ }
+ else
+ {
+ while (*sql && IsToken(*sql))
+ sql++;
+ *sql = '\0';
+ return sql + 1;
+ }
+
+ /* error */
+ return parse_error(index);
+ }
+
+ static char *
+ skip_until_const(Oid index, char *sql, const char *what)
+ {
+ char *pos;
+
+ if ((pos = strstr(sql, what)))
+ {
+ size_t len;
+
+ len = strlen(what);
+ pos[-1] = '\0';
+ return pos + len + 1;
+ }
+
+ /* error */
+ return parse_error(index);
+ }
+
+ /*
+ * Aquire lock to table
+ * In this function, the transaction is began with READ COMMITTED isolation level.
+ * Note that we try to aquire lock only once.
+ * XXX : To optinal wait_time
+ */
+ static bool
+ aquire_lock(PGconn *conn, const char *lock_query, const char *progname, bool echo)
+ {
+ PQExpBufferData sql;
+ int wait_time = 60;
+ bool ret = false;
+
+ initPQExpBuffer(&sql);
+
+ appendPQExpBuffer(&sql, "BEGIN ISOLATION LEVEL READ COMMITTED");
+ executeCommand(conn, sql.data, progname, echo);
+
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql, "SET statement_timeout TO %d", wait_time);
+ executeCommand(conn, sql.data, progname, echo);
+
+ /* Aquire lock */
+ ret = executeMaintenanceCommand(conn, lock_query, echo);
+
+ executeCommand(conn, "RESET statement_timeout", progname, echo);
+ return ret;
+ }
+
+ /*
+ * Swapping old index and new index
+ * We swap value of relfilenode in pg_class actually.
+ */
+ static bool
+ swap_index(PGconn *conn, const Oid index, const char *progname, bool echo)
+ {
+ PQExpBufferData sql;
+ PGresult *res = NULL;
+ Oid tmpindex_oid, orig_filenode, tmp_filenode;;
+
+ initPQExpBuffer(&sql);
+
+ /* Get oid of new index */
+ appendPQExpBuffer(&sql,
+ "SELECT pgc.oid FROM pg_class pgc "
+ "WHERE pgc.relname = 'index_%d' "
+ "AND pgc.relkind = 'i'", index);
+ res = executeQuery(conn, sql.data, progname, echo);
+ Assert(PQntuples(res) == 1);
+
+ tmpindex_oid = getoid(res, 0, 0);
+
+ /* Get each value of relfilenode */
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql,
+ "SELECT pgc.oid, pgc.relfilenode "
+ "FROM pg_Class pgc "
+ "WHERE pgc.oid = %u OR pgc.oid = %u", index, tmpindex_oid);
+ res = executeQuery(conn, sql.data, progname, echo);
+ Assert(PQntuples(res) == 2);
+
+ orig_filenode = (getoid(res, 0, 0) == index) ? getoid(res, 0, 1) : getoid(res, 1,1);
+ tmp_filenode = (getoid(res, 0, 0) == tmpindex_oid) ? getoid(res, 0, 1) : getoid(res, 1, 1);
+
+ if (!(aquire_lock(conn, "LOCK TABLE pg_class IN ROW EXCLUSIVE MODE", progname, echo)))
+ {
+ fprintf(stderr, _("aquire_lock failed in connection for pa_class"));
+ return false;
+ }
+
+
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql,
+ "UPDATE pg_class SET relfilenode = %u WHERE oid = %u", tmp_filenode, index);
+ executeCommand(conn, sql.data, progname, echo);
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql,
+ "UPDATE pg_class SET relfilenode = %u WHERE oid = %u", orig_filenode, tmpindex_oid);
+ executeCommand(conn, sql.data, progname, echo);
+
+ PQclear(res);
+ termPQExpBuffer(&sql);
+
+ return true;
+ }
+
+ static void
+ parse_indexdef(IndexDef *stmt, const char *indexdef, const char *indexname, const char *tablename, Oid index, Oid table)
+ {
+
+ const char *limit = strchr(indexdef, '\0');
+ char *orig = (char*)indexdef;
+
+ /* CREATE [UNIQUE] INDEX */
+ stmt->create = orig;
+ orig = skip_const(index, orig, "CREATE INDEX", "CREATE UNIQUE INDEX");
+ /* index */
+ stmt->index = orig;
+ orig = skip_const(index, orig, indexname, NULL);
+ /* ON */
+ orig = skip_const(index, orig, "ON", NULL);
+ /* table */
+ stmt->table = orig;
+ orig = skip_until(index, orig, ' ');
+ /* USING */
+ orig = skip_const(index, orig, "USING", NULL);
+ /* type */
+ stmt->type = orig;
+ orig = skip_ident(index, orig);
+ /* (columns) */
+ if ((orig = strchr(orig, '(')) == NULL)
+ parse_error(index);
+
+ orig++;
+ stmt->columns = orig;
+ if ((orig = skip_until(index, orig, ')')) == NULL)
+ parse_error(index);
+
+ /* options */
+ stmt->options = orig;
+ stmt->tablespace = NULL;
+ stmt->where = NULL;
+
+ /* Is there a tablespace? Note that apparently there is never, but
+ * if there was one it would appear here. */
+ if (orig< limit && strstr(orig, "TABLESPACE"))
+ {
+ orig = skip_until_const(index, orig, "TABLESPACE");
+ stmt->tablespace = orig;
+ orig = skip_ident(index, orig);
+ }
+
+ /* Note: assuming WHERE is the only clause allowed after TABLESPACE */
+ if (orig < limit && strstr(orig, "WHERE"))
+ {
+ orig = skip_until_const(index, orig, "WHERE");
+ stmt->where = orig;
+ }
+ }
+
+ static bool
+ reindex_indexes(PGconn *conn, PGresult *res, const char *progname, bool echo)
+ {
+ PGresult *res2 = NULL;
+ PQExpBufferData sql;
+ int num = PQntuples(res);
+ int i = 0;
+ Oid indexoid, tableoid;
+ char *schemaname, *tablename, *indexname, *indexdef;
+ bool ret = false;
+
+ initPQExpBuffer(&sql);
+
+ for (i = 0; i < num; i++)
+ {
+ IndexDef stmt;
+
+ indexoid = getoid(res, i, 0);
+ indexname = getstr(res, i, 1);
+ tableoid = getoid(res, i, 2);
+ tablename = getstr(res, i, 3);
+ schemaname = getstr(res, i, 4);
+ indexdef = getstr(res, i, 5);
+
+ /* Check if new index exist already */
+ appendPQExpBuffer(&sql,
+ "SELECT oid FROM pg_class pgc "
+ "WHERE relname = 'index_%u'", indexoid);
+ res2 = executeQuery(conn, sql.data, progname, echo);
+ resetPQExpBuffer(&sql);
+
+ if (PQntuples(res2) > 0)
+ {
+ fprintf(stderr,
+ _("%s: \"%s\".\"index_%u\" already exists, skip reindexing concurrently")
+ , progname, schemaname, indexoid);
+ PQclear(res2);
+ continue;
+ }
+
+ parse_indexdef(&stmt, indexdef, indexname, tablename, indexoid, tableoid);
+
+ appendPQExpBuffer(&sql, "%s CONCURRENTLY index_%u ON %s USING %s (%s)%s",
+ stmt.create, indexoid, stmt.table, stmt.type, stmt.columns, stmt.options);
+
+ if (stmt.tablespace)
+ appendPQExpBuffer(&sql, " TABLESPACE %s",
+ stmt.tablespace);
+ if (stmt.where)
+ appendPQExpBuffer(&sql, " WHERE %s", stmt.where);
+
+ /* Create index concurrently */
+ if(!executeMaintenanceCommand(conn, sql.data, echo ))
+ {
+ fprintf(stderr,
+ _("%s: Cannot create index concurrently \"%s\".\"index_%u\""),
+ progname, schemaname, indexoid);
+ ret = false;
+ break;
+ }
+
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql, "LOCK TABLE %s IN ACCESS EXCLUSIVE MODE",
+ stmt.table);
+
+ /* Aquire ACCESS EXCLUSIVE LOCK for table */
+ if (!(aquire_lock(conn, sql.data, progname, echo)))
+ {
+ fprintf(stderr, "aquire_lock() failed in connection for %s",
+ tablename);
+ goto drop_idx;
+ }
+
+ ret = swap_index(conn, indexoid, progname, echo);
+ executeCommand(conn, "COMMIT", progname, echo);
+
+ drop_idx:
+ PQclear(res2);
+ resetPQExpBuffer(&sql);
+ appendPQExpBuffer(&sql, "DROP INDEX %s.index_%u", schemaname, indexoid);
+ executeMaintenanceCommand(conn, sql.data, echo);
+ resetPQExpBuffer(&sql);
+ }
+
+ termPQExpBuffer(&sql);
+ return ret;
+
+ }
+
static void
reindex_one_database(const char *name, const char *dbname, const char *type,
const char *host, const char *port, const char *username,
! enum trivalue prompt_password, const char *progname, bool echo,
! bool concurrently)
{
PQExpBufferData sql;
***************
*** 246,276 **** reindex_one_database(const char *name, const char *dbname, const char *type,
initPQExpBuffer(&sql);
- appendPQExpBufferStr(&sql, "REINDEX");
- if (strcmp(type, "TABLE") == 0)
- appendPQExpBuffer(&sql, " TABLE %s", name);
- else if (strcmp(type, "INDEX") == 0)
- appendPQExpBuffer(&sql, " INDEX %s", name);
- else if (strcmp(type, "DATABASE") == 0)
- appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
- appendPQExpBufferStr(&sql, ";");
-
conn = connectDatabase(dbname, host, port, username, prompt_password,
progname, false);
! if (!executeMaintenanceCommand(conn, sql.data, echo))
{
if (strcmp(type, "TABLE") == 0)
! fprintf(stderr, _("%s: reindexing of table \"%s\" in database \"%s\" failed: %s"),
! progname, name, dbname, PQerrorMessage(conn));
! if (strcmp(type, "INDEX") == 0)
! fprintf(stderr, _("%s: reindexing of index \"%s\" in database \"%s\" failed: %s"),
! progname, name, dbname, PQerrorMessage(conn));
! else
! fprintf(stderr, _("%s: reindexing of database \"%s\" failed: %s"),
! progname, dbname, PQerrorMessage(conn));
! PQfinish(conn);
! exit(1);
}
PQfinish(conn);
--- 661,734 ----
initPQExpBuffer(&sql);
conn = connectDatabase(dbname, host, port, username, prompt_password,
progname, false);
! if (!concurrently)
{
+ appendPQExpBufferStr(&sql, "REINDEX");
if (strcmp(type, "TABLE") == 0)
! appendPQExpBuffer(&sql, " TABLE %s", name);
! else if (strcmp(type, "INDEX") == 0)
! appendPQExpBuffer(&sql, " INDEX %s", name);
! else if (strcmp(type, "DATABASE") == 0)
! appendPQExpBuffer(&sql, " DATABASE %s", fmtId(name));
! appendPQExpBufferStr(&sql, ";");
!
!
! if (!executeMaintenanceCommand(conn, sql.data, echo))
! {
! if (strcmp(type, "TABLE") == 0)
! fprintf(stderr, _("%s: reindexing of table \"%s\" in database \"%s\" failed: %s"),
! progname, name, dbname, PQerrorMessage(conn));
! if (strcmp(type, "INDEX") == 0)
! fprintf(stderr, _("%s: reindexing of index \"%s\" in database \"%s\" failed: %s"),
! progname, name, dbname, PQerrorMessage(conn));
! else
! fprintf(stderr, _("%s: reindexing of database \"%s\" failed: %s"),
! progname, dbname, PQerrorMessage(conn));
! PQfinish(conn);
! exit(1);
! }
! }
! else
! {
! PGresult *res = NULL;
!
! /* We get informations are neccesary to swap index */
! appendPQExpBufferStr(&sql,
! "SELECT pgind.indexrelid, pgc1.relname, pgc2.oid,"
! " pgc2.relname, nsp.nspname, inddef.indexdef "
! "FROM pg_class pgc1, pg_class pgc2, pg_index pgind, "
! "pg_namespace nsp, pg_indexes inddef "
! "WHERE pgc1.oid = pgind.indexrelid AND pgc2.oid = pgind.indrelid "
! "AND pgc2.relnamespace = nsp.oid AND inddef.schemaname = nsp.nspname "
! "AND inddef.indexname = pgc1.relname AND pgind.indisvalid "
! "AND nsp.nspname NOT IN ('pg_catalog', 'information_schema') " );
! if (strcmp(type, "TABLE") == 0)
! appendPQExpBuffer(&sql, "AND pgc2.relname = '%s' ", name);
! else if (strcmp(type, "INDEX") == 0)
! appendPQExpBuffer(&sql, "AND pgc1.relname = '%s' ", name);
!
! appendPQExpBufferStr(&sql, "ORDER BY nsp.nspname, pgc1.relname");
!
! res = executeQuery(conn, sql.data, progname, echo);
!
! if (PQntuples(res) == 0)
! {
! if (strcmp(type, "DATABASE") == 0 || strcmp(type, "TABLE") == 0)
! fprintf(stderr,
! _("%s : \"%s\" \"%s\" dose not have any index\n"),
! progname, name, type);
! else
! fprintf(stderr,
! _("%s: \"%s\" INDEX dose not exist\n"),
! progname, name);
! PQfinish(conn);
! termPQExpBuffer(&sql);
! exit(1);
! }
! reindex_indexes(conn, res, progname, echo);
}
PQfinish(conn);
***************
*** 281,287 **** static void
reindex_all_databases(const char *maintenance_db,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
! const char *progname, bool echo, bool quiet)
{
PGconn *conn;
PGresult *result;
--- 739,745 ----
reindex_all_databases(const char *maintenance_db,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
! const char *progname, bool echo, bool concurrently, bool quiet)
{
PGconn *conn;
PGresult *result;
***************
*** 303,309 **** reindex_all_databases(const char *maintenance_db,
}
reindex_one_database(dbname, dbname, "DATABASE", host, port, username,
! prompt_password, progname, echo);
}
PQclear(result);
--- 761,767 ----
}
reindex_one_database(dbname, dbname, "DATABASE", host, port, username,
! prompt_password, progname, echo, concurrently);
}
PQclear(result);
***************
*** 343,348 **** help(const char *progname)
--- 801,807 ----
printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
printf(_("\nOptions:\n"));
printf(_(" -a, --all reindex all databases\n"));
+ printf(_(" -C, --concurrently reindex concurrently\n"));
printf(_(" -d, --dbname=DBNAME database to reindex\n"));
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));