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