Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * reindexdb
4 : *
5 : * Portions Copyright (c) 1996-2025, 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 *sq);
64 :
65 : static void help(const char *progname);
66 :
67 : int
68 82 : 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 82 : const char *dbname = NULL;
97 82 : const char *maintenance_db = NULL;
98 82 : const char *host = NULL;
99 82 : const char *port = NULL;
100 82 : const char *username = NULL;
101 82 : const char *tablespace = NULL;
102 82 : enum trivalue prompt_password = TRI_DEFAULT;
103 : ConnParams cparams;
104 82 : bool syscatalog = false;
105 82 : bool alldb = false;
106 82 : bool echo = false;
107 82 : bool quiet = false;
108 82 : bool verbose = false;
109 82 : bool concurrently = false;
110 82 : SimpleStringList indexes = {NULL, NULL};
111 82 : SimpleStringList tables = {NULL, NULL};
112 82 : SimpleStringList schemas = {NULL, NULL};
113 82 : int concurrentCons = 1;
114 :
115 82 : pg_logging_init(argv[0]);
116 82 : progname = get_progname(argv[0]);
117 82 : set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
118 :
119 82 : handle_help_version_opts(argc, argv, "reindexdb", help);
120 :
121 : /* process command-line options */
122 230 : while ((c = getopt_long(argc, argv, "ad:eh:i:j:qp:sS:t:U:vwW", long_options, &optindex)) != -1)
123 : {
124 154 : switch (c)
125 : {
126 14 : case 'a':
127 14 : alldb = true;
128 14 : break;
129 4 : case 'd':
130 4 : dbname = pg_strdup(optarg);
131 4 : break;
132 8 : case 'e':
133 8 : echo = true;
134 8 : break;
135 0 : case 'h':
136 0 : host = pg_strdup(optarg);
137 0 : break;
138 20 : case 'i':
139 20 : simple_string_list_append(&indexes, optarg);
140 20 : break;
141 10 : case 'j':
142 10 : if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
143 : &concurrentCons))
144 0 : exit(1);
145 10 : 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 16 : case 's':
153 16 : syscatalog = true;
154 16 : break;
155 14 : case 'S':
156 14 : simple_string_list_append(&schemas, optarg);
157 14 : break;
158 24 : case 't':
159 24 : simple_string_list_append(&tables, optarg);
160 24 : break;
161 0 : case 'U':
162 0 : username = pg_strdup(optarg);
163 0 : break;
164 8 : case 'v':
165 8 : verbose = true;
166 8 : 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 20 : case 1:
174 20 : concurrently = true;
175 20 : break;
176 0 : case 2:
177 0 : maintenance_db = pg_strdup(optarg);
178 0 : break;
179 14 : case 3:
180 14 : tablespace = pg_strdup(optarg);
181 14 : break;
182 2 : default:
183 : /* getopt_long already emitted a complaint */
184 2 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
185 2 : 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 76 : if (optind < argc && dbname == NULL)
194 : {
195 56 : dbname = argv[optind];
196 56 : optind++;
197 : }
198 :
199 76 : 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 76 : cparams.pghost = host;
209 76 : cparams.pgport = port;
210 76 : cparams.pguser = username;
211 76 : cparams.prompt_password = prompt_password;
212 76 : cparams.override_dbname = NULL;
213 :
214 76 : setup_cancel_handler(NULL);
215 :
216 76 : if (concurrentCons > 1 && syscatalog)
217 2 : pg_fatal("cannot use multiple jobs to reindex system catalogs");
218 :
219 74 : if (alldb)
220 : {
221 14 : if (dbname)
222 0 : pg_fatal("cannot reindex all databases and a specific one at the same time");
223 :
224 14 : cparams.dbname = maintenance_db;
225 :
226 14 : reindex_all_databases(&cparams, progname, echo, quiet, verbose,
227 : concurrently, concurrentCons, tablespace,
228 : syscatalog, &schemas, &tables, &indexes);
229 : }
230 : else
231 : {
232 60 : if (dbname == NULL)
233 : {
234 2 : if (getenv("PGDATABASE"))
235 2 : 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 60 : cparams.dbname = dbname;
243 :
244 60 : if (syscatalog)
245 12 : reindex_one_database(&cparams, REINDEX_SYSTEM, NULL,
246 : progname, echo, verbose,
247 : concurrently, 1, tablespace);
248 :
249 58 : if (schemas.head != NULL)
250 10 : reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
251 : progname, echo, verbose,
252 : concurrently, concurrentCons, tablespace);
253 :
254 58 : if (indexes.head != NULL)
255 12 : reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
256 : progname, echo, verbose,
257 : concurrently, concurrentCons, tablespace);
258 :
259 54 : if (tables.head != NULL)
260 22 : 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 50 : if (!syscatalog && indexes.head == NULL &&
269 34 : tables.head == NULL && schemas.head == NULL)
270 10 : reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
271 : progname, echo, verbose,
272 : concurrently, concurrentCons, tablespace);
273 : }
274 :
275 62 : exit(0);
276 : }
277 :
278 : static void
279 104 : 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 104 : SimpleOidListCell *indices_tables_cell = NULL;
288 104 : bool parallel = concurrentCons > 1;
289 104 : SimpleStringList *process_list = NULL;
290 104 : SimpleOidList *tableoid_list = NULL;
291 104 : ReindexType process_type = type;
292 : ParallelSlotArray *sa;
293 104 : bool failed = false;
294 104 : int items_count = 0;
295 104 : ParallelSlot *free_slot = NULL;
296 :
297 104 : conn = connectDatabase(cparams, progname, echo, false, true);
298 :
299 102 : 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 102 : 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 102 : if (!parallel)
314 : {
315 94 : switch (process_type)
316 : {
317 44 : 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 44 : process_list = pg_malloc0(sizeof(SimpleStringList));
326 44 : simple_string_list_append(process_list, PQdb(conn));
327 44 : break;
328 :
329 50 : case REINDEX_INDEX:
330 : case REINDEX_SCHEMA:
331 : case REINDEX_TABLE:
332 50 : process_list = user_list;
333 : Assert(user_list != NULL);
334 50 : break;
335 : }
336 94 : }
337 : else
338 : {
339 8 : switch (process_type)
340 : {
341 6 : case REINDEX_SCHEMA:
342 : Assert(user_list != NULL);
343 : /* fall through */
344 :
345 : case REINDEX_DATABASE:
346 :
347 : /* Build a list of relations from the database */
348 6 : process_list = get_parallel_tables_list(conn, process_type,
349 : user_list, echo);
350 6 : process_type = REINDEX_TABLE;
351 :
352 : /* Bail out if nothing to process */
353 6 : if (process_list == NULL)
354 : {
355 2 : PQfinish(conn);
356 2 : return;
357 : }
358 4 : break;
359 :
360 2 : 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 2 : get_parallel_tabidx_list(conn, user_list, &tableoid_list,
368 : echo);
369 :
370 : /* Bail out if nothing to process */
371 2 : if (tableoid_list == NULL)
372 : {
373 0 : PQfinish(conn);
374 0 : return;
375 : }
376 :
377 2 : indices_tables_cell = tableoid_list->head;
378 2 : process_list = user_list;
379 2 : 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 100 : }
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 100 : items_count = 0;
399 106 : for (cell = process_list->head; cell; cell = cell->next)
400 : {
401 106 : items_count++;
402 :
403 : /* no need to continue if there are more elements than jobs */
404 106 : if (items_count >= concurrentCons)
405 100 : break;
406 : }
407 100 : concurrentCons = Min(concurrentCons, items_count);
408 : Assert(concurrentCons > 0);
409 :
410 : Assert(process_list != NULL);
411 :
412 100 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, NULL);
413 100 : ParallelSlotsAdoptConn(sa, conn);
414 100 : conn = NULL;
415 :
416 100 : cell = process_list->head;
417 : do
418 : {
419 : PQExpBufferData sql;
420 118 : const char *objname = cell->val;
421 :
422 118 : if (CancelRequested)
423 : {
424 0 : failed = true;
425 0 : goto finish;
426 : }
427 :
428 118 : free_slot = ParallelSlotsGetIdle(sa, NULL);
429 118 : if (!free_slot)
430 : {
431 0 : failed = true;
432 0 : goto finish;
433 : }
434 :
435 118 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
436 118 : initPQExpBuffer(&sql);
437 118 : 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 4 : gen_reindex_command(free_slot->connection, process_type, objname,
446 : echo, verbose, concurrently, tablespace, &sql);
447 8 : while (indices_tables_cell->next &&
448 6 : indices_tables_cell->val == indices_tables_cell->next->val)
449 : {
450 4 : indices_tables_cell = indices_tables_cell->next;
451 4 : cell = cell->next;
452 4 : objname = cell->val;
453 4 : appendPQExpBufferChar(&sql, '\n');
454 4 : gen_reindex_command(free_slot->connection, process_type, objname,
455 : echo, verbose, concurrently, tablespace, &sql);
456 : }
457 4 : indices_tables_cell = indices_tables_cell->next;
458 : }
459 : else
460 : {
461 114 : gen_reindex_command(free_slot->connection, process_type, objname,
462 : echo, verbose, concurrently, tablespace, &sql);
463 : }
464 118 : run_reindex_command(free_slot->connection, process_type, objname,
465 : echo, &sql);
466 118 : termPQExpBuffer(&sql);
467 :
468 118 : cell = cell->next;
469 118 : } while (cell != NULL);
470 :
471 100 : if (!ParallelSlotsWaitCompletion(sa))
472 10 : failed = true;
473 :
474 90 : finish:
475 100 : if (process_list != user_list)
476 : {
477 48 : simple_string_list_destroy(process_list);
478 48 : pg_free(process_list);
479 : }
480 :
481 100 : if (tableoid_list)
482 : {
483 2 : simple_oid_list_destroy(tableoid_list);
484 2 : pg_free(tableoid_list);
485 : }
486 :
487 100 : ParallelSlotsTerminate(sa);
488 100 : pfree(sa);
489 :
490 100 : if (failed)
491 10 : 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 122 : 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 122 : const char *paren = "(";
504 122 : const char *comma = ", ";
505 122 : const char *sep = paren;
506 :
507 : Assert(name);
508 :
509 : /* build the REINDEX query */
510 122 : appendPQExpBufferStr(sql, "REINDEX ");
511 :
512 122 : if (verbose)
513 : {
514 8 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
515 8 : sep = comma;
516 : }
517 :
518 122 : if (tablespace)
519 : {
520 14 : appendPQExpBuffer(sql, "%sTABLESPACE %s", sep,
521 : fmtIdEnc(tablespace, PQclientEncoding(conn)));
522 14 : sep = comma;
523 : }
524 :
525 122 : if (sep != paren)
526 18 : appendPQExpBufferStr(sql, ") ");
527 :
528 : /* object type */
529 122 : switch (type)
530 : {
531 28 : case REINDEX_DATABASE:
532 28 : appendPQExpBufferStr(sql, "DATABASE ");
533 28 : break;
534 22 : case REINDEX_INDEX:
535 22 : appendPQExpBufferStr(sql, "INDEX ");
536 22 : break;
537 10 : case REINDEX_SCHEMA:
538 10 : appendPQExpBufferStr(sql, "SCHEMA ");
539 10 : break;
540 16 : case REINDEX_SYSTEM:
541 16 : appendPQExpBufferStr(sql, "SYSTEM ");
542 16 : break;
543 46 : case REINDEX_TABLE:
544 46 : appendPQExpBufferStr(sql, "TABLE ");
545 46 : 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 122 : if (concurrently)
554 34 : appendPQExpBufferStr(sql, "CONCURRENTLY ");
555 :
556 : /* object name */
557 122 : switch (type)
558 : {
559 44 : case REINDEX_DATABASE:
560 : case REINDEX_SYSTEM:
561 44 : appendPQExpBufferStr(sql,
562 : fmtIdEnc(name, PQclientEncoding(conn)));
563 44 : break;
564 68 : case REINDEX_INDEX:
565 : case REINDEX_TABLE:
566 68 : appendQualifiedRelation(sql, name, conn, echo);
567 68 : break;
568 10 : case REINDEX_SCHEMA:
569 10 : appendPQExpBufferStr(sql, name);
570 10 : break;
571 : }
572 :
573 : /* finish the query */
574 122 : appendPQExpBufferChar(sql, ';');
575 122 : }
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 118 : run_reindex_command(PGconn *conn, ReindexType type, const char *name,
583 : bool echo, PQExpBufferData *sql)
584 : {
585 : bool status;
586 :
587 118 : if (echo)
588 20 : printf("%s\n", sql->data);
589 :
590 118 : status = PQsendQuery(conn, sql->data) == 1;
591 :
592 118 : 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 118 : }
618 118 : }
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 6 : 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 6 : 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 6 : switch (type)
644 : {
645 2 : case REINDEX_DATABASE:
646 : Assert(user_list == NULL);
647 2 : 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 2 : break;
660 :
661 4 : 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 4 : 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 10 : for (cell = user_list->head; cell; cell = cell->next)
684 : {
685 6 : if (cell != user_list->head)
686 2 : appendPQExpBufferChar(&catalog_query, ',');
687 :
688 6 : appendStringLiteralConn(&catalog_query, cell->val, conn);
689 : }
690 :
691 4 : appendPQExpBufferStr(&catalog_query, ")\n"
692 : " ORDER BY c.relpages DESC;");
693 : }
694 4 : break;
695 :
696 0 : case REINDEX_INDEX:
697 : case REINDEX_SYSTEM:
698 : case REINDEX_TABLE:
699 : Assert(false);
700 0 : break;
701 : }
702 :
703 6 : res = executeQuery(conn, catalog_query.data, echo);
704 6 : termPQExpBuffer(&catalog_query);
705 :
706 : /*
707 : * If no rows are returned, there are no matching tables, so we are done.
708 : */
709 6 : ntups = PQntuples(res);
710 6 : if (ntups == 0)
711 : {
712 2 : PQclear(res);
713 2 : return NULL;
714 : }
715 :
716 4 : tables = pg_malloc0(sizeof(SimpleStringList));
717 :
718 : /* Build qualified identifiers for each table */
719 24 : for (int i = 0; i < ntups; i++)
720 : {
721 40 : simple_string_list_append(tables,
722 20 : fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
723 20 : PQgetvalue(res, i, 0),
724 : PQclientEncoding(conn)));
725 : }
726 4 : PQclear(res);
727 :
728 4 : 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 2 : 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 2 : 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 2 : 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 10 : for (cell = index_list->head; cell; cell = cell->next)
778 : {
779 8 : if (cell != index_list->head)
780 6 : appendPQExpBufferStr(&catalog_query, "', '");
781 :
782 8 : 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 2 : 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 2 : simple_string_list_destroy(index_list);
797 2 : index_list->head = index_list->tail = NULL;
798 :
799 2 : res = executeQuery(conn, catalog_query.data, echo);
800 2 : termPQExpBuffer(&catalog_query);
801 :
802 : /*
803 : * If no rows are returned, there are no matching tables, so we are done.
804 : */
805 2 : ntups = PQntuples(res);
806 2 : if (ntups == 0)
807 : {
808 0 : PQclear(res);
809 0 : return;
810 : }
811 :
812 2 : *table_list = pg_malloc0(sizeof(SimpleOidList));
813 :
814 : /*
815 : * Build two lists, one with table OIDs and the other with fully-qualified
816 : * index names.
817 : */
818 10 : for (int i = 0; i < ntups; i++)
819 : {
820 8 : simple_oid_list_append(*table_list, atooid(PQgetvalue(res, i, 0)));
821 16 : simple_string_list_append(index_list,
822 8 : fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
823 8 : PQgetvalue(res, i, 2),
824 : PQclientEncoding(conn)));
825 : }
826 :
827 2 : PQclear(res);
828 : }
829 :
830 : static void
831 14 : 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 14 : conn = connectMaintenanceDatabase(cparams, progname, echo);
843 14 : result = executeQuery(conn,
844 : "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
845 : echo);
846 14 : PQfinish(conn);
847 :
848 52 : for (i = 0; i < PQntuples(result); i++)
849 : {
850 38 : char *dbname = PQgetvalue(result, i, 0);
851 :
852 38 : if (!quiet)
853 : {
854 38 : printf(_("%s: reindexing database \"%s\"\n"), progname, dbname);
855 38 : fflush(stdout);
856 : }
857 :
858 38 : cparams->override_dbname = dbname;
859 :
860 38 : if (syscatalog)
861 4 : reindex_one_database(cparams, REINDEX_SYSTEM, NULL,
862 : progname, echo, verbose,
863 : concurrently, 1, tablespace);
864 :
865 38 : if (schemas->head != NULL)
866 4 : reindex_one_database(cparams, REINDEX_SCHEMA, schemas,
867 : progname, echo, verbose,
868 : concurrently, concurrentCons, tablespace);
869 :
870 38 : if (indexes->head != NULL)
871 4 : reindex_one_database(cparams, REINDEX_INDEX, indexes,
872 : progname, echo, verbose,
873 : concurrently, 1, tablespace);
874 :
875 38 : if (tables->head != NULL)
876 4 : 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 38 : if (!syscatalog && indexes->head == NULL &&
885 30 : tables->head == NULL && schemas->head == NULL)
886 22 : reindex_one_database(cparams, REINDEX_DATABASE, NULL,
887 : progname, echo, verbose,
888 : concurrently, concurrentCons, tablespace);
889 : }
890 :
891 14 : PQclear(result);
892 14 : }
893 :
894 : static void
895 2 : help(const char *progname)
896 : {
897 2 : printf(_("%s reindexes a PostgreSQL database.\n\n"), progname);
898 2 : printf(_("Usage:\n"));
899 2 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
900 2 : printf(_("\nOptions:\n"));
901 2 : printf(_(" -a, --all reindex all databases\n"));
902 2 : printf(_(" --concurrently reindex concurrently\n"));
903 2 : printf(_(" -d, --dbname=DBNAME database to reindex\n"));
904 2 : printf(_(" -e, --echo show the commands being sent to the server\n"));
905 2 : printf(_(" -i, --index=INDEX recreate specific index(es) only\n"));
906 2 : printf(_(" -j, --jobs=NUM use this many concurrent connections to reindex\n"));
907 2 : printf(_(" -q, --quiet don't write any messages\n"));
908 2 : printf(_(" -s, --system reindex system catalogs only\n"));
909 2 : printf(_(" -S, --schema=SCHEMA reindex specific schema(s) only\n"));
910 2 : printf(_(" -t, --table=TABLE reindex specific table(s) only\n"));
911 2 : printf(_(" --tablespace=TABLESPACE tablespace where indexes are rebuilt\n"));
912 2 : printf(_(" -v, --verbose write a lot of output\n"));
913 2 : printf(_(" -V, --version output version information, then exit\n"));
914 2 : printf(_(" -?, --help show this help, then exit\n"));
915 2 : printf(_("\nConnection options:\n"));
916 2 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
917 2 : printf(_(" -p, --port=PORT database server port\n"));
918 2 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
919 2 : printf(_(" -w, --no-password never prompt for password\n"));
920 2 : printf(_(" -W, --password force password prompt\n"));
921 2 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
922 2 : printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
923 2 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
924 2 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
925 2 : }
|
