Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hppNI-0005cU-PV for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jul 2019 07:38:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hppNH-00081P-6j for pgsql-hackers@arkaria.postgresql.org; Tue, 23 Jul 2019 07:38:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hppNF-0007ym-7T for pgsql-hackers@lists.postgresql.org; Tue, 23 Jul 2019 07:38:14 +0000 Received: from out1-smtp.messagingengine.com ([66.111.4.25]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1hppNB-0006sH-7i for pgsql-hackers@lists.postgresql.org; Tue, 23 Jul 2019 07:38:12 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id 9304C21B2A; Tue, 23 Jul 2019 03:38:07 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Tue, 23 Jul 2019 03:38:07 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= date:from:to:cc:subject:message-id:references:mime-version :content-type:in-reply-to; s=fm2; bh=ksV8h8+tlZ3uUrfC7RmX0qF2zsc 79JFX6z9EGh32P2s=; b=WvSRqSQ5Ro/JBYRRRWYGlRkxqa4WZONAk5WtA6jujxk J4mrcdTKQxFa+bVwfKs5O5pnlzsCma1rShrb6NrDKQ+mCwc1kq0fUhFTefdRB0U0 fBOcpBWKhwdfzkcUYbFa184bEfCVqGDVE/GZj81uLyNk23u/PaGRPfBoERq1w/5p pNcyOoUcK3oHMGQHidiiKtezW2ZtenWT+ZaczbEI2GiW0fx7SVxFB8P8N2gohUZ/ N10xgC22hnjmF65rND2mOUk1jzp3AnlrznQAw+zzNgTcx8QLNJUiK8k8txBlz0WP 7tYplHMH1De9UP1yV2tbtvuGQuI3vWdTeVONFGlasfA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; bh=ksV8h8 +tlZ3uUrfC7RmX0qF2zsc79JFX6z9EGh32P2s=; b=KhUDxB9kGytooaieHGjHvR 7z2MjZB0OPZxrtaaCHKKjK6ALEQHkSYFX+YYe0kJbDlsSVvvWv1P/+nlFuoBqMsN UIq/jFLpD3AzW6VH/r7eHaq5XR6Lz1ixTxYrrpwLOT8yLIkWUhTMTdo2tYB85EHB 65FrYuE7VxNhf8e53I95q6El/a0gywpLEmQQ+7PUjM/+Im95xLy0V5j31xGfGZNL BJKJ9bGOReAoRXfSrGKcPX+m0eJtLWO9RiTEwrx2COxJIW1exyA1gc8f3QD/kp6K k8K/LmZ0cXSg8XsnYg44mBgzsheAMf47uuPEOD1Jwn5iLGgsaTl2zL+3w+ll652w == X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeduvddrjeejgdegvdcutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdejtddmnecujfgurhepfffhvffukfhfgggtuggjsehgtderredttddv necuhfhrohhmpefoihgthhgrvghlucfrrghquhhivghruceomhhitghhrggvlhesphgrqh huihgvrhdrgiihiieqnecukfhppeduuddurddutddvrddukedtrddukeehnecurfgrrhgr mhepmhgrihhlfhhrohhmpehmihgthhgrvghlsehprghquhhivghrrdighiiinecuvehluh hsthgvrhfuihiivgeptd X-ME-Proxy: Received: from paquier.xyz (ee0822lan1.rev.em-net.ne.jp [111.102.180.185]) by mail.messagingengine.com (Postfix) with ESMTPA id 6658C380076; Tue, 23 Jul 2019 03:38:04 -0400 (EDT) Date: Tue, 23 Jul 2019 16:38:01 +0900 From: Michael Paquier To: Julien Rouhaud Cc: Alvaro Herrera , Peter Eisentraut , Thomas Munro , Daniel Verite , Tom Lane , PostgreSQL Hackers , Kevin Grittner Subject: Re: Add parallelism and glibc dependent only options to reindexdb Message-ID: <20190723073801.GD2059@paquier.xyz> References: <20190712055717.GK2149@paquier.xyz> <20190717075902.GA20614@paquier.xyz> <20190718004514.GB1416@paquier.xyz> <20190719003529.GB1859@paquier.xyz> <20190722041114.GD1757@paquier.xyz> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="pyE8wggRBhVBcj8z" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --pyE8wggRBhVBcj8z Content-Type: multipart/mixed; boundary="maH1Gajj2nflutpK" Content-Disposition: inline --maH1Gajj2nflutpK Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On Mon, Jul 22, 2019 at 02:40:19PM +0200, Julien Rouhaud wrote: > Right, so I kept the long option. Also this comment was outdated, as > the --jobs is now just ignored with a list of indexes, so I fixed that > too. + if (!parallel) + { + if (user_list =3D=3D NULL) + { + /* + * Create a dummy list with an empty string, as user requires an + * element. + */ + process_list =3D pg_malloc0(sizeof(SimpleStringList)); + simple_string_list_append(process_list, ""); + } + } This part looks a bit hacky and this is needed because we don't have a list of objects when doing a non-parallel system or database reindex. The deal is that we just want a list with one element: the database of the connection. Wouldn't it be more natural to assign the database name here using PQdb(conn)? Then add an assertion at the top of run_reindex_command() checking for a non-NULL name? > I considered this, but it would require to adapt all code that declare > SimpleStringList stack variable (vacuumdb.c, clusterdb.c, > createuser.c, pg_dumpall.c and pg_dump.c), so it looked like too much > trouble to avoid 2 local variables here and 1 in vacuumdb.c. I don't > have a strong opinion here, so I can go for it if you prefer. Okay. This is a tad wider than the original patch proposal, and this adds two lines. So let's discard that for now and keep it simple. >> +$node->issues_sql_like([qw(reindexdb -j2)], >> + qr/statement: REINDEX TABLE public.test1/, >> + 'Global and parallel reindex will issue per-table REINDEX'); >> Would it make sense to have some tests for schemas here? >> >> One of my comments in [1] has not been answered. What about >> the decomposition of a list of schemas into a list of tables when >> using the parallel mode? >=20 > I did that in attached v6, and added suitable regression tests. The two tests for "reindexdb -j2" can be combined into a single call, checking for both commands to be generated in the same output. The second command triggering a reindex on two schemas cannot be used to check for the generation of both s1.t1 and s2.t2 as the ordering may not be guaranteed. The commands arrays also looked inconsistent with the rest. Attached is an updated patch with some format modifications and the fixes for the tests. -- Michael --maH1Gajj2nflutpK Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="reindex_parallel_v7.patch" Content-Transfer-Encoding: quoted-printable diff --git a/doc/src/sgml/ref/reindexdb.sgml b/doc/src/sgml/ref/reindexdb.s= gml index 25b5a72770..477b77c6e9 100644 --- a/doc/src/sgml/ref/reindexdb.sgml +++ b/doc/src/sgml/ref/reindexdb.sgml @@ -166,6 +166,31 @@ PostgreSQL documentation =20 + + + + + + Execute the reindex commands in parallel by running + njobs + commands simultaneously. This option reduces the time of the + processing but it also increases the load on the database server. + + + reindexdb will open + njobs connections t= o the + database, so make sure your + setting is high enough to accommodate all connections. + + + Note that this option is ignored with the + option to prevent deadlocks when processing multiple indexes from + the same relation, and incompatible with the + + + diff --git a/src/bin/scripts/Makefile b/src/bin/scripts/Makefile index 3cd793b134..ede665090f 100644 --- a/src/bin/scripts/Makefile +++ b/src/bin/scripts/Makefile @@ -29,7 +29,7 @@ dropdb: dropdb.o common.o $(WIN32RES) | submake-libpq sub= make-libpgport submake- dropuser: dropuser.o common.o $(WIN32RES) | submake-libpq submake-libpgpor= t submake-libpgfeutils clusterdb: clusterdb.o common.o $(WIN32RES) | submake-libpq submake-libpgp= ort submake-libpgfeutils vacuumdb: vacuumdb.o common.o scripts_parallel.o $(WIN32RES) | submake-lib= pq submake-libpgport submake-libpgfeutils -reindexdb: reindexdb.o common.o $(WIN32RES) | submake-libpq submake-libpgp= ort submake-libpgfeutils +reindexdb: reindexdb.o common.o scripts_parallel.o $(WIN32RES) | submake-l= ibpq submake-libpgport submake-libpgfeutils pg_isready: pg_isready.o common.o $(WIN32RES) | submake-libpq submake-libp= gport submake-libpgfeutils =20 install: all installdirs diff --git a/src/bin/scripts/reindexdb.c b/src/bin/scripts/reindexdb.c index 219a9a9211..895dc03737 100644 --- a/src/bin/scripts/reindexdb.c +++ b/src/bin/scripts/reindexdb.c @@ -10,10 +10,14 @@ */ =20 #include "postgres_fe.h" + +#include "catalog/pg_class_d.h" #include "common.h" #include "common/logging.h" +#include "fe_utils/connect.h" #include "fe_utils/simple_list.h" #include "fe_utils/string_utils.h" +#include "scripts_parallel.h" =20 typedef enum ReindexType { @@ -25,16 +29,26 @@ typedef enum ReindexType } ReindexType; =20 =20 -static void reindex_one_database(const char *name, const char *dbname, - ReindexType type, const char *host, +static SimpleStringList *get_parallel_object_list(PGconn *conn, + ReindexType type, + SimpleStringList *user_list, + bool echo); +static void reindex_one_database(const char *dbname, ReindexType type, + SimpleStringList *user_list, const char *host, const char *port, const char *username, enum trivalue prompt_password, const char *progname, - bool echo, bool verbose, bool concurrently); + bool echo, bool verbose, bool concurrently, + int concurrentCons); 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, bool verbose, bool concurrently); + bool quiet, bool verbose, bool concurrently, + int concurrentCons); +static void run_reindex_command(PGconn *conn, ReindexType type, + const char *name, bool echo, bool verbose, + bool concurrently, bool async); + static void help(const char *progname); =20 int @@ -54,6 +68,7 @@ main(int argc, char *argv[]) {"system", no_argument, NULL, 's'}, {"table", required_argument, NULL, 't'}, {"index", required_argument, NULL, 'i'}, + {"jobs", required_argument, NULL, 'j'}, {"verbose", no_argument, NULL, 'v'}, {"concurrently", no_argument, NULL, 1}, {"maintenance-db", required_argument, NULL, 2}, @@ -79,6 +94,9 @@ main(int argc, char *argv[]) SimpleStringList indexes =3D {NULL, NULL}; SimpleStringList tables =3D {NULL, NULL}; SimpleStringList schemas =3D {NULL, NULL}; + int concurrentCons =3D 1; + int tbl_count =3D 0, + nsp_count =3D 0; =20 pg_logging_init(argv[0]); progname =3D get_progname(argv[0]); @@ -87,7 +105,7 @@ main(int argc, char *argv[]) handle_help_version_opts(argc, argv, "reindexdb", help); =20 /* process command-line options */ - while ((c =3D getopt_long(argc, argv, "h:p:U:wWeqS:d:ast:i:v", long_optio= ns, &optindex)) !=3D -1) + while ((c =3D getopt_long(argc, argv, "h:p:U:wWeqS:d:ast:i:j:v", long_opt= ions, &optindex)) !=3D -1) { switch (c) { @@ -114,6 +132,7 @@ main(int argc, char *argv[]) break; case 'S': simple_string_list_append(&schemas, optarg); + nsp_count++; break; case 'd': dbname =3D pg_strdup(optarg); @@ -126,10 +145,25 @@ main(int argc, char *argv[]) break; case 't': simple_string_list_append(&tables, optarg); + tbl_count++; break; case 'i': simple_string_list_append(&indexes, optarg); break; + case 'j': + concurrentCons =3D atoi(optarg); + if (concurrentCons <=3D 0) + { + pg_log_error("number of parallel jobs must be at least 1"); + exit(1); + } + if (concurrentCons > FD_SETSIZE - 1) + { + pg_log_error("too many parallel jobs requested (maximum: %d)", + FD_SETSIZE - 1); + exit(1); + } + break; case 'v': verbose =3D true; break; @@ -194,7 +228,8 @@ main(int argc, char *argv[]) } =20 reindex_all_databases(maintenance_db, host, port, username, - prompt_password, progname, echo, quiet, verbose, concurrently); + prompt_password, progname, echo, quiet, verbose, + concurrently, concurrentCons); } else if (syscatalog) { @@ -214,6 +249,12 @@ main(int argc, char *argv[]) exit(1); } =20 + if (concurrentCons > 1) + { + pg_log_error("cannot use multiple jobs to reindex system catalogs"); + exit(1); + } + if (dbname =3D=3D NULL) { if (getenv("PGDATABASE")) @@ -224,9 +265,9 @@ main(int argc, char *argv[]) dbname =3D get_user_name_or_exit(progname); } =20 - reindex_one_database(NULL, dbname, REINDEX_SYSTEM, host, + reindex_one_database(dbname, REINDEX_SYSTEM, NULL, host, port, username, prompt_password, progname, - echo, verbose, concurrently); + echo, verbose, concurrently, 1); } else { @@ -241,61 +282,57 @@ main(int argc, char *argv[]) } =20 if (schemas.head !=3D NULL) - { - SimpleStringListCell *cell; - - for (cell =3D schemas.head; cell; cell =3D cell->next) - { - reindex_one_database(cell->val, dbname, REINDEX_SCHEMA, host, - port, username, prompt_password, progname, - echo, verbose, concurrently); - } - } + reindex_one_database(dbname, REINDEX_SCHEMA, &schemas, host, + port, username, prompt_password, progname, + echo, verbose, concurrently, + Min(concurrentCons, nsp_count)); =20 if (indexes.head !=3D NULL) { - SimpleStringListCell *cell; - - for (cell =3D indexes.head; cell; cell =3D cell->next) - { - reindex_one_database(cell->val, dbname, REINDEX_INDEX, host, - port, username, prompt_password, progname, - echo, verbose, concurrently); - } + /* + * An index list cannot be processed by multiple connections, as + * it could cause conflicts if reindexing multiple indexes from + * the same table. We simply ignore the passed number of jobs if + * any. + */ + reindex_one_database(dbname, REINDEX_INDEX, &indexes, host, + port, username, prompt_password, progname, + echo, verbose, concurrently, 1); } + if (tables.head !=3D NULL) - { - SimpleStringListCell *cell; - - for (cell =3D tables.head; cell; cell =3D cell->next) - { - reindex_one_database(cell->val, dbname, REINDEX_TABLE, host, - port, username, prompt_password, progname, - echo, verbose, concurrently); - } - } + reindex_one_database(dbname, REINDEX_TABLE, &tables, host, + port, username, prompt_password, progname, + echo, verbose, concurrently, + Min(concurrentCons, tbl_count)); =20 /* * reindex database only if neither index nor table nor schema is * specified */ if (indexes.head =3D=3D NULL && tables.head =3D=3D NULL && schemas.head = =3D=3D NULL) - reindex_one_database(NULL, dbname, REINDEX_DATABASE, host, + reindex_one_database(dbname, REINDEX_DATABASE, NULL, host, port, username, prompt_password, progname, - echo, verbose, concurrently); + echo, verbose, concurrently, concurrentCons); } =20 exit(0); } =20 static void -reindex_one_database(const char *name, const char *dbname, ReindexType typ= e, - const char *host, const char *port, const char *username, +reindex_one_database(const char *dbname, ReindexType type, + SimpleStringList *user_list, const char *host, + const char *port, const char *username, enum trivalue prompt_password, const char *progname, bool echo, - bool verbose, bool concurrently) + bool verbose, bool concurrently, int concurrentCons) { - PQExpBufferData sql; PGconn *conn; + SimpleStringListCell *cell; + bool parallel =3D concurrentCons > 1; + SimpleStringList *process_list =3D user_list; + ReindexType process_type =3D type; + ParallelSlot *slots; + bool failed =3D false; =20 conn =3D connectDatabase(dbname, host, port, username, prompt_password, progname, echo, false, false); @@ -308,6 +345,107 @@ reindex_one_database(const char *name, const char *db= name, ReindexType type, exit(1); } =20 + if (!parallel) + { + if (user_list =3D=3D NULL) + { + /* + * Create a dummy list with an empty string, as user requires an + * element. + */ + process_list =3D pg_malloc0(sizeof(SimpleStringList)); + simple_string_list_append(process_list, ""); + } + } + else + { + /* + * Database-wide parallel reindex requires special processing. If + * multiple jobs were asked, we have to reindex system catalogs first, + * as they can't be processed in parallel. + */ + if (process_type =3D=3D REINDEX_DATABASE) + { + Assert(user_list =3D=3D NULL); + run_reindex_command(conn, REINDEX_SYSTEM, NULL, echo, verbose, + concurrently, false); + + process_list =3D get_parallel_object_list(conn, process_type, + user_list, echo); + process_type =3D REINDEX_TABLE; + + /* Bail out if nothing to process */ + if (process_list =3D=3D NULL) + { + PQfinish(conn); + return; + } + } + else if (process_type =3D=3D REINDEX_SCHEMA) + { + Assert(user_list !=3D NULL); + + process_list =3D get_parallel_object_list(conn, process_type, + user_list, echo); + process_type =3D REINDEX_TABLE; + + /* Bail out if nothing to process */ + if (process_list =3D=3D NULL) + { + PQfinish(conn); + return; + } + } + else + Assert(user_list !=3D NULL); + } + + slots =3D ParallelSlotsSetup(dbname, host, port, username, prompt_passwor= d, + progname, echo, conn, concurrentCons); + + cell =3D process_list->head; + do + { + const char *objname =3D cell->val; + ParallelSlot *free_slot =3D NULL; + + if (CancelRequested) + { + failed =3D true; + goto finish; + } + + free_slot =3D ParallelSlotsGetIdle(slots, concurrentCons); + if (!free_slot) + { + failed =3D true; + goto finish; + } + + run_reindex_command(free_slot->connection, process_type, objname, + echo, verbose, concurrently, true); + + cell =3D cell->next; + } while (cell !=3D NULL); + + if (!ParallelSlotsWaitCompletion(slots, concurrentCons)) + failed =3D true; + +finish: + ParallelSlotsTerminate(slots, concurrentCons); + pfree(slots); + + if (failed) + exit(1); +} + +static void +run_reindex_command(PGconn *conn, ReindexType type, const char *name, + bool echo, bool verbose, bool concurrently, bool async) +{ + PQExpBufferData sql; + bool status; + /* build the REINDEX query */ initPQExpBuffer(&sql); =20 @@ -358,7 +496,17 @@ reindex_one_database(const char *name, const char *dbn= ame, ReindexType type, /* finish the query */ appendPQExpBufferChar(&sql, ';'); =20 - if (!executeMaintenanceCommand(conn, sql.data, echo)) + if (async) + { + if (echo) + printf("%s\n", sql.data); + + status =3D PQsendQuery(conn, sql.data) =3D=3D 1; + } + else + status =3D executeMaintenanceCommand(conn, sql.data, echo); + + if (!status) { switch (type) { @@ -383,20 +531,135 @@ reindex_one_database(const char *name, const char *d= bname, ReindexType type, name, PQdb(conn), PQerrorMessage(conn)); break; } - PQfinish(conn); - exit(1); + if (!async) + { + PQfinish(conn); + exit(1); + } } =20 - PQfinish(conn); termPQExpBuffer(&sql); } =20 +/* + * Prepare the list of objects to process by querying the catalogs. + * + * This function will return a SimpleStringList object containing the enti= re + * list of tables in the given database that should be processed by a para= llel + * database-wide reindex (excluding system tables), or NULL if there's no = such + * table. + */ +static SimpleStringList * +get_parallel_object_list(PGconn *conn, ReindexType type, + SimpleStringList *user_list, bool echo) +{ + PQExpBufferData catalog_query; + PQExpBufferData buf; + PGresult *res; + SimpleStringList *tables; + int ntups, + i; + + initPQExpBuffer(&catalog_query); + + /* + * The queries here are using a safe search_path, so there's no need to + * fully qualify everything. + */ + if (type =3D=3D REINDEX_DATABASE) + { + Assert(user_list =3D=3D NULL); + + appendPQExpBuffer(&catalog_query, + "SELECT c.relname, ns.nspname\n" + " FROM pg_catalog.pg_class c\n" + " JOIN pg_catalog.pg_namespace ns" + " ON c.relnamespace =3D ns.oid\n" + " WHERE ns.nspname !=3D 'pg_catalog'\n" + " AND c.relkind IN (" + CppAsString2(RELKIND_RELATION) ", " + CppAsString2(RELKIND_MATVIEW) ")\n" + " ORDER BY c.relpages DESC;"); + } + else if (type =3D=3D REINDEX_SCHEMA) + { + SimpleStringListCell *cell; + bool nsp_listed =3D false; + + Assert(user_list !=3D NULL); + + /* + * All the tables from all the listed schemas are grabbed at + * once. + */ + appendPQExpBuffer(&catalog_query, + "SELECT c.relname, ns.nspname\n" + " FROM pg_catalog.pg_class c\n" + " JOIN pg_catalog.pg_namespace ns" + " ON c.relnamespace =3D ns.oid\n" + " WHERE c.relkind IN (" + CppAsString2(RELKIND_RELATION) ", " + CppAsString2(RELKIND_MATVIEW) ")\n" + " AND ns.nspname IN ("); + + for (cell =3D user_list->head; cell; cell =3D cell->next) + { + const char *nspname =3D cell->val; + + if (nsp_listed) + appendPQExpBuffer(&catalog_query, ", "); + else + nsp_listed =3D true; + + appendStringLiteralConn(&catalog_query, nspname, conn); + + } + + appendPQExpBuffer(&catalog_query, ")\n" + " ORDER BY c.relpages DESC;"); + } + else + Assert(false); + + res =3D executeQuery(conn, catalog_query.data, echo); + termPQExpBuffer(&catalog_query); + + /* + * If no rows are returned, there are no matching tables, so we are done. + */ + ntups =3D PQntuples(res); + if (ntups =3D=3D 0) + { + PQclear(res); + PQfinish(conn); + return NULL; + } + + tables =3D pg_malloc0(sizeof(SimpleStringList)); + + /* Build qualified identifiers for each table */ + initPQExpBuffer(&buf); + for (i =3D 0; i < ntups; i++) + { + appendPQExpBufferStr(&buf, + fmtQualifiedId(PQgetvalue(res, i, 1), + PQgetvalue(res, i, 0))); + + simple_string_list_append(tables, buf.data); + resetPQExpBuffer(&buf); + } + termPQExpBuffer(&buf); + PQclear(res); + + return tables; +} + 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, bool verbose, - bool concurrently) + bool concurrently, int concurrentCons) { PGconn *conn; PGresult *result; @@ -423,9 +686,10 @@ reindex_all_databases(const char *maintenance_db, appendPQExpBufferStr(&connstr, "dbname=3D"); appendConnStrVal(&connstr, dbname); =20 - reindex_one_database(NULL, connstr.data, REINDEX_DATABASE, host, + reindex_one_database(connstr.data, REINDEX_DATABASE, NULL, host, port, username, prompt_password, - progname, echo, verbose, concurrently); + progname, echo, verbose, concurrently, + concurrentCons); } termPQExpBuffer(&connstr); =20 @@ -444,6 +708,7 @@ help(const char *progname) printf(_(" -d, --dbname=3DDBNAME database to reindex\n")); printf(_(" -e, --echo show the commands being sent to the= server\n")); printf(_(" -i, --index=3DINDEX recreate specific index(es) only\= n")); + printf(_(" -j, --jobs=3DNUM use this many concurrent connecti= ons to reindex\n")); printf(_(" -q, --quiet don't write any messages\n")); printf(_(" -s, --system reindex system catalogs\n")); printf(_(" -S, --schema=3DSCHEMA reindex specific schema(s) only\n= ")); diff --git a/src/bin/scripts/t/090_reindexdb.pl b/src/bin/scripts/t/090_rei= ndexdb.pl index 1af8ab70ad..d058690892 100644 --- a/src/bin/scripts/t/090_reindexdb.pl +++ b/src/bin/scripts/t/090_reindexdb.pl @@ -3,7 +3,7 @@ use warnings; =20 use PostgresNode; use TestLib; -use Test::More tests =3D> 34; +use Test::More tests =3D> 39; =20 program_help_ok('reindexdb'); program_version_ok('reindexdb'); @@ -77,3 +77,27 @@ $node->command_ok( $node->command_ok( [qw(reindexdb --echo --system dbname=3Dtemplate1)], 'reindexdb system with connection string'); + +# parallel processing +$node->safe_psql( + 'postgres', q| + CREATE SCHEMA s1; + CREATE TABLE s1.t1(id integer); + CREATE INDEX ON s1.t1(id); + CREATE SCHEMA s2; + CREATE TABLE s2.t2(id integer); + CREATE INDEX ON s2.t2(id); +|); + +$node->command_fails( + [ 'reindexdb', '-j', '2', '-s', 'postgres' ], + 'reindexdb cannot process system catalogs in parallel'); +$node->issues_sql_like( + [ 'reindexdb', '-j', '2', 'postgres' ], + qr/statement:\ REINDEX SYSTEM postgres; +.*statement:\ REINDEX TABLE public\.test1/s, + 'global and parallel reindex issues REINDEX SYSTEM'); +$node->issues_sql_like( + [ 'reindexdb', '-j', '2', '-S', 's1', '-S', 's2', 'postgres' ], + qr/statement:\ REINDEX TABLE s1.t1;/, + 'Schema-specific and parallel reindex will issue per-table REINDEX'); --maH1Gajj2nflutpK-- --pyE8wggRBhVBcj8z Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAl02uVkACgkQnvQgOdby QH2h3A//Zbgq/yDy3fA6HhmwiFMQJ/JfLVzQRAza/kbsDdTDAoMR+wHucpxtsXf0 25o+DZ6WVp0lA+BX2cl0bQXVfhgBVwAJj2vt5QoajBD+Q5o7sAiTTrsD+n7QHW/3 4wQlU9dh6TKokkeq+PQU1d/t+TGdiRIghUMUclLXT+0uaVwE+v97z3i+KWxjxkio U/8xZRLA3CKFyGyHA6UWW8NeLmtVMzRHou3aefWRO7XSLIt+j1Ecfj/8Jc+JiYbD Zze6ulVHFlsKxMFWMhSQ38KrWDLn6Gzqws8WccGSBJNpxtmATyhFtFHC4bDilMt3 C/acHpiBU6G4BmBzHO1atHg27946AT0T8F7mxqOMm9dhd5Vi6kD1AYtQJKmDIWdp UPEIrNY92xeSUpRXL5cDUo3jw7M2VGbA1ohhXbKVI2vbbmVNc/9OypkLvu5lAumP JP0BBExIpt5YeIiP8gZp3aT2NXWapfQCLZLqHBMLULGhitjuzxKdowsc1fORIG+2 1kyEBNJ1fU8jGoex14k5h8lX6uReU75z0l66tNZTCVK/KVUDZPYaVuCH0z8hBR33 0NApy1wPdNubVBUjrvRRh2f6B2Ova11ZPwNaJzZkYgdpdVX7ggJIcAtSg16gbZAE h1JtQEw80MedpLWNvM+/DRIj3txzvz/vIORE3HfrDwRRr/gKOCw= =83JB -----END PGP SIGNATURE----- --pyE8wggRBhVBcj8z--