TLA Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * reindexdb
4 : *
5 : * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
6 : *
7 : * src/bin/scripts/reindexdb.c
8 : *
9 : *-------------------------------------------------------------------------
10 : */
11 :
12 : #include "postgres_fe.h"
13 :
14 : #include <limits.h>
15 :
16 : #include "catalog/pg_class_d.h"
17 : #include "common.h"
18 : #include "common/connect.h"
19 : #include "common/logging.h"
20 : #include "fe_utils/cancel.h"
21 : #include "fe_utils/option_utils.h"
22 : #include "fe_utils/parallel_slot.h"
23 : #include "fe_utils/query_utils.h"
24 : #include "fe_utils/simple_list.h"
25 : #include "fe_utils/string_utils.h"
26 :
27 : typedef enum ReindexType
28 : {
29 : REINDEX_DATABASE,
30 : REINDEX_INDEX,
31 : REINDEX_SCHEMA,
32 : REINDEX_SYSTEM,
33 : REINDEX_TABLE
34 : } ReindexType;
35 :
36 :
37 : static SimpleStringList *get_parallel_object_list(PGconn *conn,
38 : ReindexType type,
39 : SimpleStringList *user_list,
40 : bool echo);
41 : static void reindex_one_database(ConnParams *cparams, ReindexType type,
42 : SimpleStringList *user_list,
43 : const char *progname,
44 : bool echo, bool verbose, bool concurrently,
45 : int concurrentCons, const char *tablespace);
46 : static void reindex_all_databases(ConnParams *cparams,
47 : const char *progname, bool echo,
48 : bool quiet, bool verbose, bool concurrently,
49 : int concurrentCons, const char *tablespace);
50 : static void run_reindex_command(PGconn *conn, ReindexType type,
51 : const char *name, bool echo, bool verbose,
52 : bool concurrently, bool async,
53 : const char *tablespace);
54 :
55 : static void help(const char *progname);
56 :
57 : int
58 CBC 32 : main(int argc, char *argv[])
59 : {
60 : static struct option long_options[] = {
61 : {"host", required_argument, NULL, 'h'},
62 : {"port", required_argument, NULL, 'p'},
63 : {"username", required_argument, NULL, 'U'},
64 : {"no-password", no_argument, NULL, 'w'},
65 : {"password", no_argument, NULL, 'W'},
66 : {"echo", no_argument, NULL, 'e'},
67 : {"quiet", no_argument, NULL, 'q'},
68 : {"schema", required_argument, NULL, 'S'},
69 : {"dbname", required_argument, NULL, 'd'},
70 : {"all", no_argument, NULL, 'a'},
71 : {"system", no_argument, NULL, 's'},
72 : {"table", required_argument, NULL, 't'},
73 : {"index", required_argument, NULL, 'i'},
74 : {"jobs", required_argument, NULL, 'j'},
75 : {"verbose", no_argument, NULL, 'v'},
76 : {"concurrently", no_argument, NULL, 1},
77 : {"maintenance-db", required_argument, NULL, 2},
78 : {"tablespace", required_argument, NULL, 3},
79 : {NULL, 0, NULL, 0}
80 : };
81 :
82 : const char *progname;
83 : int optindex;
84 : int c;
85 :
86 32 : const char *dbname = NULL;
87 32 : const char *maintenance_db = NULL;
88 32 : const char *host = NULL;
89 32 : const char *port = NULL;
90 32 : const char *username = NULL;
91 32 : const char *tablespace = NULL;
92 32 : enum trivalue prompt_password = TRI_DEFAULT;
93 : ConnParams cparams;
94 32 : bool syscatalog = false;
95 32 : bool alldb = false;
96 32 : bool echo = false;
97 32 : bool quiet = false;
98 32 : bool verbose = false;
99 32 : bool concurrently = false;
100 32 : SimpleStringList indexes = {NULL, NULL};
101 32 : SimpleStringList tables = {NULL, NULL};
102 32 : SimpleStringList schemas = {NULL, NULL};
103 32 : int concurrentCons = 1;
104 :
105 32 : pg_logging_init(argv[0]);
106 32 : progname = get_progname(argv[0]);
107 32 : set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
108 :
109 32 : handle_help_version_opts(argc, argv, "reindexdb", help);
110 :
111 : /* process command-line options */
112 GNC 87 : while ((c = getopt_long(argc, argv, "ad:eh:i:j:qp:sS:t:U:vwW", long_options, &optindex)) != -1)
113 : {
114 CBC 58 : switch (c)
115 : {
116 GNC 2 : case 'a':
117 2 : alldb = true;
118 GBC 2 : break;
119 1 : case 'd':
120 1 : dbname = pg_strdup(optarg);
121 CBC 1 : break;
122 GNC 4 : case 'e':
123 4 : echo = true;
124 CBC 4 : break;
125 UNC 0 : case 'h':
126 0 : host = pg_strdup(optarg);
127 UBC 0 : break;
128 GBC 5 : case 'i':
129 5 : simple_string_list_append(&indexes, optarg);
130 CBC 5 : break;
131 5 : case 'j':
132 5 : if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
133 EUB : &concurrentCons))
134 UBC 0 : exit(1);
135 GBC 5 : break;
136 UNC 0 : case 'q':
137 0 : quiet = true;
138 0 : break;
139 0 : case 'p':
140 0 : port = pg_strdup(optarg);
141 0 : break;
142 GNC 4 : case 's':
143 4 : syscatalog = true;
144 4 : break;
145 5 : case 'S':
146 5 : simple_string_list_append(&schemas, optarg);
147 5 : break;
148 10 : case 't':
149 10 : simple_string_list_append(&tables, optarg);
150 10 : break;
151 UNC 0 : case 'U':
152 0 : username = pg_strdup(optarg);
153 0 : break;
154 GBC 4 : case 'v':
155 4 : verbose = true;
156 4 : break;
157 UNC 0 : case 'w':
158 0 : prompt_password = TRI_NO;
159 0 : break;
160 0 : case 'W':
161 0 : prompt_password = TRI_YES;
162 0 : break;
163 CBC 10 : case 1:
164 10 : concurrently = true;
165 10 : break;
166 UBC 0 : case 2:
167 0 : maintenance_db = pg_strdup(optarg);
168 0 : break;
169 CBC 7 : case 3:
170 7 : tablespace = pg_strdup(optarg);
171 7 : break;
172 1 : default:
173 : /* getopt_long already emitted a complaint */
174 1 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
175 1 : exit(1);
176 : }
177 : }
178 :
179 : /*
180 : * Non-option argument specifies database name as long as it wasn't
181 : * already specified with -d / --dbname
182 : */
183 29 : if (optind < argc && dbname == NULL)
184 : {
185 25 : dbname = argv[optind];
186 25 : optind++;
187 : }
188 :
189 29 : if (optind < argc)
190 : {
191 UBC 0 : pg_log_error("too many command-line arguments (first is \"%s\")",
192 : argv[optind]);
193 0 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
194 0 : exit(1);
195 : }
196 :
197 : /* fill cparams except for dbname, which is set below */
198 CBC 29 : cparams.pghost = host;
199 29 : cparams.pgport = port;
200 29 : cparams.pguser = username;
201 29 : cparams.prompt_password = prompt_password;
202 29 : cparams.override_dbname = NULL;
203 :
204 29 : setup_cancel_handler(NULL);
205 :
206 29 : if (alldb)
207 : {
208 2 : if (dbname)
209 UBC 0 : pg_fatal("cannot reindex all databases and a specific one at the same time");
210 CBC 2 : if (syscatalog)
211 UBC 0 : pg_fatal("cannot reindex all databases and system catalogs at the same time");
212 CBC 2 : if (schemas.head != NULL)
213 UBC 0 : pg_fatal("cannot reindex specific schema(s) in all databases");
214 CBC 2 : if (tables.head != NULL)
215 UBC 0 : pg_fatal("cannot reindex specific table(s) in all databases");
216 CBC 2 : if (indexes.head != NULL)
217 UBC 0 : pg_fatal("cannot reindex specific index(es) in all databases");
218 :
219 CBC 2 : cparams.dbname = maintenance_db;
220 :
221 2 : reindex_all_databases(&cparams, progname, echo, quiet, verbose,
222 : concurrently, concurrentCons, tablespace);
223 : }
224 27 : else if (syscatalog)
225 : {
226 4 : if (schemas.head != NULL)
227 UBC 0 : pg_fatal("cannot reindex specific schema(s) and system catalogs at the same time");
228 CBC 4 : if (tables.head != NULL)
229 UBC 0 : pg_fatal("cannot reindex specific table(s) and system catalogs at the same time");
230 CBC 4 : if (indexes.head != NULL)
231 UBC 0 : pg_fatal("cannot reindex specific index(es) and system catalogs at the same time");
232 :
233 CBC 4 : if (concurrentCons > 1)
234 1 : pg_fatal("cannot use multiple jobs to reindex system catalogs");
235 :
236 3 : if (dbname == NULL)
237 : {
238 UBC 0 : if (getenv("PGDATABASE"))
239 0 : dbname = getenv("PGDATABASE");
240 0 : else if (getenv("PGUSER"))
241 0 : dbname = getenv("PGUSER");
242 : else
243 0 : dbname = get_user_name_or_exit(progname);
244 : }
245 :
246 CBC 3 : cparams.dbname = dbname;
247 :
248 3 : reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
249 : progname, echo, verbose,
250 : concurrently, 1, tablespace);
251 : }
252 : else
253 : {
254 : /*
255 : * Index-level REINDEX is not supported with multiple jobs as we
256 : * cannot control the concurrent processing of multiple indexes
257 : * depending on the same relation.
258 : */
259 23 : if (concurrentCons > 1 && indexes.head != NULL)
260 1 : pg_fatal("cannot use multiple jobs to reindex indexes");
261 :
262 22 : if (dbname == NULL)
263 : {
264 1 : if (getenv("PGDATABASE"))
265 1 : dbname = getenv("PGDATABASE");
266 UBC 0 : else if (getenv("PGUSER"))
267 0 : dbname = getenv("PGUSER");
268 : else
269 0 : dbname = get_user_name_or_exit(progname);
270 : }
271 :
272 CBC 22 : cparams.dbname = dbname;
273 :
274 22 : if (schemas.head != NULL)
275 4 : reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
276 : progname, echo, verbose,
277 : concurrently, concurrentCons, tablespace);
278 :
279 22 : if (indexes.head != NULL)
280 4 : reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
281 : progname, echo, verbose,
282 : concurrently, 1, tablespace);
283 :
284 20 : if (tables.head != NULL)
285 10 : reindex_one_database(&cparams, REINDEX_TABLE, &tables,
286 : progname, echo, verbose,
287 : concurrently, concurrentCons, tablespace);
288 :
289 : /*
290 : * reindex database only if neither index nor table nor schema is
291 : * specified
292 : */
293 18 : if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
294 4 : reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
295 : progname, echo, verbose,
296 : concurrently, concurrentCons, tablespace);
297 : }
298 :
299 22 : exit(0);
300 : }
301 :
302 : static void
303 34 : reindex_one_database(ConnParams *cparams, ReindexType type,
304 : SimpleStringList *user_list,
305 : const char *progname, bool echo,
306 : bool verbose, bool concurrently, int concurrentCons,
307 : const char *tablespace)
308 : {
309 : PGconn *conn;
310 : SimpleStringListCell *cell;
311 34 : bool parallel = concurrentCons > 1;
312 34 : SimpleStringList *process_list = user_list;
313 34 : ReindexType process_type = type;
314 : ParallelSlotArray *sa;
315 34 : bool failed = false;
316 34 : int items_count = 0;
317 :
318 34 : conn = connectDatabase(cparams, progname, echo, false, false);
319 :
320 34 : if (concurrently && PQserverVersion(conn) < 120000)
321 : {
322 UBC 0 : PQfinish(conn);
323 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
324 : "concurrently", "12");
325 : }
326 :
327 CBC 34 : if (tablespace && PQserverVersion(conn) < 140000)
328 : {
329 UBC 0 : PQfinish(conn);
330 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
331 : "tablespace", "14");
332 : }
333 :
334 CBC 34 : if (!parallel)
335 : {
336 31 : switch (process_type)
337 : {
338 15 : case REINDEX_DATABASE:
339 : case REINDEX_SYSTEM:
340 :
341 : /*
342 : * Database and system reindexes only need to work on the
343 : * database itself, so build a list with a single entry.
344 : */
345 15 : Assert(user_list == NULL);
346 15 : process_list = pg_malloc0(sizeof(SimpleStringList));
347 15 : simple_string_list_append(process_list, PQdb(conn));
348 15 : break;
349 :
350 16 : case REINDEX_INDEX:
351 : case REINDEX_SCHEMA:
352 : case REINDEX_TABLE:
353 16 : Assert(user_list != NULL);
354 16 : break;
355 : }
356 : }
357 : else
358 : {
359 3 : switch (process_type)
360 : {
361 1 : case REINDEX_DATABASE:
362 :
363 : /* Build a list of relations from the database */
364 GIC 1 : process_list = get_parallel_object_list(conn, process_type,
365 ECB : user_list, echo);
366 GIC 1 : process_type = REINDEX_TABLE;
367 ECB :
368 : /* Bail out if nothing to process */
369 GIC 1 : if (process_list == NULL)
370 LBC 0 : return;
371 CBC 1 : break;
372 ECB :
373 GIC 2 : case REINDEX_SCHEMA:
374 GBC 2 : Assert(user_list != NULL);
375 :
376 : /* Build a list of relations from all the schemas */
377 2 : process_list = get_parallel_object_list(conn, process_type,
378 : user_list, echo);
379 GIC 2 : process_type = REINDEX_TABLE;
380 EUB :
381 : /* Bail out if nothing to process */
382 GIC 2 : if (process_list == NULL)
383 1 : return;
384 1 : break;
385 :
386 UBC 0 : case REINDEX_SYSTEM:
387 : case REINDEX_INDEX:
388 : /* not supported */
389 UIC 0 : Assert(false);
390 : break;
391 :
392 0 : case REINDEX_TABLE:
393 :
394 : /*
395 ECB : * Fall through. The list of items for tables is already
396 : * created.
397 : */
398 UIC 0 : break;
399 : }
400 ECB : }
401 :
402 : /*
403 : * Adjust the number of concurrent connections depending on the items in
404 : * the list. We choose the minimum between the number of concurrent
405 : * connections and the number of items in the list.
406 : */
407 GIC 35 : for (cell = process_list->head; cell; cell = cell->next)
408 ECB : {
409 CBC 35 : items_count++;
410 :
411 ECB : /* no need to continue if there are more elements than jobs */
412 GIC 35 : if (items_count >= concurrentCons)
413 33 : break;
414 ECB : }
415 CBC 33 : concurrentCons = Min(concurrentCons, items_count);
416 GIC 33 : Assert(concurrentCons > 0);
417 ECB :
418 GIC 33 : Assert(process_list != NULL);
419 EUB :
420 GBC 33 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, NULL);
421 GIC 33 : ParallelSlotsAdoptConn(sa, conn);
422 :
423 CBC 33 : cell = process_list->head;
424 ECB : do
425 : {
426 GBC 41 : const char *objname = cell->val;
427 41 : ParallelSlot *free_slot = NULL;
428 :
429 GIC 41 : if (CancelRequested)
430 ECB : {
431 LBC 0 : failed = true;
432 UIC 0 : goto finish;
433 : }
434 ECB :
435 CBC 41 : free_slot = ParallelSlotsGetIdle(sa, NULL);
436 GIC 41 : if (!free_slot)
437 ECB : {
438 LBC 0 : failed = true;
439 UIC 0 : goto finish;
440 ECB : }
441 :
442 GIC 41 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
443 CBC 41 : run_reindex_command(free_slot->connection, process_type, objname,
444 ECB : echo, verbose, concurrently, true, tablespace);
445 :
446 GIC 41 : cell = cell->next;
447 CBC 41 : } while (cell != NULL);
448 ECB :
449 GIC 33 : if (!ParallelSlotsWaitCompletion(sa))
450 CBC 5 : failed = true;
451 ECB :
452 GIC 28 : finish:
453 33 : if (process_list != user_list)
454 : {
455 CBC 17 : simple_string_list_destroy(process_list);
456 GIC 17 : pg_free(process_list);
457 : }
458 :
459 CBC 33 : ParallelSlotsTerminate(sa);
460 33 : pfree(sa);
461 ECB :
462 GIC 33 : if (failed)
463 5 : exit(1);
464 : }
465 ECB :
466 : static void
467 GIC 41 : run_reindex_command(PGconn *conn, ReindexType type, const char *name,
468 ECB : bool echo, bool verbose, bool concurrently, bool async,
469 : const char *tablespace)
470 : {
471 GIC 41 : const char *paren = "(";
472 CBC 41 : const char *comma = ", ";
473 GIC 41 : const char *sep = paren;
474 ECB : PQExpBufferData sql;
475 : bool status;
476 :
477 GIC 41 : Assert(name);
478 ECB :
479 : /* build the REINDEX query */
480 CBC 41 : initPQExpBuffer(&sql);
481 ECB :
482 GIC 41 : appendPQExpBufferStr(&sql, "REINDEX ");
483 :
484 CBC 41 : if (verbose)
485 ECB : {
486 GIC 4 : appendPQExpBuffer(&sql, "%sVERBOSE", sep);
487 4 : sep = comma;
488 ECB : }
489 :
490 CBC 41 : if (tablespace)
491 ECB : {
492 CBC 7 : appendPQExpBuffer(&sql, "%sTABLESPACE %s", sep, fmtId(tablespace));
493 7 : sep = comma;
494 ECB : }
495 :
496 CBC 41 : if (sep != paren)
497 9 : appendPQExpBufferStr(&sql, ") ");
498 ECB :
499 : /* object type */
500 CBC 41 : switch (type)
501 ECB : {
502 CBC 12 : case REINDEX_DATABASE:
503 12 : appendPQExpBufferStr(&sql, "DATABASE ");
504 12 : break;
505 GIC 4 : case REINDEX_INDEX:
506 4 : appendPQExpBufferStr(&sql, "INDEX ");
507 4 : break;
508 2 : case REINDEX_SCHEMA:
509 2 : appendPQExpBufferStr(&sql, "SCHEMA ");
510 2 : break;
511 3 : case REINDEX_SYSTEM:
512 CBC 3 : appendPQExpBufferStr(&sql, "SYSTEM ");
513 3 : break;
514 GIC 20 : case REINDEX_TABLE:
515 20 : appendPQExpBufferStr(&sql, "TABLE ");
516 CBC 20 : break;
517 : }
518 ECB :
519 : /*
520 : * Parenthesized grammar is only supported for CONCURRENTLY since
521 : * PostgreSQL 14. Since 12, CONCURRENTLY can be specified after the
522 : * object type.
523 : */
524 CBC 41 : if (concurrently)
525 17 : appendPQExpBufferStr(&sql, "CONCURRENTLY ");
526 ECB :
527 : /* object name */
528 CBC 41 : switch (type)
529 : {
530 GIC 15 : case REINDEX_DATABASE:
531 : case REINDEX_SYSTEM:
532 CBC 15 : appendPQExpBufferStr(&sql, fmtId(name));
533 GIC 15 : break;
534 CBC 24 : case REINDEX_INDEX:
535 : case REINDEX_TABLE:
536 24 : appendQualifiedRelation(&sql, name, conn, echo);
537 24 : break;
538 GIC 2 : case REINDEX_SCHEMA:
539 CBC 2 : appendPQExpBufferStr(&sql, name);
540 GIC 2 : break;
541 : }
542 EUB :
543 : /* finish the query */
544 CBC 41 : appendPQExpBufferChar(&sql, ';');
545 :
546 GBC 41 : if (async)
547 : {
548 41 : if (echo)
549 10 : printf("%s\n", sql.data);
550 :
551 41 : status = PQsendQuery(conn, sql.data) == 1;
552 EUB : }
553 : else
554 UIC 0 : status = executeMaintenanceCommand(conn, sql.data, echo);
555 EUB :
556 GBC 41 : if (!status)
557 EUB : {
558 UIC 0 : switch (type)
559 EUB : {
560 UBC 0 : case REINDEX_DATABASE:
561 0 : pg_log_error("reindexing of database \"%s\" failed: %s",
562 : PQdb(conn), PQerrorMessage(conn));
563 0 : break;
564 0 : case REINDEX_INDEX:
565 0 : pg_log_error("reindexing of index \"%s\" in database \"%s\" failed: %s",
566 : name, PQdb(conn), PQerrorMessage(conn));
567 0 : break;
568 UIC 0 : case REINDEX_SCHEMA:
569 UBC 0 : pg_log_error("reindexing of schema \"%s\" in database \"%s\" failed: %s",
570 : name, PQdb(conn), PQerrorMessage(conn));
571 0 : break;
572 0 : case REINDEX_SYSTEM:
573 UIC 0 : pg_log_error("reindexing of system catalogs in database \"%s\" failed: %s",
574 : PQdb(conn), PQerrorMessage(conn));
575 0 : break;
576 LBC 0 : case REINDEX_TABLE:
577 0 : pg_log_error("reindexing of table \"%s\" in database \"%s\" failed: %s",
578 : name, PQdb(conn), PQerrorMessage(conn));
579 UIC 0 : break;
580 : }
581 0 : if (!async)
582 : {
583 0 : PQfinish(conn);
584 0 : exit(1);
585 : }
586 : }
587 :
588 CBC 41 : termPQExpBuffer(&sql);
589 GIC 41 : }
590 :
591 : /*
592 : * Prepare the list of objects to process by querying the catalogs.
593 : *
594 : * This function will return a SimpleStringList object containing the entire
595 : * list of tables in the given database that should be processed by a parallel
596 : * database-wide reindex (excluding system tables), or NULL if there's no such
597 : * table.
598 ECB : */
599 : static SimpleStringList *
600 GIC 3 : get_parallel_object_list(PGconn *conn, ReindexType type,
601 : SimpleStringList *user_list, bool echo)
602 : {
603 : PQExpBufferData catalog_query;
604 ECB : PQExpBufferData buf;
605 : PGresult *res;
606 : SimpleStringList *tables;
607 : int ntups,
608 : i;
609 :
610 GIC 3 : initPQExpBuffer(&catalog_query);
611 :
612 : /*
613 : * The queries here are using a safe search_path, so there's no need to
614 : * fully qualify everything.
615 : */
616 3 : switch (type)
617 : {
618 CBC 1 : case REINDEX_DATABASE:
619 GIC 1 : Assert(user_list == NULL);
620 CBC 1 : appendPQExpBufferStr(&catalog_query,
621 : "SELECT c.relname, ns.nspname\n"
622 : " FROM pg_catalog.pg_class c\n"
623 ECB : " JOIN pg_catalog.pg_namespace ns"
624 : " ON c.relnamespace = ns.oid\n"
625 : " WHERE ns.nspname != 'pg_catalog'\n"
626 : " AND c.relkind IN ("
627 : CppAsString2(RELKIND_RELATION) ", "
628 : CppAsString2(RELKIND_MATVIEW) ")\n"
629 : " ORDER BY c.relpages DESC;");
630 GIC 1 : break;
631 ECB :
632 GIC 2 : case REINDEX_SCHEMA:
633 : {
634 : SimpleStringListCell *cell;
635 2 : bool nsp_listed = false;
636 :
637 2 : Assert(user_list != NULL);
638 :
639 : /*
640 : * All the tables from all the listed schemas are grabbed at
641 ECB : * once.
642 : */
643 CBC 2 : appendPQExpBufferStr(&catalog_query,
644 : "SELECT c.relname, ns.nspname\n"
645 ECB : " FROM pg_catalog.pg_class c\n"
646 : " JOIN pg_catalog.pg_namespace ns"
647 : " ON c.relnamespace = ns.oid\n"
648 : " WHERE c.relkind IN ("
649 : CppAsString2(RELKIND_RELATION) ", "
650 : CppAsString2(RELKIND_MATVIEW) ")\n"
651 : " AND ns.nspname IN (");
652 :
653 CBC 5 : for (cell = user_list->head; cell; cell = cell->next)
654 : {
655 GIC 3 : const char *nspname = cell->val;
656 ECB :
657 GIC 3 : if (nsp_listed)
658 GBC 1 : appendPQExpBufferStr(&catalog_query, ", ");
659 : else
660 GIC 2 : nsp_listed = true;
661 EUB :
662 GIC 3 : appendStringLiteralConn(&catalog_query, nspname, conn);
663 : }
664 :
665 CBC 2 : appendPQExpBufferStr(&catalog_query, ")\n"
666 ECB : " ORDER BY c.relpages DESC;");
667 : }
668 GIC 2 : break;
669 :
670 UIC 0 : case REINDEX_SYSTEM:
671 ECB : case REINDEX_INDEX:
672 : case REINDEX_TABLE:
673 UIC 0 : Assert(false);
674 ECB : break;
675 : }
676 :
677 GIC 3 : res = executeQuery(conn, catalog_query.data, echo);
678 3 : termPQExpBuffer(&catalog_query);
679 ECB :
680 : /*
681 : * If no rows are returned, there are no matching tables, so we are done.
682 : */
683 CBC 3 : ntups = PQntuples(res);
684 GIC 3 : if (ntups == 0)
685 ECB : {
686 CBC 1 : PQclear(res);
687 1 : PQfinish(conn);
688 GIC 1 : return NULL;
689 ECB : }
690 :
691 GIC 2 : tables = pg_malloc0(sizeof(SimpleStringList));
692 ECB :
693 : /* Build qualified identifiers for each table */
694 GIC 2 : initPQExpBuffer(&buf);
695 CBC 12 : for (i = 0; i < ntups; i++)
696 : {
697 GIC 10 : appendPQExpBufferStr(&buf,
698 10 : fmtQualifiedId(PQgetvalue(res, i, 1),
699 CBC 10 : PQgetvalue(res, i, 0)));
700 :
701 GIC 10 : simple_string_list_append(tables, buf.data);
702 10 : resetPQExpBuffer(&buf);
703 : }
704 2 : termPQExpBuffer(&buf);
705 2 : PQclear(res);
706 :
707 2 : return tables;
708 ECB : }
709 :
710 : static void
711 GIC 2 : reindex_all_databases(ConnParams *cparams,
712 ECB : const char *progname, bool echo, bool quiet, bool verbose,
713 : bool concurrently, int concurrentCons,
714 : const char *tablespace)
715 : {
716 : PGconn *conn;
717 : PGresult *result;
718 : int i;
719 :
720 GIC 2 : conn = connectMaintenanceDatabase(cparams, progname, echo);
721 2 : result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", echo);
722 CBC 2 : PQfinish(conn);
723 :
724 11 : for (i = 0; i < PQntuples(result); i++)
725 : {
726 GIC 9 : char *dbname = PQgetvalue(result, i, 0);
727 :
728 9 : if (!quiet)
729 ECB : {
730 CBC 9 : printf(_("%s: reindexing database \"%s\"\n"), progname, dbname);
731 GIC 9 : fflush(stdout);
732 : }
733 ECB :
734 GIC 9 : cparams->override_dbname = dbname;
735 ECB :
736 CBC 9 : reindex_one_database(cparams, REINDEX_DATABASE, NULL,
737 ECB : progname, echo, verbose, concurrently,
738 : concurrentCons, tablespace);
739 : }
740 :
741 CBC 2 : PQclear(result);
742 2 : }
743 ECB :
744 : static void
745 CBC 1 : help(const char *progname)
746 ECB : {
747 CBC 1 : printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
748 1 : printf(_("Usage:\n"));
749 1 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
750 1 : printf(_("\nOptions:\n"));
751 1 : printf(_(" -a, --all reindex all databases\n"));
752 1 : printf(_(" --concurrently reindex concurrently\n"));
753 1 : printf(_(" -d, --dbname=DBNAME database to reindex\n"));
754 1 : printf(_(" -e, --echo show the commands being sent to the server\n"));
755 1 : printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
756 1 : printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
757 1 : printf(_(" -q, --quiet don't write any messages\n"));
758 1 : printf(_(" -s, --system reindex system catalogs only\n"));
759 1 : printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
760 1 : printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
761 1 : printf(_(" --tablespace=TABLESPACE tablespace where indexes are rebuilt\n"));
762 1 : printf(_(" -v, --verbose write a lot of output\n"));
763 1 : printf(_(" -V, --version output version information, then exit\n"));
764 GIC 1 : printf(_(" -?, --help show this help, then exit\n"));
765 1 : printf(_("\nConnection options:\n"));
766 1 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
767 1 : printf(_(" -p, --port=PORT database server port\n"));
768 1 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
769 1 : printf(_(" -w, --no-password never prompt for password\n"));
770 1 : printf(_(" -W, --password force password prompt\n"));
771 1 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
772 1 : printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
773 1 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
774 1 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
775 1 : }
|
