Age Owner 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
6463 bruce 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 :
6385 86 32 : const char *dbname = NULL;
4142 rhaas 87 32 : const char *maintenance_db = NULL;
6385 bruce 88 32 : const char *host = NULL;
89 32 : const char *port = NULL;
90 32 : const char *username = NULL;
767 michael 91 32 : const char *tablespace = NULL;
5155 peter_e 92 32 : enum trivalue prompt_password = TRI_DEFAULT;
93 : ConnParams cparams;
6463 bruce 94 32 : bool syscatalog = false;
95 32 : bool alldb = false;
96 32 : bool echo = false;
97 32 : bool quiet = false;
2886 fujii 98 32 : bool verbose = false;
1472 peter 99 32 : bool concurrently = false;
3734 magnus 100 32 : SimpleStringList indexes = {NULL, NULL};
101 32 : SimpleStringList tables = {NULL, NULL};
3043 simon 102 32 : SimpleStringList schemas = {NULL, NULL};
1352 michael 103 32 : int concurrentCons = 1;
104 :
1469 peter 105 32 : pg_logging_init(argv[0]);
6463 bruce 106 32 : progname = get_progname(argv[0]);
5232 peter_e 107 32 : set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
108 :
6463 bruce 109 32 : handle_help_version_opts(argc, argv, "reindexdb", help);
110 :
111 : /* process command-line options */
118 peter 112 GNC 87 : while ((c = getopt_long(argc, argv, "ad:eh:i:j:qp:sS:t:U:vwW", long_options, &optindex)) != -1)
113 : {
6463 bruce 114 CBC 58 : switch (c)
115 : {
118 peter 116 GNC 2 : case 'a':
117 2 : alldb = true;
6463 bruce 118 GBC 2 : break;
118 peter 119 1 : case 'd':
120 1 : dbname = pg_strdup(optarg);
6463 bruce 121 CBC 1 : break;
118 peter 122 GNC 4 : case 'e':
123 4 : echo = true;
6463 bruce 124 CBC 4 : break;
118 peter 125 UNC 0 : case 'h':
126 0 : host = pg_strdup(optarg);
5155 peter_e 127 UBC 0 : break;
118 peter 128 GBC 5 : case 'i':
129 5 : simple_string_list_append(&indexes, optarg);
6463 bruce 130 CBC 5 : break;
118 peter 131 5 : case 'j':
132 5 : if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
118 peter 133 EUB : &concurrentCons))
118 peter 134 UBC 0 : exit(1);
6463 bruce 135 GBC 5 : break;
6463 bruce 136 UNC 0 : case 'q':
137 0 : quiet = true;
138 0 : break;
118 peter 139 0 : case 'p':
140 0 : port = pg_strdup(optarg);
6463 bruce 141 0 : break;
6463 bruce 142 GNC 4 : case 's':
143 4 : syscatalog = true;
144 4 : break;
118 peter 145 5 : case 'S':
146 5 : simple_string_list_append(&schemas, optarg);
147 5 : break;
6463 bruce 148 10 : case 't':
3734 magnus 149 10 : simple_string_list_append(&tables, optarg);
6463 bruce 150 10 : break;
118 peter 151 UNC 0 : case 'U':
152 0 : username = pg_strdup(optarg);
1352 michael 153 0 : break;
2886 fujii 154 GBC 4 : case 'v':
155 4 : verbose = true;
156 4 : break;
118 peter 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;
1472 peter 163 CBC 10 : case 1:
164 10 : concurrently = true;
165 10 : break;
4142 rhaas 166 UBC 0 : case 2:
3831 bruce 167 0 : maintenance_db = pg_strdup(optarg);
4142 rhaas 168 0 : break;
767 michael 169 CBC 7 : case 3:
170 7 : tablespace = pg_strdup(optarg);
171 7 : break;
6463 bruce 172 1 : default:
173 : /* getopt_long already emitted a complaint */
366 tgl 174 1 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
6463 bruce 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 : */
4009 andrew 183 29 : if (optind < argc && dbname == NULL)
184 : {
185 25 : dbname = argv[optind];
186 25 : optind++;
187 : }
188 :
189 29 : if (optind < argc)
190 : {
1469 peter 191 UBC 0 : pg_log_error("too many command-line arguments (first is \"%s\")",
192 : argv[optind]);
366 tgl 193 0 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
4009 andrew 194 0 : exit(1);
195 : }
196 :
197 : /* fill cparams except for dbname, which is set below */
902 tgl 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 :
1224 michael 204 29 : setup_cancel_handler(NULL);
205 :
6463 bruce 206 29 : if (alldb)
207 : {
208 2 : if (dbname)
366 tgl 209 UBC 0 : pg_fatal("cannot reindex all databases and a specific one at the same time");
6463 bruce 210 CBC 2 : if (syscatalog)
366 tgl 211 UBC 0 : pg_fatal("cannot reindex all databases and system catalogs at the same time");
3043 simon 212 CBC 2 : if (schemas.head != NULL)
366 tgl 213 UBC 0 : pg_fatal("cannot reindex specific schema(s) in all databases");
3734 magnus 214 CBC 2 : if (tables.head != NULL)
366 tgl 215 UBC 0 : pg_fatal("cannot reindex specific table(s) in all databases");
3734 magnus 216 CBC 2 : if (indexes.head != NULL)
366 tgl 217 UBC 0 : pg_fatal("cannot reindex specific index(es) in all databases");
218 :
902 tgl 219 CBC 2 : cparams.dbname = maintenance_db;
220 :
221 2 : reindex_all_databases(&cparams, progname, echo, quiet, verbose,
222 : concurrently, concurrentCons, tablespace);
223 : }
6463 bruce 224 27 : else if (syscatalog)
225 : {
3043 simon 226 4 : if (schemas.head != NULL)
366 tgl 227 UBC 0 : pg_fatal("cannot reindex specific schema(s) and system catalogs at the same time");
3734 magnus 228 CBC 4 : if (tables.head != NULL)
366 tgl 229 UBC 0 : pg_fatal("cannot reindex specific table(s) and system catalogs at the same time");
3734 magnus 230 CBC 4 : if (indexes.head != NULL)
366 tgl 231 UBC 0 : pg_fatal("cannot reindex specific index(es) and system catalogs at the same time");
232 :
1352 michael 233 CBC 4 : if (concurrentCons > 1)
366 tgl 234 1 : pg_fatal("cannot use multiple jobs to reindex system catalogs");
235 :
6463 bruce 236 3 : if (dbname == NULL)
237 : {
6463 bruce 238 UBC 0 : if (getenv("PGDATABASE"))
239 0 : dbname = getenv("PGDATABASE");
240 0 : else if (getenv("PGUSER"))
241 0 : dbname = getenv("PGUSER");
242 : else
3399 243 0 : dbname = get_user_name_or_exit(progname);
244 : }
245 :
902 tgl 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 : */
1352 michael 259 23 : if (concurrentCons > 1 && indexes.head != NULL)
366 tgl 260 1 : pg_fatal("cannot use multiple jobs to reindex indexes");
261 :
6463 bruce 262 22 : if (dbname == NULL)
263 : {
264 1 : if (getenv("PGDATABASE"))
265 1 : dbname = getenv("PGDATABASE");
6463 bruce 266 UBC 0 : else if (getenv("PGUSER"))
267 0 : dbname = getenv("PGUSER");
268 : else
3399 269 0 : dbname = get_user_name_or_exit(progname);
270 : }
271 :
902 tgl 272 CBC 22 : cparams.dbname = dbname;
273 :
3043 simon 274 22 : if (schemas.head != NULL)
902 tgl 275 4 : reindex_one_database(&cparams, REINDEX_SCHEMA, &schemas,
276 : progname, echo, verbose,
277 : concurrently, concurrentCons, tablespace);
278 :
3734 magnus 279 22 : if (indexes.head != NULL)
902 tgl 280 4 : reindex_one_database(&cparams, REINDEX_INDEX, &indexes,
281 : progname, echo, verbose,
282 : concurrently, 1, tablespace);
283 :
3734 magnus 284 20 : if (tables.head != NULL)
902 tgl 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 : */
3043 simon 293 18 : if (indexes.head == NULL && tables.head == NULL && schemas.head == NULL)
902 tgl 294 4 : reindex_one_database(&cparams, REINDEX_DATABASE, NULL,
295 : progname, echo, verbose,
296 : concurrently, concurrentCons, tablespace);
297 : }
298 :
6463 bruce 299 22 : exit(0);
300 : }
301 :
302 : static void
759 rhaas 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;
1352 michael 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 :
902 tgl 318 34 : conn = connectDatabase(cparams, progname, echo, false, false);
319 :
1472 peter 320 34 : if (concurrently && PQserverVersion(conn) < 120000)
321 : {
1472 peter 322 UBC 0 : PQfinish(conn);
366 tgl 323 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
324 : "concurrently", "12");
325 : }
326 :
767 michael 327 CBC 34 : if (tablespace && PQserverVersion(conn) < 140000)
328 : {
767 michael 329 UBC 0 : PQfinish(conn);
366 tgl 330 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
331 : "tablespace", "14");
332 : }
333 :
1352 michael 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 */
1352 michael 364 GIC 1 : process_list = get_parallel_object_list(conn, process_type,
1352 michael 365 ECB : user_list, echo);
1352 michael 366 GIC 1 : process_type = REINDEX_TABLE;
1352 michael 367 ECB :
368 : /* Bail out if nothing to process */
1352 michael 369 GIC 1 : if (process_list == NULL)
1352 michael 370 LBC 0 : return;
1352 michael 371 CBC 1 : break;
1352 michael 372 ECB :
1352 michael 373 GIC 2 : case REINDEX_SCHEMA:
1352 michael 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);
1352 michael 379 GIC 2 : process_type = REINDEX_TABLE;
1352 michael 380 EUB :
381 : /* Bail out if nothing to process */
1352 michael 382 GIC 2 : if (process_list == NULL)
383 1 : return;
384 1 : break;
385 :
1352 michael 386 UBC 0 : case REINDEX_SYSTEM:
387 : case REINDEX_INDEX:
388 : /* not supported */
1352 michael 389 UIC 0 : Assert(false);
390 : break;
391 :
392 0 : case REINDEX_TABLE:
393 :
394 : /*
1352 michael 395 ECB : * Fall through. The list of items for tables is already
396 : * created.
397 : */
1352 michael 398 UIC 0 : break;
399 : }
1352 michael 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 : */
1352 michael 407 GIC 35 : for (cell = process_list->head; cell; cell = cell->next)
1352 michael 408 ECB : {
1352 michael 409 CBC 35 : items_count++;
410 :
1352 michael 411 ECB : /* no need to continue if there are more elements than jobs */
1352 michael 412 GIC 35 : if (items_count >= concurrentCons)
413 33 : break;
1352 michael 414 ECB : }
1352 michael 415 CBC 33 : concurrentCons = Min(concurrentCons, items_count);
1352 michael 416 GIC 33 : Assert(concurrentCons > 0);
1352 michael 417 ECB :
1352 michael 418 GIC 33 : Assert(process_list != NULL);
1352 michael 419 EUB :
759 rhaas 420 GBC 33 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, NULL);
759 rhaas 421 GIC 33 : ParallelSlotsAdoptConn(sa, conn);
422 :
1352 michael 423 CBC 33 : cell = process_list->head;
1352 michael 424 ECB : do
425 : {
1352 michael 426 GBC 41 : const char *objname = cell->val;
427 41 : ParallelSlot *free_slot = NULL;
428 :
1352 michael 429 GIC 41 : if (CancelRequested)
1352 michael 430 ECB : {
1352 michael 431 LBC 0 : failed = true;
1352 michael 432 UIC 0 : goto finish;
433 : }
1352 michael 434 ECB :
759 rhaas 435 CBC 41 : free_slot = ParallelSlotsGetIdle(sa, NULL);
1352 michael 436 GIC 41 : if (!free_slot)
1352 michael 437 ECB : {
1352 michael 438 LBC 0 : failed = true;
1352 michael 439 UIC 0 : goto finish;
1352 michael 440 ECB : }
441 :
793 rhaas 442 GIC 41 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
1352 michael 443 CBC 41 : run_reindex_command(free_slot->connection, process_type, objname,
767 michael 444 ECB : echo, verbose, concurrently, true, tablespace);
445 :
1352 michael 446 GIC 41 : cell = cell->next;
1352 michael 447 CBC 41 : } while (cell != NULL);
1352 michael 448 ECB :
759 rhaas 449 GIC 33 : if (!ParallelSlotsWaitCompletion(sa))
1352 michael 450 CBC 5 : failed = true;
1352 michael 451 ECB :
1352 michael 452 GIC 28 : finish:
1349 453 33 : if (process_list != user_list)
454 : {
1349 michael 455 CBC 17 : simple_string_list_destroy(process_list);
1349 michael 456 GIC 17 : pg_free(process_list);
457 : }
458 :
759 rhaas 459 CBC 33 : ParallelSlotsTerminate(sa);
460 33 : pfree(sa);
1352 michael 461 ECB :
1352 michael 462 GIC 33 : if (failed)
463 5 : exit(1);
464 : }
1352 michael 465 ECB :
466 : static void
1352 michael 467 GIC 41 : run_reindex_command(PGconn *conn, ReindexType type, const char *name,
767 michael 468 ECB : bool echo, bool verbose, bool concurrently, bool async,
469 : const char *tablespace)
1352 470 : {
767 michael 471 GIC 41 : const char *paren = "(";
767 michael 472 CBC 41 : const char *comma = ", ";
767 michael 473 GIC 41 : const char *sep = paren;
1352 michael 474 ECB : PQExpBufferData sql;
475 : bool status;
476 :
1352 michael 477 GIC 41 : Assert(name);
1352 michael 478 ECB :
479 : /* build the REINDEX query */
6463 bruce 480 CBC 41 : initPQExpBuffer(&sql);
6463 bruce 481 ECB :
1868 noah 482 GIC 41 : appendPQExpBufferStr(&sql, "REINDEX ");
483 :
2886 fujii 484 CBC 41 : if (verbose)
767 michael 485 ECB : {
767 michael 486 GIC 4 : appendPQExpBuffer(&sql, "%sVERBOSE", sep);
487 4 : sep = comma;
767 michael 488 ECB : }
489 :
767 michael 490 CBC 41 : if (tablespace)
767 michael 491 ECB : {
767 michael 492 CBC 7 : appendPQExpBuffer(&sql, "%sTABLESPACE %s", sep, fmtId(tablespace));
493 7 : sep = comma;
767 michael 494 ECB : }
495 :
767 michael 496 CBC 41 : if (sep != paren)
497 9 : appendPQExpBufferStr(&sql, ") ");
2886 fujii 498 ECB :
1377 michael 499 : /* object type */
1377 michael 500 CBC 41 : switch (type)
1377 michael 501 ECB : {
1377 michael 502 CBC 12 : case REINDEX_DATABASE:
503 12 : appendPQExpBufferStr(&sql, "DATABASE ");
504 12 : break;
1377 michael 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:
1377 michael 512 CBC 3 : appendPQExpBufferStr(&sql, "SYSTEM ");
513 3 : break;
1377 michael 514 GIC 20 : case REINDEX_TABLE:
515 20 : appendPQExpBufferStr(&sql, "TABLE ");
1377 michael 516 CBC 20 : break;
517 : }
1377 michael 518 ECB :
519 : /*
767 520 : * Parenthesized grammar is only supported for CONCURRENTLY since
521 : * PostgreSQL 14. Since 12, CONCURRENTLY can be specified after the
522 : * object type.
523 : */
1472 peter 524 CBC 41 : if (concurrently)
525 17 : appendPQExpBufferStr(&sql, "CONCURRENTLY ");
1377 michael 526 ECB :
527 : /* object name */
1377 michael 528 CBC 41 : switch (type)
529 : {
1377 michael 530 GIC 15 : case REINDEX_DATABASE:
531 : case REINDEX_SYSTEM:
1352 michael 532 CBC 15 : appendPQExpBufferStr(&sql, fmtId(name));
1377 michael 533 GIC 15 : break;
1377 michael 534 CBC 24 : case REINDEX_INDEX:
535 : case REINDEX_TABLE:
1360 536 24 : appendQualifiedRelation(&sql, name, conn, echo);
1377 537 24 : break;
1377 michael 538 GIC 2 : case REINDEX_SCHEMA:
1377 michael 539 CBC 2 : appendPQExpBufferStr(&sql, name);
1377 michael 540 GIC 2 : break;
541 : }
1377 michael 542 EUB :
543 : /* finish the query */
2838 heikki.linnakangas 544 CBC 41 : appendPQExpBufferChar(&sql, ';');
545 :
1352 michael 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;
1352 michael 552 EUB : }
553 : else
1352 michael 554 UIC 0 : status = executeMaintenanceCommand(conn, sql.data, echo);
1352 michael 555 EUB :
1352 michael 556 GBC 41 : if (!status)
6463 bruce 557 EUB : {
1377 michael 558 UIC 0 : switch (type)
1377 michael 559 EUB : {
1377 michael 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;
1377 michael 568 UIC 0 : case REINDEX_SCHEMA:
1377 michael 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:
1066 peter 573 UIC 0 : pg_log_error("reindexing of system catalogs in database \"%s\" failed: %s",
574 : PQdb(conn), PQerrorMessage(conn));
1377 michael 575 0 : break;
1377 michael 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));
1377 michael 579 UIC 0 : break;
580 : }
1352 581 0 : if (!async)
582 : {
583 0 : PQfinish(conn);
584 0 : exit(1);
585 : }
586 : }
587 :
6463 bruce 588 CBC 41 : termPQExpBuffer(&sql);
6463 bruce 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.
1352 michael 598 ECB : */
599 : static SimpleStringList *
1352 michael 600 GIC 3 : get_parallel_object_list(PGconn *conn, ReindexType type,
601 : SimpleStringList *user_list, bool echo)
602 : {
603 : PQExpBufferData catalog_query;
1352 michael 604 ECB : PQExpBufferData buf;
605 : PGresult *res;
606 : SimpleStringList *tables;
607 : int ntups,
608 : i;
609 :
1352 michael 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 : {
1352 michael 618 CBC 1 : case REINDEX_DATABASE:
1352 michael 619 GIC 1 : Assert(user_list == NULL);
906 drowley 620 CBC 1 : appendPQExpBufferStr(&catalog_query,
621 : "SELECT c.relname, ns.nspname\n"
622 : " FROM pg_catalog.pg_class c\n"
906 drowley 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;");
1352 michael 630 GIC 1 : break;
1352 michael 631 ECB :
1352 michael 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
1352 michael 641 ECB : * once.
642 : */
906 drowley 643 CBC 2 : appendPQExpBufferStr(&catalog_query,
644 : "SELECT c.relname, ns.nspname\n"
906 drowley 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 :
1352 michael 653 CBC 5 : for (cell = user_list->head; cell; cell = cell->next)
654 : {
1352 michael 655 GIC 3 : const char *nspname = cell->val;
1352 michael 656 ECB :
1352 michael 657 GIC 3 : if (nsp_listed)
906 drowley 658 GBC 1 : appendPQExpBufferStr(&catalog_query, ", ");
659 : else
1352 michael 660 GIC 2 : nsp_listed = true;
1352 michael 661 EUB :
1352 michael 662 GIC 3 : appendStringLiteralConn(&catalog_query, nspname, conn);
663 : }
664 :
906 drowley 665 CBC 2 : appendPQExpBufferStr(&catalog_query, ")\n"
906 drowley 666 ECB : " ORDER BY c.relpages DESC;");
667 : }
1352 michael 668 GIC 2 : break;
669 :
1352 michael 670 UIC 0 : case REINDEX_SYSTEM:
1352 michael 671 ECB : case REINDEX_INDEX:
672 : case REINDEX_TABLE:
1352 michael 673 UIC 0 : Assert(false);
1352 michael 674 ECB : break;
675 : }
676 :
1352 michael 677 GIC 3 : res = executeQuery(conn, catalog_query.data, echo);
678 3 : termPQExpBuffer(&catalog_query);
1352 michael 679 ECB :
680 : /*
681 : * If no rows are returned, there are no matching tables, so we are done.
682 : */
1352 michael 683 CBC 3 : ntups = PQntuples(res);
1352 michael 684 GIC 3 : if (ntups == 0)
1352 michael 685 ECB : {
1352 michael 686 CBC 1 : PQclear(res);
687 1 : PQfinish(conn);
1352 michael 688 GIC 1 : return NULL;
1352 michael 689 ECB : }
690 :
1352 michael 691 GIC 2 : tables = pg_malloc0(sizeof(SimpleStringList));
1352 michael 692 ECB :
693 : /* Build qualified identifiers for each table */
1352 michael 694 GIC 2 : initPQExpBuffer(&buf);
1352 michael 695 CBC 12 : for (i = 0; i < ntups; i++)
696 : {
1352 michael 697 GIC 10 : appendPQExpBufferStr(&buf,
698 10 : fmtQualifiedId(PQgetvalue(res, i, 1),
1352 michael 699 CBC 10 : PQgetvalue(res, i, 0)));
700 :
1352 michael 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;
1352 michael 708 ECB : }
709 :
6463 bruce 710 : static void
902 tgl 711 GIC 2 : reindex_all_databases(ConnParams *cparams,
1472 peter 712 ECB : const char *progname, bool echo, bool quiet, bool verbose,
713 : bool concurrently, int concurrentCons,
767 michael 714 : const char *tablespace)
715 : {
6385 bruce 716 : PGconn *conn;
717 : PGresult *result;
718 : int i;
6463 719 :
902 tgl 720 GIC 2 : conn = connectMaintenanceDatabase(cparams, progname, echo);
1360 michael 721 2 : result = executeQuery(conn, "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;", echo);
6463 bruce 722 CBC 2 : PQfinish(conn);
723 :
724 11 : for (i = 0; i < PQntuples(result); i++)
725 : {
6385 bruce 726 GIC 9 : char *dbname = PQgetvalue(result, i, 0);
727 :
6463 728 9 : if (!quiet)
5788 peter_e 729 ECB : {
5788 peter_e 730 CBC 9 : printf(_("%s: reindexing database \"%s\"\n"), progname, dbname);
5788 peter_e 731 GIC 9 : fflush(stdout);
732 : }
6463 bruce 733 ECB :
902 tgl 734 GIC 9 : cparams->override_dbname = dbname;
2435 noah 735 ECB :
902 tgl 736 CBC 9 : reindex_one_database(cparams, REINDEX_DATABASE, NULL,
1352 michael 737 ECB : progname, echo, verbose, concurrently,
767 738 : concurrentCons, tablespace);
6463 bruce 739 : }
740 :
6463 bruce 741 CBC 2 : PQclear(result);
742 2 : }
6463 bruce 743 ECB :
744 : static void
6463 bruce 745 CBC 1 : help(const char *progname)
6463 bruce 746 ECB : {
6463 bruce 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"));
767 michael 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"));
529 magnus 758 1 : printf(_(" -s, --system reindex system catalogs only\n"));
767 michael 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"));
767 michael 764 GIC 1 : printf(_(" -?, --help show this help, then exit\n"));
6463 bruce 765 1 : printf(_("\nConnection options:\n"));
767 michael 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"));
6463 bruce 772 1 : printf(_("\nRead the description of the SQL command REINDEX for details.\n"));
1136 peter 773 1 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
774 1 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
6463 bruce 775 1 : }
|
