Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * vacuumdb
4 : *
5 : * Portions Copyright (c) 1996-2025, 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_main;
47 : bool process_toast;
48 : bool skip_database_stats;
49 : char *buffer_usage_limit;
50 : bool missing_stats_only;
51 : } vacuumingOptions;
52 :
53 : /* object filter options */
54 : typedef enum
55 : {
56 : OBJFILTER_NONE = 0, /* no filter used */
57 : OBJFILTER_ALL_DBS = (1 << 0), /* -a | --all */
58 : OBJFILTER_DATABASE = (1 << 1), /* -d | --dbname */
59 : OBJFILTER_TABLE = (1 << 2), /* -t | --table */
60 : OBJFILTER_SCHEMA = (1 << 3), /* -n | --schema */
61 : OBJFILTER_SCHEMA_EXCLUDE = (1 << 4), /* -N | --exclude-schema */
62 : } VacObjFilter;
63 :
64 : static VacObjFilter objfilter = OBJFILTER_NONE;
65 :
66 : static SimpleStringList *retrieve_objects(PGconn *conn,
67 : vacuumingOptions *vacopts,
68 : SimpleStringList *objects,
69 : bool echo);
70 :
71 : static void vacuum_one_database(ConnParams *cparams,
72 : vacuumingOptions *vacopts,
73 : int stage,
74 : SimpleStringList *objects,
75 : SimpleStringList **found_objs,
76 : int concurrentCons,
77 : const char *progname, bool echo, bool quiet);
78 :
79 : static void vacuum_all_databases(ConnParams *cparams,
80 : vacuumingOptions *vacopts,
81 : bool analyze_in_stages,
82 : SimpleStringList *objects,
83 : int concurrentCons,
84 : const char *progname, bool echo, bool quiet);
85 :
86 : static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
87 : vacuumingOptions *vacopts, const char *table);
88 :
89 : static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
90 : const char *table);
91 :
92 : static void help(const char *progname);
93 :
94 : void check_objfilter(void);
95 :
96 : static char *escape_quotes(const char *src);
97 :
98 : /* For analyze-in-stages mode */
99 : #define ANALYZE_NO_STAGE -1
100 : #define ANALYZE_NUM_STAGES 3
101 :
102 :
103 : int
104 190 : main(int argc, char *argv[])
105 : {
106 : static struct option long_options[] = {
107 : {"host", required_argument, NULL, 'h'},
108 : {"port", required_argument, NULL, 'p'},
109 : {"username", required_argument, NULL, 'U'},
110 : {"no-password", no_argument, NULL, 'w'},
111 : {"password", no_argument, NULL, 'W'},
112 : {"echo", no_argument, NULL, 'e'},
113 : {"quiet", no_argument, NULL, 'q'},
114 : {"dbname", required_argument, NULL, 'd'},
115 : {"analyze", no_argument, NULL, 'z'},
116 : {"analyze-only", no_argument, NULL, 'Z'},
117 : {"freeze", no_argument, NULL, 'F'},
118 : {"all", no_argument, NULL, 'a'},
119 : {"table", required_argument, NULL, 't'},
120 : {"full", no_argument, NULL, 'f'},
121 : {"verbose", no_argument, NULL, 'v'},
122 : {"jobs", required_argument, NULL, 'j'},
123 : {"parallel", required_argument, NULL, 'P'},
124 : {"schema", required_argument, NULL, 'n'},
125 : {"exclude-schema", required_argument, NULL, 'N'},
126 : {"maintenance-db", required_argument, NULL, 2},
127 : {"analyze-in-stages", no_argument, NULL, 3},
128 : {"disable-page-skipping", no_argument, NULL, 4},
129 : {"skip-locked", no_argument, NULL, 5},
130 : {"min-xid-age", required_argument, NULL, 6},
131 : {"min-mxid-age", required_argument, NULL, 7},
132 : {"no-index-cleanup", no_argument, NULL, 8},
133 : {"force-index-cleanup", no_argument, NULL, 9},
134 : {"no-truncate", no_argument, NULL, 10},
135 : {"no-process-toast", no_argument, NULL, 11},
136 : {"no-process-main", no_argument, NULL, 12},
137 : {"buffer-usage-limit", required_argument, NULL, 13},
138 : {"missing-stats-only", no_argument, NULL, 14},
139 : {NULL, 0, NULL, 0}
140 : };
141 :
142 : const char *progname;
143 : int optindex;
144 : int c;
145 190 : const char *dbname = NULL;
146 190 : const char *maintenance_db = NULL;
147 190 : char *host = NULL;
148 190 : char *port = NULL;
149 190 : char *username = NULL;
150 190 : enum trivalue prompt_password = TRI_DEFAULT;
151 : ConnParams cparams;
152 190 : bool echo = false;
153 190 : bool quiet = false;
154 : vacuumingOptions vacopts;
155 190 : bool analyze_in_stages = false;
156 190 : SimpleStringList objects = {NULL, NULL};
157 190 : int concurrentCons = 1;
158 190 : int tbl_count = 0;
159 :
160 : /* initialize options */
161 190 : memset(&vacopts, 0, sizeof(vacopts));
162 190 : vacopts.parallel_workers = -1;
163 190 : vacopts.buffer_usage_limit = NULL;
164 190 : vacopts.no_index_cleanup = false;
165 190 : vacopts.force_index_cleanup = false;
166 190 : vacopts.do_truncate = true;
167 190 : vacopts.process_main = true;
168 190 : vacopts.process_toast = true;
169 :
170 190 : pg_logging_init(argv[0]);
171 190 : progname = get_progname(argv[0]);
172 190 : set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
173 :
174 190 : handle_help_version_opts(argc, argv, "vacuumdb", help);
175 :
176 526 : while ((c = getopt_long(argc, argv, "ad:efFh:j:n:N:p:P:qt:U:vwWzZ", long_options, &optindex)) != -1)
177 : {
178 380 : switch (c)
179 : {
180 50 : case 'a':
181 50 : objfilter |= OBJFILTER_ALL_DBS;
182 50 : break;
183 4 : case 'd':
184 4 : objfilter |= OBJFILTER_DATABASE;
185 4 : dbname = pg_strdup(optarg);
186 4 : break;
187 2 : case 'e':
188 2 : echo = true;
189 2 : break;
190 2 : case 'f':
191 2 : vacopts.full = true;
192 2 : break;
193 18 : case 'F':
194 18 : vacopts.freeze = true;
195 18 : break;
196 32 : case 'h':
197 32 : host = pg_strdup(optarg);
198 32 : break;
199 2 : case 'j':
200 2 : if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
201 : &concurrentCons))
202 0 : exit(1);
203 2 : break;
204 12 : case 'n':
205 12 : objfilter |= OBJFILTER_SCHEMA;
206 12 : simple_string_list_append(&objects, optarg);
207 12 : break;
208 12 : case 'N':
209 12 : objfilter |= OBJFILTER_SCHEMA_EXCLUDE;
210 12 : simple_string_list_append(&objects, optarg);
211 12 : break;
212 32 : case 'p':
213 32 : port = pg_strdup(optarg);
214 32 : break;
215 6 : case 'P':
216 6 : if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
217 : &vacopts.parallel_workers))
218 2 : exit(1);
219 4 : break;
220 0 : case 'q':
221 0 : quiet = true;
222 0 : break;
223 48 : case 't':
224 48 : objfilter |= OBJFILTER_TABLE;
225 48 : simple_string_list_append(&objects, optarg);
226 48 : tbl_count++;
227 48 : break;
228 32 : case 'U':
229 32 : username = pg_strdup(optarg);
230 32 : break;
231 0 : case 'v':
232 0 : vacopts.verbose = true;
233 0 : break;
234 0 : case 'w':
235 0 : prompt_password = TRI_NO;
236 0 : break;
237 0 : case 'W':
238 0 : prompt_password = TRI_YES;
239 0 : break;
240 24 : case 'z':
241 24 : vacopts.and_analyze = true;
242 24 : break;
243 38 : case 'Z':
244 38 : vacopts.analyze_only = true;
245 38 : break;
246 0 : case 2:
247 0 : maintenance_db = pg_strdup(optarg);
248 0 : break;
249 12 : case 3:
250 12 : analyze_in_stages = vacopts.analyze_only = true;
251 12 : break;
252 4 : case 4:
253 4 : vacopts.disable_page_skipping = true;
254 4 : break;
255 4 : case 5:
256 4 : vacopts.skip_locked = true;
257 4 : break;
258 4 : case 6:
259 4 : if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
260 : &vacopts.min_xid_age))
261 2 : exit(1);
262 2 : break;
263 4 : case 7:
264 4 : if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
265 : &vacopts.min_mxid_age))
266 2 : exit(1);
267 2 : break;
268 4 : case 8:
269 4 : vacopts.no_index_cleanup = true;
270 4 : break;
271 0 : case 9:
272 0 : vacopts.force_index_cleanup = true;
273 0 : break;
274 4 : case 10:
275 4 : vacopts.do_truncate = false;
276 4 : break;
277 4 : case 11:
278 4 : vacopts.process_toast = false;
279 4 : break;
280 4 : case 12:
281 4 : vacopts.process_main = false;
282 4 : break;
283 0 : case 13:
284 0 : vacopts.buffer_usage_limit = escape_quotes(optarg);
285 0 : break;
286 20 : case 14:
287 20 : vacopts.missing_stats_only = true;
288 20 : break;
289 2 : default:
290 : /* getopt_long already emitted a complaint */
291 2 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
292 2 : exit(1);
293 : }
294 : }
295 :
296 : /*
297 : * Non-option argument specifies database name as long as it wasn't
298 : * already specified with -d / --dbname
299 : */
300 146 : if (optind < argc && dbname == NULL)
301 : {
302 96 : objfilter |= OBJFILTER_DATABASE;
303 96 : dbname = argv[optind];
304 96 : optind++;
305 : }
306 :
307 146 : if (optind < argc)
308 : {
309 0 : pg_log_error("too many command-line arguments (first is \"%s\")",
310 : argv[optind]);
311 0 : pg_log_error_hint("Try \"%s --help\" for more information.", progname);
312 0 : exit(1);
313 : }
314 :
315 : /*
316 : * Validate the combination of filters specified in the command-line
317 : * options.
318 : */
319 146 : check_objfilter();
320 :
321 136 : if (vacopts.analyze_only)
322 : {
323 50 : if (vacopts.full)
324 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
325 : "full");
326 50 : if (vacopts.freeze)
327 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
328 : "freeze");
329 50 : if (vacopts.disable_page_skipping)
330 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
331 : "disable-page-skipping");
332 48 : if (vacopts.no_index_cleanup)
333 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
334 : "no-index-cleanup");
335 46 : if (vacopts.force_index_cleanup)
336 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
337 : "force-index-cleanup");
338 46 : if (!vacopts.do_truncate)
339 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
340 : "no-truncate");
341 44 : if (!vacopts.process_main)
342 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
343 : "no-process-main");
344 42 : if (!vacopts.process_toast)
345 2 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
346 : "no-process-toast");
347 : /* allow 'and_analyze' with 'analyze_only' */
348 : }
349 :
350 : /* Prohibit full and analyze_only options with parallel option */
351 126 : if (vacopts.parallel_workers >= 0)
352 : {
353 4 : if (vacopts.analyze_only)
354 0 : pg_fatal("cannot use the \"%s\" option when performing only analyze",
355 : "parallel");
356 4 : if (vacopts.full)
357 0 : pg_fatal("cannot use the \"%s\" option when performing full vacuum",
358 : "parallel");
359 : }
360 :
361 : /* Prohibit --no-index-cleanup and --force-index-cleanup together */
362 126 : if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
363 0 : pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
364 : "no-index-cleanup", "force-index-cleanup");
365 :
366 : /*
367 : * buffer-usage-limit is not allowed with VACUUM FULL unless ANALYZE is
368 : * included too.
369 : */
370 126 : if (vacopts.buffer_usage_limit && vacopts.full && !vacopts.and_analyze)
371 0 : pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
372 : "buffer-usage-limit", "full");
373 :
374 : /*
375 : * Prohibit --missing-stats-only without --analyze-only or
376 : * --analyze-in-stages.
377 : */
378 126 : if (vacopts.missing_stats_only && !vacopts.analyze_only)
379 0 : pg_fatal("cannot use the \"%s\" option without \"%s\" or \"%s\"",
380 : "missing-stats-only", "analyze-only", "analyze-in-stages");
381 :
382 : /* fill cparams except for dbname, which is set below */
383 126 : cparams.pghost = host;
384 126 : cparams.pgport = port;
385 126 : cparams.pguser = username;
386 126 : cparams.prompt_password = prompt_password;
387 126 : cparams.override_dbname = NULL;
388 :
389 126 : setup_cancel_handler(NULL);
390 :
391 : /* Avoid opening extra connections. */
392 126 : if (tbl_count && (concurrentCons > tbl_count))
393 0 : concurrentCons = tbl_count;
394 :
395 126 : if (objfilter & OBJFILTER_ALL_DBS)
396 : {
397 46 : cparams.dbname = maintenance_db;
398 :
399 46 : vacuum_all_databases(&cparams, &vacopts,
400 : analyze_in_stages,
401 : &objects,
402 : concurrentCons,
403 : progname, echo, quiet);
404 : }
405 : else
406 : {
407 80 : if (dbname == NULL)
408 : {
409 0 : if (getenv("PGDATABASE"))
410 0 : dbname = getenv("PGDATABASE");
411 0 : else if (getenv("PGUSER"))
412 0 : dbname = getenv("PGUSER");
413 : else
414 0 : dbname = get_user_name_or_exit(progname);
415 : }
416 :
417 80 : cparams.dbname = dbname;
418 :
419 80 : if (analyze_in_stages)
420 : {
421 : int stage;
422 10 : SimpleStringList *found_objs = NULL;
423 :
424 40 : for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
425 : {
426 30 : vacuum_one_database(&cparams, &vacopts,
427 : stage,
428 : &objects,
429 30 : vacopts.missing_stats_only ? &found_objs : NULL,
430 : concurrentCons,
431 : progname, echo, quiet);
432 : }
433 : }
434 : else
435 70 : vacuum_one_database(&cparams, &vacopts,
436 : ANALYZE_NO_STAGE,
437 : &objects, NULL,
438 : concurrentCons,
439 : progname, echo, quiet);
440 : }
441 :
442 120 : exit(0);
443 : }
444 :
445 : /*
446 : * Verify that the filters used at command line are compatible.
447 : */
448 : void
449 146 : check_objfilter(void)
450 : {
451 146 : if ((objfilter & OBJFILTER_ALL_DBS) &&
452 50 : (objfilter & OBJFILTER_DATABASE))
453 4 : pg_fatal("cannot vacuum all databases and a specific one at the same time");
454 :
455 142 : if ((objfilter & OBJFILTER_TABLE) &&
456 44 : (objfilter & OBJFILTER_SCHEMA))
457 2 : pg_fatal("cannot vacuum all tables in schema(s) and specific table(s) at the same time");
458 :
459 140 : if ((objfilter & OBJFILTER_TABLE) &&
460 42 : (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
461 2 : pg_fatal("cannot vacuum specific table(s) and exclude schema(s) at the same time");
462 :
463 138 : if ((objfilter & OBJFILTER_SCHEMA) &&
464 8 : (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
465 2 : pg_fatal("cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
466 136 : }
467 :
468 : /*
469 : * Returns a newly malloc'd version of 'src' with escaped single quotes and
470 : * backslashes.
471 : */
472 : static char *
473 0 : escape_quotes(const char *src)
474 : {
475 0 : char *result = escape_single_quotes_ascii(src);
476 :
477 0 : if (!result)
478 0 : pg_fatal("out of memory");
479 0 : return result;
480 : }
481 :
482 : /*
483 : * vacuum_one_database
484 : *
485 : * Process tables in the given database.
486 : *
487 : * There are two ways to specify the list of objects to process:
488 : *
489 : * 1) The "found_objs" parameter is a double pointer to a fully qualified list
490 : * of objects to process, as returned by a previous call to
491 : * vacuum_one_database().
492 : *
493 : * a) If both "found_objs" (the double pointer) and "*found_objs" (the
494 : * once-dereferenced double pointer) are not NULL, this list takes
495 : * priority, and anything specified in "objects" is ignored.
496 : *
497 : * b) If "found_objs" (the double pointer) is not NULL but "*found_objs"
498 : * (the once-dereferenced double pointer) _is_ NULL, the "objects"
499 : * parameter takes priority, and the results of the catalog query
500 : * described in (2) are stored in "found_objs".
501 : *
502 : * c) If "found_objs" (the double pointer) is NULL, the "objects"
503 : * parameter again takes priority, and the results of the catalog query
504 : * are not saved.
505 : *
506 : * 2) The "objects" parameter is a user-specified list of objects to process.
507 : * When (1b) or (1c) applies, this function performs a catalog query to
508 : * retrieve a fully qualified list of objects to process, as described
509 : * below.
510 : *
511 : * a) If "objects" is not NULL, the catalog query gathers only the objects
512 : * listed in "objects".
513 : *
514 : * b) If "objects" is NULL, all tables in the database are gathered.
515 : *
516 : * Note that this function is only concerned with running exactly one stage
517 : * when in analyze-in-stages mode; caller must iterate on us if necessary.
518 : *
519 : * If concurrentCons is > 1, multiple connections are used to vacuum tables
520 : * in parallel.
521 : */
522 : static void
523 210 : vacuum_one_database(ConnParams *cparams,
524 : vacuumingOptions *vacopts,
525 : int stage,
526 : SimpleStringList *objects,
527 : SimpleStringList **found_objs,
528 : int concurrentCons,
529 : const char *progname, bool echo, bool quiet)
530 : {
531 : PQExpBufferData sql;
532 : PGconn *conn;
533 : SimpleStringListCell *cell;
534 : ParallelSlotArray *sa;
535 210 : int ntups = 0;
536 210 : bool failed = false;
537 : const char *initcmd;
538 210 : SimpleStringList *ret = NULL;
539 210 : const char *stage_commands[] = {
540 : "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
541 : "SET default_statistics_target=10; RESET vacuum_cost_delay;",
542 : "RESET default_statistics_target;"
543 : };
544 210 : const char *stage_messages[] = {
545 : gettext_noop("Generating minimal optimizer statistics (1 target)"),
546 : gettext_noop("Generating medium optimizer statistics (10 targets)"),
547 : gettext_noop("Generating default (full) optimizer statistics")
548 : };
549 :
550 : Assert(stage == ANALYZE_NO_STAGE ||
551 : (stage >= 0 && stage < ANALYZE_NUM_STAGES));
552 :
553 210 : conn = connectDatabase(cparams, progname, echo, false, true);
554 :
555 208 : if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
556 : {
557 0 : PQfinish(conn);
558 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
559 : "disable-page-skipping", "9.6");
560 : }
561 :
562 208 : if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
563 : {
564 0 : PQfinish(conn);
565 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
566 : "no-index-cleanup", "12");
567 : }
568 :
569 208 : if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
570 : {
571 0 : PQfinish(conn);
572 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
573 : "force-index-cleanup", "12");
574 : }
575 :
576 208 : if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
577 : {
578 0 : PQfinish(conn);
579 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
580 : "no-truncate", "12");
581 : }
582 :
583 208 : if (!vacopts->process_main && PQserverVersion(conn) < 160000)
584 : {
585 0 : PQfinish(conn);
586 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
587 : "no-process-main", "16");
588 : }
589 :
590 208 : if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
591 : {
592 0 : PQfinish(conn);
593 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
594 : "no-process-toast", "14");
595 : }
596 :
597 208 : if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
598 : {
599 0 : PQfinish(conn);
600 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
601 : "skip-locked", "12");
602 : }
603 :
604 208 : if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
605 : {
606 0 : PQfinish(conn);
607 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
608 : "--min-xid-age", "9.6");
609 : }
610 :
611 208 : if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
612 : {
613 0 : PQfinish(conn);
614 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
615 : "--min-mxid-age", "9.6");
616 : }
617 :
618 208 : if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
619 : {
620 0 : PQfinish(conn);
621 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
622 : "--parallel", "13");
623 : }
624 :
625 208 : if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
626 : {
627 0 : PQfinish(conn);
628 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
629 : "--buffer-usage-limit", "16");
630 : }
631 :
632 208 : if (vacopts->missing_stats_only && PQserverVersion(conn) < 150000)
633 : {
634 0 : PQfinish(conn);
635 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
636 : "--missing-stats-only", "15");
637 : }
638 :
639 : /* skip_database_stats is used automatically if server supports it */
640 208 : vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
641 :
642 208 : if (!quiet)
643 : {
644 208 : if (stage != ANALYZE_NO_STAGE)
645 42 : printf(_("%s: processing database \"%s\": %s\n"),
646 : progname, PQdb(conn), _(stage_messages[stage]));
647 : else
648 166 : printf(_("%s: vacuuming database \"%s\"\n"),
649 : progname, PQdb(conn));
650 208 : fflush(stdout);
651 : }
652 :
653 : /*
654 : * If the caller provided the results of a previous catalog query, just
655 : * use that. Otherwise, run the catalog query ourselves and set the
656 : * return variable if provided.
657 : */
658 208 : if (found_objs && *found_objs)
659 16 : ret = *found_objs;
660 : else
661 : {
662 192 : ret = retrieve_objects(conn, vacopts, objects, echo);
663 190 : if (found_objs)
664 8 : *found_objs = ret;
665 : }
666 :
667 : /*
668 : * Count the number of objects in the catalog query result. If there are
669 : * none, we are done.
670 : */
671 9792 : for (cell = ret ? ret->head : NULL; cell; cell = cell->next)
672 9586 : ntups++;
673 :
674 206 : if (ntups == 0)
675 : {
676 22 : PQfinish(conn);
677 22 : return;
678 : }
679 :
680 : /*
681 : * Ensure concurrentCons is sane. If there are more connections than
682 : * vacuumable relations, we don't need to use them all.
683 : */
684 184 : if (concurrentCons > ntups)
685 0 : concurrentCons = ntups;
686 184 : if (concurrentCons <= 0)
687 0 : concurrentCons = 1;
688 :
689 : /*
690 : * All slots need to be prepared to run the appropriate analyze stage, if
691 : * caller requested that mode. We have to prepare the initial connection
692 : * ourselves before setting up the slots.
693 : */
694 184 : if (stage == ANALYZE_NO_STAGE)
695 154 : initcmd = NULL;
696 : else
697 : {
698 30 : initcmd = stage_commands[stage];
699 30 : executeCommand(conn, initcmd, echo);
700 : }
701 :
702 : /*
703 : * Setup the database connections. We reuse the connection we already have
704 : * for the first slot. If not in parallel mode, the first slot in the
705 : * array contains the connection.
706 : */
707 184 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
708 184 : ParallelSlotsAdoptConn(sa, conn);
709 :
710 184 : initPQExpBuffer(&sql);
711 :
712 184 : cell = ret->head;
713 : do
714 : {
715 9586 : const char *tabname = cell->val;
716 : ParallelSlot *free_slot;
717 :
718 9586 : if (CancelRequested)
719 : {
720 0 : failed = true;
721 0 : goto finish;
722 : }
723 :
724 9586 : free_slot = ParallelSlotsGetIdle(sa, NULL);
725 9586 : if (!free_slot)
726 : {
727 0 : failed = true;
728 0 : goto finish;
729 : }
730 :
731 9586 : prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection),
732 : vacopts, tabname);
733 :
734 : /*
735 : * Execute the vacuum. All errors are handled in processQueryResult
736 : * through ParallelSlotsGetIdle.
737 : */
738 9586 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
739 9586 : run_vacuum_command(free_slot->connection, sql.data,
740 : echo, tabname);
741 :
742 9586 : cell = cell->next;
743 9586 : } while (cell != NULL);
744 :
745 184 : if (!ParallelSlotsWaitCompletion(sa))
746 : {
747 2 : failed = true;
748 2 : goto finish;
749 : }
750 :
751 : /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
752 182 : if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE)
753 : {
754 152 : const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
755 152 : ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
756 :
757 152 : if (!free_slot)
758 : {
759 0 : failed = true;
760 0 : goto finish;
761 : }
762 :
763 152 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
764 152 : run_vacuum_command(free_slot->connection, cmd, echo, NULL);
765 :
766 152 : if (!ParallelSlotsWaitCompletion(sa))
767 0 : failed = true;
768 : }
769 :
770 182 : finish:
771 184 : ParallelSlotsTerminate(sa);
772 184 : pg_free(sa);
773 :
774 184 : termPQExpBuffer(&sql);
775 :
776 184 : if (failed)
777 2 : exit(1);
778 : }
779 :
780 : /*
781 : * Prepare the list of tables to process by querying the catalogs.
782 : *
783 : * Since we execute the constructed query with the default search_path (which
784 : * could be unsafe), everything in this query MUST be fully qualified.
785 : *
786 : * First, build a WITH clause for the catalog query if any tables were
787 : * specified, with a set of values made of relation names and their optional
788 : * set of columns. This is used to match any provided column lists with the
789 : * generated qualified identifiers and to filter for the tables provided via
790 : * --table. If a listed table does not exist, the catalog query will fail.
791 : */
792 : static SimpleStringList *
793 192 : retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
794 : SimpleStringList *objects, bool echo)
795 : {
796 : PQExpBufferData buf;
797 : PQExpBufferData catalog_query;
798 : PGresult *res;
799 : SimpleStringListCell *cell;
800 192 : SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList));
801 192 : bool objects_listed = false;
802 :
803 192 : initPQExpBuffer(&catalog_query);
804 254 : for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
805 : {
806 62 : char *just_table = NULL;
807 62 : const char *just_columns = NULL;
808 :
809 62 : if (!objects_listed)
810 : {
811 58 : appendPQExpBufferStr(&catalog_query,
812 : "WITH listed_objects (object_oid, column_list) "
813 : "AS (\n VALUES (");
814 58 : objects_listed = true;
815 : }
816 : else
817 4 : appendPQExpBufferStr(&catalog_query, ",\n (");
818 :
819 62 : if (objfilter & (OBJFILTER_SCHEMA | OBJFILTER_SCHEMA_EXCLUDE))
820 : {
821 20 : appendStringLiteralConn(&catalog_query, cell->val, conn);
822 20 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
823 : }
824 :
825 62 : if (objfilter & OBJFILTER_TABLE)
826 : {
827 : /*
828 : * Split relation and column names given by the user, this is used
829 : * to feed the CTE with values on which are performed pre-run
830 : * validity checks as well. For now these happen only on the
831 : * relation name.
832 : */
833 42 : splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
834 : &just_table, &just_columns);
835 :
836 42 : appendStringLiteralConn(&catalog_query, just_table, conn);
837 42 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
838 : }
839 :
840 62 : if (just_columns && just_columns[0] != '\0')
841 10 : appendStringLiteralConn(&catalog_query, just_columns, conn);
842 : else
843 52 : appendPQExpBufferStr(&catalog_query, "NULL");
844 :
845 62 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
846 :
847 62 : pg_free(just_table);
848 : }
849 :
850 : /* Finish formatting the CTE */
851 192 : if (objects_listed)
852 58 : appendPQExpBufferStr(&catalog_query, "\n)\n");
853 :
854 192 : appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
855 :
856 192 : if (objects_listed)
857 58 : appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
858 :
859 192 : appendPQExpBufferStr(&catalog_query,
860 : " FROM pg_catalog.pg_class c\n"
861 : " JOIN pg_catalog.pg_namespace ns"
862 : " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
863 : " CROSS JOIN LATERAL (SELECT c.relkind IN ("
864 : CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
865 : CppAsString2(RELKIND_PARTITIONED_INDEX) ")) as p (inherited)\n"
866 : " LEFT JOIN pg_catalog.pg_class t"
867 : " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
868 :
869 : /*
870 : * Used to match the tables or schemas listed by the user, completing the
871 : * JOIN clause.
872 : */
873 192 : if (objects_listed)
874 : {
875 58 : appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
876 : " ON listed_objects.object_oid"
877 : " OPERATOR(pg_catalog.=) ");
878 :
879 58 : if (objfilter & OBJFILTER_TABLE)
880 42 : appendPQExpBufferStr(&catalog_query, "c.oid\n");
881 : else
882 16 : appendPQExpBufferStr(&catalog_query, "ns.oid\n");
883 : }
884 :
885 : /*
886 : * Exclude temporary tables, beginning the WHERE clause.
887 : */
888 192 : appendPQExpBufferStr(&catalog_query,
889 : " WHERE c.relpersistence OPERATOR(pg_catalog.!=) "
890 : CppAsString2(RELPERSISTENCE_TEMP) "\n");
891 :
892 : /*
893 : * Used to match the tables or schemas listed by the user, for the WHERE
894 : * clause.
895 : */
896 192 : if (objects_listed)
897 : {
898 58 : if (objfilter & OBJFILTER_SCHEMA_EXCLUDE)
899 8 : appendPQExpBuffer(&catalog_query,
900 : " AND listed_objects.object_oid IS NULL\n");
901 : else
902 50 : appendPQExpBuffer(&catalog_query,
903 : " AND listed_objects.object_oid IS NOT NULL\n");
904 : }
905 :
906 : /*
907 : * If no tables were listed, filter for the relevant relation types. If
908 : * tables were given via --table, don't bother filtering by relation type.
909 : * Instead, let the server decide whether a given relation can be
910 : * processed in which case the user will know about it.
911 : */
912 192 : if ((objfilter & OBJFILTER_TABLE) == 0)
913 : {
914 150 : appendPQExpBuffer(&catalog_query,
915 : " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
916 : CppAsString2(RELKIND_RELATION) ", "
917 : CppAsString2(RELKIND_MATVIEW) "])\n");
918 : }
919 :
920 : /*
921 : * For --min-xid-age and --min-mxid-age, the age of the relation is the
922 : * greatest of the ages of the main relation and its associated TOAST
923 : * table. The commands generated by vacuumdb will also process the TOAST
924 : * table for the relation if necessary, so it does not need to be
925 : * considered separately.
926 : */
927 192 : if (vacopts->min_xid_age != 0)
928 : {
929 2 : appendPQExpBuffer(&catalog_query,
930 : " AND GREATEST(pg_catalog.age(c.relfrozenxid),"
931 : " pg_catalog.age(t.relfrozenxid)) "
932 : " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
933 : " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
934 : " '0'::pg_catalog.xid\n",
935 : vacopts->min_xid_age);
936 : }
937 :
938 192 : if (vacopts->min_mxid_age != 0)
939 : {
940 2 : appendPQExpBuffer(&catalog_query,
941 : " AND GREATEST(pg_catalog.mxid_age(c.relminmxid),"
942 : " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
943 : " '%d'::pg_catalog.int4\n"
944 : " AND c.relminmxid OPERATOR(pg_catalog.!=)"
945 : " '0'::pg_catalog.xid\n",
946 : vacopts->min_mxid_age);
947 : }
948 :
949 192 : if (vacopts->missing_stats_only)
950 : {
951 20 : appendPQExpBufferStr(&catalog_query, " AND (\n");
952 :
953 : /* regular stats */
954 20 : appendPQExpBufferStr(&catalog_query,
955 : " EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
956 : " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
957 : " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
958 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
959 : " AND NOT a.attisdropped\n"
960 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
961 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
962 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
963 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
964 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
965 :
966 : /* extended stats */
967 20 : appendPQExpBufferStr(&catalog_query,
968 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
969 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
970 : " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
971 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
972 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
973 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
974 : " AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n");
975 :
976 : /* expression indexes */
977 20 : appendPQExpBufferStr(&catalog_query,
978 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
979 : " JOIN pg_catalog.pg_index i"
980 : " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n"
981 : " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
982 : " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
983 : " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]"
984 : " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n"
985 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
986 : " AND NOT a.attisdropped\n"
987 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
988 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
989 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
990 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
991 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
992 :
993 : /* inheritance and regular stats */
994 20 : appendPQExpBufferStr(&catalog_query,
995 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
996 : " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
997 : " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
998 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
999 : " AND NOT a.attisdropped\n"
1000 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
1001 : " AND c.relhassubclass\n"
1002 : " AND NOT p.inherited\n"
1003 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1004 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1005 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
1006 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
1007 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
1008 : " AND s.stainherit))\n");
1009 :
1010 : /* inheritance and extended stats */
1011 20 : appendPQExpBufferStr(&catalog_query,
1012 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
1013 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
1014 : " AND c.reltuples OPERATOR(pg_catalog.!=) 0::pg_catalog.float4\n"
1015 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
1016 : " AND c.relhassubclass\n"
1017 : " AND NOT p.inherited\n"
1018 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1019 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1020 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
1021 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
1022 : " AND d.stxdinherit))\n");
1023 :
1024 20 : appendPQExpBufferStr(&catalog_query, " )\n");
1025 : }
1026 :
1027 : /*
1028 : * Execute the catalog query. We use the default search_path for this
1029 : * query for consistency with table lookups done elsewhere by the user.
1030 : */
1031 192 : appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
1032 192 : executeCommand(conn, "RESET search_path;", echo);
1033 192 : res = executeQuery(conn, catalog_query.data, echo);
1034 190 : termPQExpBuffer(&catalog_query);
1035 190 : PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
1036 :
1037 : /*
1038 : * Build qualified identifiers for each table, including the column list
1039 : * if given.
1040 : */
1041 190 : initPQExpBuffer(&buf);
1042 9768 : for (int i = 0; i < PQntuples(res); i++)
1043 : {
1044 19156 : appendPQExpBufferStr(&buf,
1045 9578 : fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
1046 9578 : PQgetvalue(res, i, 0),
1047 : PQclientEncoding(conn)));
1048 :
1049 9578 : if (objects_listed && !PQgetisnull(res, i, 2))
1050 10 : appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
1051 :
1052 9578 : simple_string_list_append(found_objs, buf.data);
1053 9578 : resetPQExpBuffer(&buf);
1054 : }
1055 190 : termPQExpBuffer(&buf);
1056 190 : PQclear(res);
1057 :
1058 190 : return found_objs;
1059 : }
1060 :
1061 : /*
1062 : * Vacuum/analyze all connectable databases.
1063 : *
1064 : * In analyze-in-stages mode, we process all databases in one stage before
1065 : * moving on to the next stage. That ensure minimal stats are available
1066 : * quickly everywhere before generating more detailed ones.
1067 : */
1068 : static void
1069 46 : vacuum_all_databases(ConnParams *cparams,
1070 : vacuumingOptions *vacopts,
1071 : bool analyze_in_stages,
1072 : SimpleStringList *objects,
1073 : int concurrentCons,
1074 : const char *progname, bool echo, bool quiet)
1075 : {
1076 : PGconn *conn;
1077 : PGresult *result;
1078 : int stage;
1079 : int i;
1080 :
1081 46 : conn = connectMaintenanceDatabase(cparams, progname, echo);
1082 46 : result = executeQuery(conn,
1083 : "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
1084 : echo);
1085 46 : PQfinish(conn);
1086 :
1087 46 : if (analyze_in_stages)
1088 : {
1089 2 : SimpleStringList **found_objs = NULL;
1090 :
1091 2 : if (vacopts->missing_stats_only)
1092 0 : found_objs = palloc0(PQntuples(result) * sizeof(SimpleStringList *));
1093 :
1094 : /*
1095 : * When analyzing all databases in stages, we analyze them all in the
1096 : * fastest stage first, so that initial statistics become available
1097 : * for all of them as soon as possible.
1098 : *
1099 : * This means we establish several times as many connections, but
1100 : * that's a secondary consideration.
1101 : */
1102 8 : for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
1103 : {
1104 18 : for (i = 0; i < PQntuples(result); i++)
1105 : {
1106 12 : cparams->override_dbname = PQgetvalue(result, i, 0);
1107 :
1108 12 : vacuum_one_database(cparams, vacopts,
1109 : stage,
1110 : objects,
1111 12 : vacopts->missing_stats_only ? &found_objs[i] : NULL,
1112 : concurrentCons,
1113 : progname, echo, quiet);
1114 : }
1115 : }
1116 : }
1117 : else
1118 : {
1119 142 : for (i = 0; i < PQntuples(result); i++)
1120 : {
1121 98 : cparams->override_dbname = PQgetvalue(result, i, 0);
1122 :
1123 98 : vacuum_one_database(cparams, vacopts,
1124 : ANALYZE_NO_STAGE,
1125 : objects, NULL,
1126 : concurrentCons,
1127 : progname, echo, quiet);
1128 : }
1129 : }
1130 :
1131 46 : PQclear(result);
1132 46 : }
1133 :
1134 : /*
1135 : * Construct a vacuum/analyze command to run based on the given options, in the
1136 : * given string buffer, which may contain previous garbage.
1137 : *
1138 : * The table name used must be already properly quoted. The command generated
1139 : * depends on the server version involved and it is semicolon-terminated.
1140 : */
1141 : static void
1142 9586 : prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
1143 : vacuumingOptions *vacopts, const char *table)
1144 : {
1145 9586 : const char *paren = " (";
1146 9586 : const char *comma = ", ";
1147 9586 : const char *sep = paren;
1148 :
1149 9586 : resetPQExpBuffer(sql);
1150 :
1151 9586 : if (vacopts->analyze_only)
1152 : {
1153 2474 : appendPQExpBufferStr(sql, "ANALYZE");
1154 :
1155 : /* parenthesized grammar of ANALYZE is supported since v11 */
1156 2474 : if (serverVersion >= 110000)
1157 : {
1158 2474 : if (vacopts->skip_locked)
1159 : {
1160 : /* SKIP_LOCKED is supported since v12 */
1161 : Assert(serverVersion >= 120000);
1162 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1163 136 : sep = comma;
1164 : }
1165 2474 : if (vacopts->verbose)
1166 : {
1167 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
1168 0 : sep = comma;
1169 : }
1170 2474 : if (vacopts->buffer_usage_limit)
1171 : {
1172 : Assert(serverVersion >= 160000);
1173 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1174 : vacopts->buffer_usage_limit);
1175 0 : sep = comma;
1176 : }
1177 2474 : if (sep != paren)
1178 136 : appendPQExpBufferChar(sql, ')');
1179 : }
1180 : else
1181 : {
1182 0 : if (vacopts->verbose)
1183 0 : appendPQExpBufferStr(sql, " VERBOSE");
1184 : }
1185 : }
1186 : else
1187 : {
1188 7112 : appendPQExpBufferStr(sql, "VACUUM");
1189 :
1190 : /* parenthesized grammar of VACUUM is supported since v9.0 */
1191 7112 : if (serverVersion >= 90000)
1192 : {
1193 7112 : if (vacopts->disable_page_skipping)
1194 : {
1195 : /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
1196 : Assert(serverVersion >= 90600);
1197 136 : appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
1198 136 : sep = comma;
1199 : }
1200 7112 : if (vacopts->no_index_cleanup)
1201 : {
1202 : /* "INDEX_CLEANUP FALSE" has been supported since v12 */
1203 : Assert(serverVersion >= 120000);
1204 : Assert(!vacopts->force_index_cleanup);
1205 136 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
1206 136 : sep = comma;
1207 : }
1208 7112 : if (vacopts->force_index_cleanup)
1209 : {
1210 : /* "INDEX_CLEANUP TRUE" has been supported since v12 */
1211 : Assert(serverVersion >= 120000);
1212 : Assert(!vacopts->no_index_cleanup);
1213 0 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
1214 0 : sep = comma;
1215 : }
1216 7112 : if (!vacopts->do_truncate)
1217 : {
1218 : /* TRUNCATE is supported since v12 */
1219 : Assert(serverVersion >= 120000);
1220 136 : appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
1221 136 : sep = comma;
1222 : }
1223 7112 : if (!vacopts->process_main)
1224 : {
1225 : /* PROCESS_MAIN is supported since v16 */
1226 : Assert(serverVersion >= 160000);
1227 136 : appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
1228 136 : sep = comma;
1229 : }
1230 7112 : if (!vacopts->process_toast)
1231 : {
1232 : /* PROCESS_TOAST is supported since v14 */
1233 : Assert(serverVersion >= 140000);
1234 136 : appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
1235 136 : sep = comma;
1236 : }
1237 7112 : if (vacopts->skip_database_stats)
1238 : {
1239 : /* SKIP_DATABASE_STATS is supported since v16 */
1240 : Assert(serverVersion >= 160000);
1241 7112 : appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
1242 7112 : sep = comma;
1243 : }
1244 7112 : if (vacopts->skip_locked)
1245 : {
1246 : /* SKIP_LOCKED is supported since v12 */
1247 : Assert(serverVersion >= 120000);
1248 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1249 136 : sep = comma;
1250 : }
1251 7112 : if (vacopts->full)
1252 : {
1253 136 : appendPQExpBuffer(sql, "%sFULL", sep);
1254 136 : sep = comma;
1255 : }
1256 7112 : if (vacopts->freeze)
1257 : {
1258 2312 : appendPQExpBuffer(sql, "%sFREEZE", sep);
1259 2312 : sep = comma;
1260 : }
1261 7112 : if (vacopts->verbose)
1262 : {
1263 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
1264 0 : sep = comma;
1265 : }
1266 7112 : if (vacopts->and_analyze)
1267 : {
1268 2318 : appendPQExpBuffer(sql, "%sANALYZE", sep);
1269 2318 : sep = comma;
1270 : }
1271 7112 : if (vacopts->parallel_workers >= 0)
1272 : {
1273 : /* PARALLEL is supported since v13 */
1274 : Assert(serverVersion >= 130000);
1275 272 : appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
1276 : vacopts->parallel_workers);
1277 272 : sep = comma;
1278 : }
1279 7112 : if (vacopts->buffer_usage_limit)
1280 : {
1281 : Assert(serverVersion >= 160000);
1282 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1283 : vacopts->buffer_usage_limit);
1284 0 : sep = comma;
1285 : }
1286 7112 : if (sep != paren)
1287 7112 : appendPQExpBufferChar(sql, ')');
1288 : }
1289 : else
1290 : {
1291 0 : if (vacopts->full)
1292 0 : appendPQExpBufferStr(sql, " FULL");
1293 0 : if (vacopts->freeze)
1294 0 : appendPQExpBufferStr(sql, " FREEZE");
1295 0 : if (vacopts->verbose)
1296 0 : appendPQExpBufferStr(sql, " VERBOSE");
1297 0 : if (vacopts->and_analyze)
1298 0 : appendPQExpBufferStr(sql, " ANALYZE");
1299 : }
1300 : }
1301 :
1302 9586 : appendPQExpBuffer(sql, " %s;", table);
1303 9586 : }
1304 :
1305 : /*
1306 : * Send a vacuum/analyze command to the server, returning after sending the
1307 : * command.
1308 : *
1309 : * Any errors during command execution are reported to stderr.
1310 : */
1311 : static void
1312 9738 : run_vacuum_command(PGconn *conn, const char *sql, bool echo,
1313 : const char *table)
1314 : {
1315 : bool status;
1316 :
1317 9738 : if (echo)
1318 966 : printf("%s\n", sql);
1319 :
1320 9738 : status = PQsendQuery(conn, sql) == 1;
1321 :
1322 9738 : if (!status)
1323 : {
1324 0 : if (table)
1325 0 : pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1326 : table, PQdb(conn), PQerrorMessage(conn));
1327 : else
1328 0 : pg_log_error("vacuuming of database \"%s\" failed: %s",
1329 : PQdb(conn), PQerrorMessage(conn));
1330 : }
1331 9738 : }
1332 :
1333 : static void
1334 2 : help(const char *progname)
1335 : {
1336 2 : printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1337 2 : printf(_("Usage:\n"));
1338 2 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1339 2 : printf(_("\nOptions:\n"));
1340 2 : printf(_(" -a, --all vacuum all databases\n"));
1341 2 : printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
1342 2 : printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1343 2 : printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1344 2 : printf(_(" -e, --echo show the commands being sent to the server\n"));
1345 2 : printf(_(" -f, --full do full vacuuming\n"));
1346 2 : printf(_(" -F, --freeze freeze row transaction information\n"));
1347 2 : printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1348 2 : printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1349 2 : printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1350 2 : printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1351 2 : printf(_(" --missing-stats-only only analyze relations with missing statistics\n"));
1352 2 : printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1353 2 : printf(_(" --no-process-main skip the main relation\n"));
1354 2 : printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1355 2 : printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1356 2 : printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
1357 2 : printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
1358 2 : printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1359 2 : printf(_(" -q, --quiet don't write any messages\n"));
1360 2 : printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1361 2 : printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1362 2 : printf(_(" -v, --verbose write a lot of output\n"));
1363 2 : printf(_(" -V, --version output version information, then exit\n"));
1364 2 : printf(_(" -z, --analyze update optimizer statistics\n"));
1365 2 : printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1366 2 : printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1367 : " stages for faster results; no vacuum\n"));
1368 2 : printf(_(" -?, --help show this help, then exit\n"));
1369 2 : printf(_("\nConnection options:\n"));
1370 2 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1371 2 : printf(_(" -p, --port=PORT database server port\n"));
1372 2 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
1373 2 : printf(_(" -w, --no-password never prompt for password\n"));
1374 2 : printf(_(" -W, --password force password prompt\n"));
1375 2 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1376 2 : printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1377 2 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1378 2 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1379 2 : }
|
