?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Consistently use "superuser" instead of "super user"
[pgsql.git] / src / bin / scripts / vacuumdb.c
blobf40bd148579f6498f22e14b7cd6fd8df4e130491
1 /*-------------------------------------------------------------------------
2 *
3 * vacuumdb
4 *
5 * Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
7 *
8 * src/bin/scripts/vacuumdb.c
9 *
10 *-------------------------------------------------------------------------
11 */
12
13 #include "postgres_fe.h"
14
15 #include <limits.h>
16
17 #include "catalog/pg_class_d.h"
18 #include "common.h"
19 #include "common/connect.h"
20 #include "common/logging.h"
21 #include "fe_utils/cancel.h"
22 #include "fe_utils/option_utils.h"
23 #include "fe_utils/parallel_slot.h"
24 #include "fe_utils/query_utils.h"
25 #include "fe_utils/simple_list.h"
26 #include "fe_utils/string_utils.h"
27
28
29 /* vacuum options controlled by user flags */
30 typedef struct vacuumingOptions
31 {
32 bool analyze_only;
33 bool verbose;
34 bool and_analyze;
35 bool full;
36 bool freeze;
37 bool disable_page_skipping;
38 bool skip_locked;
39 int min_xid_age;
40 int min_mxid_age;
41 int parallel_workers; /* >= 0 indicates user specified the
42 * parallel degree, otherwise -1 */
43 bool no_index_cleanup;
44 bool force_index_cleanup;
45 bool do_truncate;
46 bool process_toast;
47 } vacuumingOptions;
48
49
50 static void vacuum_one_database(ConnParams *cparams,
51 vacuumingOptions *vacopts,
52 int stage,
53 SimpleStringList *tables,
54 int concurrentCons,
55 const char *progname, bool echo, bool quiet);
56
57 static void vacuum_all_databases(ConnParams *cparams,
58 vacuumingOptions *vacopts,
59 bool analyze_in_stages,
60 int concurrentCons,
61 const char *progname, bool echo, bool quiet);
62
63 static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
64 vacuumingOptions *vacopts, const char *table);
65
66 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
67 const char *table);
68
69 static void help(const char *progname);
70
71 /* For analyze-in-stages mode */
72 #define ANALYZE_NO_STAGE -1
73 #define ANALYZE_NUM_STAGES 3
74
75
76 int
77 main(int argc, char *argv[])
78 {
79 static struct option long_options[] = {
80 {"host", required_argument, NULL, 'h'},
81 {"port", required_argument, NULL, 'p'},
82 {"username", required_argument, NULL, 'U'},
83 {"no-password", no_argument, NULL, 'w'},
84 {"password", no_argument, NULL, 'W'},
85 {"echo", no_argument, NULL, 'e'},
86 {"quiet", no_argument, NULL, 'q'},
87 {"dbname", required_argument, NULL, 'd'},
88 {"analyze", no_argument, NULL, 'z'},
89 {"analyze-only", no_argument, NULL, 'Z'},
90 {"freeze", no_argument, NULL, 'F'},
91 {"all", no_argument, NULL, 'a'},
92 {"table", required_argument, NULL, 't'},
93 {"full", no_argument, NULL, 'f'},
94 {"verbose", no_argument, NULL, 'v'},
95 {"jobs", required_argument, NULL, 'j'},
96 {"parallel", required_argument, NULL, 'P'},
97 {"maintenance-db", required_argument, NULL, 2},
98 {"analyze-in-stages", no_argument, NULL, 3},
99 {"disable-page-skipping", no_argument, NULL, 4},
100 {"skip-locked", no_argument, NULL, 5},
101 {"min-xid-age", required_argument, NULL, 6},
102 {"min-mxid-age", required_argument, NULL, 7},
103 {"no-index-cleanup", no_argument, NULL, 8},
104 {"force-index-cleanup", no_argument, NULL, 9},
105 {"no-truncate", no_argument, NULL, 10},
106 {"no-process-toast", no_argument, NULL, 11},
107 {NULL, 0, NULL, 0}
108 };
109
110 const char *progname;
111 int optindex;
112 int c;
113 const char *dbname = NULL;
114 const char *maintenance_db = NULL;
115 char *host = NULL;
116 char *port = NULL;
117 char *username = NULL;
118 enum trivalue prompt_password = TRI_DEFAULT;
119 ConnParams cparams;
120 bool echo = false;
121 bool quiet = false;
122 vacuumingOptions vacopts;
123 bool analyze_in_stages = false;
124 bool alldb = false;
125 SimpleStringList tables = {NULL, NULL};
126 int concurrentCons = 1;
127 int tbl_count = 0;
128
129 /* initialize options */
130 memset(&vacopts, 0, sizeof(vacopts));
131 vacopts.parallel_workers = -1;
132 vacopts.no_index_cleanup = false;
133 vacopts.force_index_cleanup = false;
134 vacopts.do_truncate = true;
135 vacopts.process_toast = true;
136
137 pg_logging_init(argv[0]);
138 progname = get_progname(argv[0]);
139 set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
140
141 handle_help_version_opts(argc, argv, "vacuumdb", help);
142
143 while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:", long_options, &optindex)) != -1)
144 {
145 switch (c)
146 {
147 case 'h':
148 host = pg_strdup(optarg);
149 break;
150 case 'p':
151 port = pg_strdup(optarg);
152 break;
153 case 'U':
154 username = pg_strdup(optarg);
155 break;
156 case 'w':
157 prompt_password = TRI_NO;
158 break;
159 case 'W':
160 prompt_password = TRI_YES;
161 break;
162 case 'e':
163 echo = true;
164 break;
165 case 'q':
166 quiet = true;
167 break;
168 case 'd':
169 dbname = pg_strdup(optarg);
170 break;
171 case 'z':
172 vacopts.and_analyze = true;
173 break;
174 case 'Z':
175 vacopts.analyze_only = true;
176 break;
177 case 'F':
178 vacopts.freeze = true;
179 break;
180 case 'a':
181 alldb = true;
182 break;
183 case 't':
184 {
185 simple_string_list_append(&tables, optarg);
186 tbl_count++;
187 break;
188 }
189 case 'f':
190 vacopts.full = true;
191 break;
192 case 'v':
193 vacopts.verbose = true;
194 break;
195 case 'j':
196 if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
197 &concurrentCons))
198 exit(1);
199 break;
200 case 'P':
201 if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
202 &vacopts.parallel_workers))
203 exit(1);
204 break;
205 case 2:
206 maintenance_db = pg_strdup(optarg);
207 break;
208 case 3:
209 analyze_in_stages = vacopts.analyze_only = true;
210 break;
211 case 4:
212 vacopts.disable_page_skipping = true;
213 break;
214 case 5:
215 vacopts.skip_locked = true;
216 break;
217 case 6:
218 if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
219 &vacopts.min_xid_age))
220 exit(1);
221 break;
222 case 7:
223 if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
224 &vacopts.min_mxid_age))
225 exit(1);
226 break;
227 case 8:
228 vacopts.no_index_cleanup = true;
229 break;
230 case 9:
231 vacopts.force_index_cleanup = true;
232 break;
233 case 10:
234 vacopts.do_truncate = false;
235 break;
236 case 11:
237 vacopts.process_toast = false;
238 break;
239 default:
240 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
241 exit(1);
242 }
243 }
244
245 /*
246 * Non-option argument specifies database name as long as it wasn't
247 * already specified with -d / --dbname
248 */
249 if (optind < argc && dbname == NULL)
250 {
251 dbname = argv[optind];
252 optind++;
253 }
254
255 if (optind < argc)
256 {
257 pg_log_error("too many command-line arguments (first is \"%s\")",
258 argv[optind]);
259 fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
260 exit(1);
261 }
262
263 if (vacopts.analyze_only)
264 {
265 if (vacopts.full)
266 {
267 pg_log_error("cannot use the \"%s\" option when performing only analyze",
268 "full");
269 exit(1);
270 }
271 if (vacopts.freeze)
272 {
273 pg_log_error("cannot use the \"%s\" option when performing only analyze",
274 "freeze");
275 exit(1);
276 }
277 if (vacopts.disable_page_skipping)
278 {
279 pg_log_error("cannot use the \"%s\" option when performing only analyze",
280 "disable-page-skipping");
281 exit(1);
282 }
283 if (vacopts.no_index_cleanup)
284 {
285 pg_log_error("cannot use the \"%s\" option when performing only analyze",
286 "no-index-cleanup");
287 exit(1);
288 }
289 if (vacopts.force_index_cleanup)
290 {
291 pg_log_error("cannot use the \"%s\" option when performing only analyze",
292 "force-index-cleanup");
293 exit(1);
294 }
295 if (!vacopts.do_truncate)
296 {
297 pg_log_error("cannot use the \"%s\" option when performing only analyze",
298 "no-truncate");
299 exit(1);
300 }
301 if (!vacopts.process_toast)
302 {
303 pg_log_error("cannot use the \"%s\" option when performing only analyze",
304 "no-process-toast");
305 exit(1);
306 }
307 /* allow 'and_analyze' with 'analyze_only' */
308 }
309
310 /* Prohibit full and analyze_only options with parallel option */
311 if (vacopts.parallel_workers >= 0)
312 {
313 if (vacopts.analyze_only)
314 {
315 pg_log_error("cannot use the \"%s\" option when performing only analyze",
316 "parallel");
317 exit(1);
318 }
319 if (vacopts.full)
320 {
321 pg_log_error("cannot use the \"%s\" option when performing full vacuum",
322 "parallel");
323 exit(1);
324 }
325 }
326
327 /* Prohibit --no-index-cleanup and --force-index-cleanup together */
328 if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
329 {
330 pg_log_error("cannot use the \"%s\" option with the \"%s\" option",
331 "no-index-cleanup", "force-index-cleanup");
332 exit(1);
333 }
334
335 /* fill cparams except for dbname, which is set below */
336 cparams.pghost = host;
337 cparams.pgport = port;
338 cparams.pguser = username;
339 cparams.prompt_password = prompt_password;
340 cparams.override_dbname = NULL;
341
342 setup_cancel_handler(NULL);
343
344 /* Avoid opening extra connections. */
345 if (tbl_count && (concurrentCons > tbl_count))
346 concurrentCons = tbl_count;
347
348 if (alldb)
349 {
350 if (dbname)
351 {
352 pg_log_error("cannot vacuum all databases and a specific one at the same time");
353 exit(1);
354 }
355 if (tables.head != NULL)
356 {
357 pg_log_error("cannot vacuum specific table(s) in all databases");
358 exit(1);
359 }
360
361 cparams.dbname = maintenance_db;
362
363 vacuum_all_databases(&cparams, &vacopts,
364 analyze_in_stages,
365 concurrentCons,
366 progname, echo, quiet);
367 }
368 else
369 {
370 if (dbname == NULL)
371 {
372 if (getenv("PGDATABASE"))
373 dbname = getenv("PGDATABASE");
374 else if (getenv("PGUSER"))
375 dbname = getenv("PGUSER");
376 else
377 dbname = get_user_name_or_exit(progname);
378 }
379
380 cparams.dbname = dbname;
381
382 if (analyze_in_stages)
383 {
384 int stage;
385
386 for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
387 {
388 vacuum_one_database(&cparams, &vacopts,
389 stage,
390 &tables,
391 concurrentCons,
392 progname, echo, quiet);
393 }
394 }
395 else
396 vacuum_one_database(&cparams, &vacopts,
397 ANALYZE_NO_STAGE,
398 &tables,
399 concurrentCons,
400 progname, echo, quiet);
401 }
402
403 exit(0);
404 }
405
406 /*
407 * vacuum_one_database
408 *
409 * Process tables in the given database. If the 'tables' list is empty,
410 * process all tables in the database.
411 *
412 * Note that this function is only concerned with running exactly one stage
413 * when in analyze-in-stages mode; caller must iterate on us if necessary.
414 *
415 * If concurrentCons is > 1, multiple connections are used to vacuum tables
416 * in parallel. In this case and if the table list is empty, we first obtain
417 * a list of tables from the database.
418 */
419 static void
420 vacuum_one_database(ConnParams *cparams,
421 vacuumingOptions *vacopts,
422 int stage,
423 SimpleStringList *tables,
424 int concurrentCons,
425 const char *progname, bool echo, bool quiet)
426 {
427 PQExpBufferData sql;
428 PQExpBufferData buf;
429 PQExpBufferData catalog_query;
430 PGresult *res;
431 PGconn *conn;
432 SimpleStringListCell *cell;
433 ParallelSlotArray *sa;
434 SimpleStringList dbtables = {NULL, NULL};
435 int i;
436 int ntups;
437 bool failed = false;
438 bool tables_listed = false;
439 bool has_where = false;
440 const char *initcmd;
441 const char *stage_commands[] = {
442 "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
443 "SET default_statistics_target=10; RESET vacuum_cost_delay;",
444 "RESET default_statistics_target;"
445 };
446 const char *stage_messages[] = {
447 gettext_noop("Generating minimal optimizer statistics (1 target)"),
448 gettext_noop("Generating medium optimizer statistics (10 targets)"),
449 gettext_noop("Generating default (full) optimizer statistics")
450 };
451
452 Assert(stage == ANALYZE_NO_STAGE ||
453 (stage >= 0 && stage < ANALYZE_NUM_STAGES));
454
455 conn = connectDatabase(cparams, progname, echo, false, true);
456
457 if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
458 {
459 PQfinish(conn);
460 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
461 "disable-page-skipping", "9.6");
462 exit(1);
463 }
464
465 if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
466 {
467 PQfinish(conn);
468 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
469 "no-index-cleanup", "12");
470 exit(1);
471 }
472
473 if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
474 {
475 PQfinish(conn);
476 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
477 "force-index-cleanup", "12");
478 exit(1);
479 }
480
481 if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
482 {
483 PQfinish(conn);
484 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
485 "no-truncate", "12");
486 exit(1);
487 }
488
489 if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
490 {
491 PQfinish(conn);
492 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
493 "no-process-toast", "14");
494 exit(1);
495 }
496
497 if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
498 {
499 PQfinish(conn);
500 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
501 "skip-locked", "12");
502 exit(1);
503 }
504
505 if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
506 {
507 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
508 "--min-xid-age", "9.6");
509 exit(1);
510 }
511
512 if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
513 {
514 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
515 "--min-mxid-age", "9.6");
516 exit(1);
517 }
518
519 if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
520 {
521 pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
522 "--parallel", "13");
523 exit(1);
524 }
525
526 if (!quiet)
527 {
528 if (stage != ANALYZE_NO_STAGE)
529 printf(_("%s: processing database \"%s\": %s\n"),
530 progname, PQdb(conn), _(stage_messages[stage]));
531 else
532 printf(_("%s: vacuuming database \"%s\"\n"),
533 progname, PQdb(conn));
534 fflush(stdout);
535 }
536
537 /*
538 * Prepare the list of tables to process by querying the catalogs.
539 *
540 * Since we execute the constructed query with the default search_path
541 * (which could be unsafe), everything in this query MUST be fully
542 * qualified.
543 *
544 * First, build a WITH clause for the catalog query if any tables were
545 * specified, with a set of values made of relation names and their
546 * optional set of columns. This is used to match any provided column
547 * lists with the generated qualified identifiers and to filter for the
548 * tables provided via --table. If a listed table does not exist, the
549 * catalog query will fail.
550 */
551 initPQExpBuffer(&catalog_query);
552 for (cell = tables ? tables->head : NULL; cell; cell = cell->next)
553 {
554 char *just_table;
555 const char *just_columns;
556
557 /*
558 * Split relation and column names given by the user, this is used to
559 * feed the CTE with values on which are performed pre-run validity
560 * checks as well. For now these happen only on the relation name.
561 */
562 splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
563 &just_table, &just_columns);
564
565 if (!tables_listed)
566 {
567 appendPQExpBufferStr(&catalog_query,
568 "WITH listed_tables (table_oid, column_list) "
569 "AS (\n VALUES (");
570 tables_listed = true;
571 }
572 else
573 appendPQExpBufferStr(&catalog_query, ",\n (");
574
575 appendStringLiteralConn(&catalog_query, just_table, conn);
576 appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
577
578 if (just_columns && just_columns[0] != '\0')
579 appendStringLiteralConn(&catalog_query, just_columns, conn);
580 else
581 appendPQExpBufferStr(&catalog_query, "NULL");
582
583 appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
584
585 pg_free(just_table);
586 }
587
588 /* Finish formatting the CTE */
589 if (tables_listed)
590 appendPQExpBufferStr(&catalog_query, "\n)\n");
591
592 appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
593
594 if (tables_listed)
595 appendPQExpBufferStr(&catalog_query, ", listed_tables.column_list");
596
597 appendPQExpBufferStr(&catalog_query,
598 " FROM pg_catalog.pg_class c\n"
599 " JOIN pg_catalog.pg_namespace ns"
600 " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
601 " LEFT JOIN pg_catalog.pg_class t"
602 " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
603
604 /* Used to match the tables listed by the user */
605 if (tables_listed)
606 appendPQExpBufferStr(&catalog_query, " JOIN listed_tables"
607 " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n");
608
609 /*
610 * If no tables were listed, filter for the relevant relation types. If
611 * tables were given via --table, don't bother filtering by relation type.
612 * Instead, let the server decide whether a given relation can be
613 * processed in which case the user will know about it.
614 */
615 if (!tables_listed)
616 {
617 appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
618 CppAsString2(RELKIND_RELATION) ", "
619 CppAsString2(RELKIND_MATVIEW) "])\n");
620 has_where = true;
621 }
622
623 /*
624 * For --min-xid-age and --min-mxid-age, the age of the relation is the
625 * greatest of the ages of the main relation and its associated TOAST
626 * table. The commands generated by vacuumdb will also process the TOAST
627 * table for the relation if necessary, so it does not need to be
628 * considered separately.
629 */
630 if (vacopts->min_xid_age != 0)
631 {
632 appendPQExpBuffer(&catalog_query,
633 " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
634 " pg_catalog.age(t.relfrozenxid)) "
635 " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
636 " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
637 " '0'::pg_catalog.xid\n",
638 has_where ? "AND" : "WHERE", vacopts->min_xid_age);
639 has_where = true;
640 }
641
642 if (vacopts->min_mxid_age != 0)
643 {
644 appendPQExpBuffer(&catalog_query,
645 " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
646 " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
647 " '%d'::pg_catalog.int4\n"
648 " AND c.relminmxid OPERATOR(pg_catalog.!=)"
649 " '0'::pg_catalog.xid\n",
650 has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
651 has_where = true;
652 }
653
654 /*
655 * Execute the catalog query. We use the default search_path for this
656 * query for consistency with table lookups done elsewhere by the user.
657 */
658 appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
659 executeCommand(conn, "RESET search_path;", echo);
660 res = executeQuery(conn, catalog_query.data, echo);
661 termPQExpBuffer(&catalog_query);
662 PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
663
664 /*
665 * If no rows are returned, there are no matching tables, so we are done.
666 */
667 ntups = PQntuples(res);
668 if (ntups == 0)
669 {
670 PQclear(res);
671 PQfinish(conn);
672 return;
673 }
674
675 /*
676 * Build qualified identifiers for each table, including the column list
677 * if given.
678 */
679 initPQExpBuffer(&buf);
680 for (i = 0; i < ntups; i++)
681 {
682 appendPQExpBufferStr(&buf,
683 fmtQualifiedId(PQgetvalue(res, i, 1),
684 PQgetvalue(res, i, 0)));
685
686 if (tables_listed && !PQgetisnull(res, i, 2))
687 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
688
689 simple_string_list_append(&dbtables, buf.data);
690 resetPQExpBuffer(&buf);
691 }
692 termPQExpBuffer(&buf);
693 PQclear(res);
694
695 /*
696 * Ensure concurrentCons is sane. If there are more connections than
697 * vacuumable relations, we don't need to use them all.
698 */
699 if (concurrentCons > ntups)
700 concurrentCons = ntups;
701 if (concurrentCons <= 0)
702 concurrentCons = 1;
703
704 /*
705 * All slots need to be prepared to run the appropriate analyze stage, if
706 * caller requested that mode. We have to prepare the initial connection
707 * ourselves before setting up the slots.
708 */
709 if (stage == ANALYZE_NO_STAGE)
710 initcmd = NULL;
711 else
712 {
713 initcmd = stage_commands[stage];
714 executeCommand(conn, initcmd, echo);
715 }
716
717 /*
718 * Setup the database connections. We reuse the connection we already have
719 * for the first slot. If not in parallel mode, the first slot in the
720 * array contains the connection.
721 */
722 sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
723 ParallelSlotsAdoptConn(sa, conn);
724
725 initPQExpBuffer(&sql);
726
727 cell = dbtables.head;
728 do
729 {
730 const char *tabname = cell->val;
731 ParallelSlot *free_slot;
732
733 if (CancelRequested)
734 {
735 failed = true;
736 goto finish;
737 }
738
739 free_slot = ParallelSlotsGetIdle(sa, NULL);
740 if (!free_slot)
741 {
742 failed = true;
743 goto finish;
744 }
745
746 prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection),
747 vacopts, tabname);
748
749 /*
750 * Execute the vacuum. All errors are handled in processQueryResult
751 * through ParallelSlotsGetIdle.
752 */
753 ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
754 run_vacuum_command(free_slot->connection, sql.data,
755 echo, tabname);
756
757 cell = cell->next;
758 } while (cell != NULL);
759
760 if (!ParallelSlotsWaitCompletion(sa))
761 failed = true;
762
763 finish:
764 ParallelSlotsTerminate(sa);
765 pg_free(sa);
766
767 termPQExpBuffer(&sql);
768
769 if (failed)
770 exit(1);
771 }
772
773 /*
774 * Vacuum/analyze all connectable databases.
775 *
776 * In analyze-in-stages mode, we process all databases in one stage before
777 * moving on to the next stage. That ensure minimal stats are available
778 * quickly everywhere before generating more detailed ones.
779 */
780 static void
781 vacuum_all_databases(ConnParams *cparams,
782 vacuumingOptions *vacopts,
783 bool analyze_in_stages,
784 int concurrentCons,
785 const char *progname, bool echo, bool quiet)
786 {
787 PGconn *conn;
788 PGresult *result;
789 int stage;
790 int i;
791
792 conn = connectMaintenanceDatabase(cparams, progname, echo);
793 result = executeQuery(conn,
794 "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
795 echo);
796 PQfinish(conn);
797
798 if (analyze_in_stages)
799 {
800 /*
801 * When analyzing all databases in stages, we analyze them all in the
802 * fastest stage first, so that initial statistics become available
803 * for all of them as soon as possible.
804 *
805 * This means we establish several times as many connections, but
806 * that's a secondary consideration.
807 */
808 for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
809 {
810 for (i = 0; i < PQntuples(result); i++)
811 {
812 cparams->override_dbname = PQgetvalue(result, i, 0);
813
814 vacuum_one_database(cparams, vacopts,
815 stage,
816 NULL,
817 concurrentCons,
818 progname, echo, quiet);
819 }
820 }
821 }
822 else
823 {
824 for (i = 0; i < PQntuples(result); i++)
825 {
826 cparams->override_dbname = PQgetvalue(result, i, 0);
827
828 vacuum_one_database(cparams, vacopts,
829 ANALYZE_NO_STAGE,
830 NULL,
831 concurrentCons,
832 progname, echo, quiet);
833 }
834 }
835
836 PQclear(result);
837 }
838
839 /*
840 * Construct a vacuum/analyze command to run based on the given options, in the
841 * given string buffer, which may contain previous garbage.
842 *
843 * The table name used must be already properly quoted. The command generated
844 * depends on the server version involved and it is semicolon-terminated.
845 */
846 static void
847 prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
848 vacuumingOptions *vacopts, const char *table)
849 {
850 const char *paren = " (";
851 const char *comma = ", ";
852 const char *sep = paren;
853
854 resetPQExpBuffer(sql);
855
856 if (vacopts->analyze_only)
857 {
858 appendPQExpBufferStr(sql, "ANALYZE");
859
860 /* parenthesized grammar of ANALYZE is supported since v11 */
861 if (serverVersion >= 110000)
862 {
863 if (vacopts->skip_locked)
864 {
865 /* SKIP_LOCKED is supported since v12 */
866 Assert(serverVersion >= 120000);
867 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
868 sep = comma;
869 }
870 if (vacopts->verbose)
871 {
872 appendPQExpBuffer(sql, "%sVERBOSE", sep);
873 sep = comma;
874 }
875 if (sep != paren)
876 appendPQExpBufferChar(sql, ')');
877 }
878 else
879 {
880 if (vacopts->verbose)
881 appendPQExpBufferStr(sql, " VERBOSE");
882 }
883 }
884 else
885 {
886 appendPQExpBufferStr(sql, "VACUUM");
887
888 /* parenthesized grammar of VACUUM is supported since v9.0 */
889 if (serverVersion >= 90000)
890 {
891 if (vacopts->disable_page_skipping)
892 {
893 /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
894 Assert(serverVersion >= 90600);
895 appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
896 sep = comma;
897 }
898 if (vacopts->no_index_cleanup)
899 {
900 /* "INDEX_CLEANUP FALSE" has been supported since v12 */
901 Assert(serverVersion >= 120000);
902 Assert(!vacopts->force_index_cleanup);
903 appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
904 sep = comma;
905 }
906 if (vacopts->force_index_cleanup)
907 {
908 /* "INDEX_CLEANUP TRUE" has been supported since v12 */
909 Assert(serverVersion >= 120000);
910 Assert(!vacopts->no_index_cleanup);
911 appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
912 sep = comma;
913 }
914 if (!vacopts->do_truncate)
915 {
916 /* TRUNCATE is supported since v12 */
917 Assert(serverVersion >= 120000);
918 appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
919 sep = comma;
920 }
921 if (!vacopts->process_toast)
922 {
923 /* PROCESS_TOAST is supported since v14 */
924 Assert(serverVersion >= 140000);
925 appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
926 sep = comma;
927 }
928 if (vacopts->skip_locked)
929 {
930 /* SKIP_LOCKED is supported since v12 */
931 Assert(serverVersion >= 120000);
932 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
933 sep = comma;
934 }
935 if (vacopts->full)
936 {
937 appendPQExpBuffer(sql, "%sFULL", sep);
938 sep = comma;
939 }
940 if (vacopts->freeze)
941 {
942 appendPQExpBuffer(sql, "%sFREEZE", sep);
943 sep = comma;
944 }
945 if (vacopts->verbose)
946 {
947 appendPQExpBuffer(sql, "%sVERBOSE", sep);
948 sep = comma;
949 }
950 if (vacopts->and_analyze)
951 {
952 appendPQExpBuffer(sql, "%sANALYZE", sep);
953 sep = comma;
954 }
955 if (vacopts->parallel_workers >= 0)
956 {
957 /* PARALLEL is supported since v13 */
958 Assert(serverVersion >= 130000);
959 appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
960 vacopts->parallel_workers);
961 sep = comma;
962 }
963 if (sep != paren)
964 appendPQExpBufferChar(sql, ')');
965 }
966 else
967 {
968 if (vacopts->full)
969 appendPQExpBufferStr(sql, " FULL");
970 if (vacopts->freeze)
971 appendPQExpBufferStr(sql, " FREEZE");
972 if (vacopts->verbose)
973 appendPQExpBufferStr(sql, " VERBOSE");
974 if (vacopts->and_analyze)
975 appendPQExpBufferStr(sql, " ANALYZE");
976 }
977 }
978
979 appendPQExpBuffer(sql, " %s;", table);
980 }
981
982 /*
983 * Send a vacuum/analyze command to the server, returning after sending the
984 * command.
985 *
986 * Any errors during command execution are reported to stderr.
987 */
988 static void
989 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
990 const char *table)
991 {
992 bool status;
993
994 if (echo)
995 printf("%s\n", sql);
996
997 status = PQsendQuery(conn, sql) == 1;
998
999 if (!status)
1000 {
1001 if (table)
1002 pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1003 table, PQdb(conn), PQerrorMessage(conn));
1004 else
1005 pg_log_error("vacuuming of database \"%s\" failed: %s",
1006 PQdb(conn), PQerrorMessage(conn));
1007 }
1008 }
1009
1010 static void
1011 help(const char *progname)
1012 {
1013 printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1014 printf(_("Usage:\n"));
1015 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1016 printf(_("\nOptions:\n"));
1017 printf(_(" -a, --all vacuum all databases\n"));
1018 printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1019 printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1020 printf(_(" -e, --echo show the commands being sent to the server\n"));
1021 printf(_(" -f, --full do full vacuuming\n"));
1022 printf(_(" -F, --freeze freeze row transaction information\n"));
1023 printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1024 printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1025 printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1026 printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1027 printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1028 printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1029 printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1030 printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1031 printf(_(" -q, --quiet don't write any messages\n"));
1032 printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1033 printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1034 printf(_(" -v, --verbose write a lot of output\n"));
1035 printf(_(" -V, --version output version information, then exit\n"));
1036 printf(_(" -z, --analyze update optimizer statistics\n"));
1037 printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1038 printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1039 " stages for faster results; no vacuum\n"));
1040 printf(_(" -?, --help show this help, then exit\n"));
1041 printf(_("\nConnection options:\n"));
1042 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1043 printf(_(" -p, --port=PORT database server port\n"));
1044 printf(_(" -U, --username=USERNAME user name to connect as\n"));
1045 printf(_(" -w, --no-password never prompt for password\n"));
1046 printf(_(" -W, --password force password prompt\n"));
1047 printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1048 printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1049 printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1050 printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1051 }
Postgresql Clone from git://git.postgresql.org/git/postgresql.git