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