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 : appendPQExpBufferStr(&catalog_query,
900 : " AND listed_objects.object_oid IS NULL\n");
901 : else
902 50 : appendPQExpBufferStr(&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 : appendPQExpBufferStr(&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 a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
958 : " AND NOT a.attisdropped\n"
959 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
960 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
961 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
962 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
963 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
964 :
965 : /* extended stats */
966 20 : appendPQExpBufferStr(&catalog_query,
967 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
968 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
969 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
970 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
971 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
972 : " AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n");
973 :
974 : /* expression indexes */
975 20 : appendPQExpBufferStr(&catalog_query,
976 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
977 : " JOIN pg_catalog.pg_index i"
978 : " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n"
979 : " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
980 : " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]"
981 : " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n"
982 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
983 : " AND NOT a.attisdropped\n"
984 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
985 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
986 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
987 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
988 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
989 :
990 : /* inheritance and regular stats */
991 20 : appendPQExpBufferStr(&catalog_query,
992 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
993 : " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
994 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
995 : " AND NOT a.attisdropped\n"
996 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
997 : " AND c.relhassubclass\n"
998 : " AND NOT p.inherited\n"
999 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1000 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1001 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
1002 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
1003 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
1004 : " AND s.stainherit))\n");
1005 :
1006 : /* inheritance and extended stats */
1007 20 : appendPQExpBufferStr(&catalog_query,
1008 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
1009 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
1010 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
1011 : " AND c.relhassubclass\n"
1012 : " AND NOT p.inherited\n"
1013 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
1014 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
1015 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
1016 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
1017 : " AND d.stxdinherit))\n");
1018 :
1019 20 : appendPQExpBufferStr(&catalog_query, " )\n");
1020 : }
1021 :
1022 : /*
1023 : * Execute the catalog query. We use the default search_path for this
1024 : * query for consistency with table lookups done elsewhere by the user.
1025 : */
1026 192 : appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
1027 192 : executeCommand(conn, "RESET search_path;", echo);
1028 192 : res = executeQuery(conn, catalog_query.data, echo);
1029 190 : termPQExpBuffer(&catalog_query);
1030 190 : PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
1031 :
1032 : /*
1033 : * Build qualified identifiers for each table, including the column list
1034 : * if given.
1035 : */
1036 190 : initPQExpBuffer(&buf);
1037 9768 : for (int i = 0; i < PQntuples(res); i++)
1038 : {
1039 19156 : appendPQExpBufferStr(&buf,
1040 9578 : fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
1041 9578 : PQgetvalue(res, i, 0),
1042 : PQclientEncoding(conn)));
1043 :
1044 9578 : if (objects_listed && !PQgetisnull(res, i, 2))
1045 10 : appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
1046 :
1047 9578 : simple_string_list_append(found_objs, buf.data);
1048 9578 : resetPQExpBuffer(&buf);
1049 : }
1050 190 : termPQExpBuffer(&buf);
1051 190 : PQclear(res);
1052 :
1053 190 : return found_objs;
1054 : }
1055 :
1056 : /*
1057 : * Vacuum/analyze all connectable databases.
1058 : *
1059 : * In analyze-in-stages mode, we process all databases in one stage before
1060 : * moving on to the next stage. That ensure minimal stats are available
1061 : * quickly everywhere before generating more detailed ones.
1062 : */
1063 : static void
1064 46 : vacuum_all_databases(ConnParams *cparams,
1065 : vacuumingOptions *vacopts,
1066 : bool analyze_in_stages,
1067 : SimpleStringList *objects,
1068 : int concurrentCons,
1069 : const char *progname, bool echo, bool quiet)
1070 : {
1071 : PGconn *conn;
1072 : PGresult *result;
1073 : int stage;
1074 : int i;
1075 :
1076 46 : conn = connectMaintenanceDatabase(cparams, progname, echo);
1077 46 : result = executeQuery(conn,
1078 : "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
1079 : echo);
1080 46 : PQfinish(conn);
1081 :
1082 46 : if (analyze_in_stages)
1083 : {
1084 2 : SimpleStringList **found_objs = NULL;
1085 :
1086 2 : if (vacopts->missing_stats_only)
1087 0 : found_objs = palloc0(PQntuples(result) * sizeof(SimpleStringList *));
1088 :
1089 : /*
1090 : * When analyzing all databases in stages, we analyze them all in the
1091 : * fastest stage first, so that initial statistics become available
1092 : * for all of them as soon as possible.
1093 : *
1094 : * This means we establish several times as many connections, but
1095 : * that's a secondary consideration.
1096 : */
1097 8 : for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
1098 : {
1099 18 : for (i = 0; i < PQntuples(result); i++)
1100 : {
1101 12 : cparams->override_dbname = PQgetvalue(result, i, 0);
1102 :
1103 12 : vacuum_one_database(cparams, vacopts,
1104 : stage,
1105 : objects,
1106 12 : vacopts->missing_stats_only ? &found_objs[i] : NULL,
1107 : concurrentCons,
1108 : progname, echo, quiet);
1109 : }
1110 : }
1111 : }
1112 : else
1113 : {
1114 142 : for (i = 0; i < PQntuples(result); i++)
1115 : {
1116 98 : cparams->override_dbname = PQgetvalue(result, i, 0);
1117 :
1118 98 : vacuum_one_database(cparams, vacopts,
1119 : ANALYZE_NO_STAGE,
1120 : objects, NULL,
1121 : concurrentCons,
1122 : progname, echo, quiet);
1123 : }
1124 : }
1125 :
1126 46 : PQclear(result);
1127 46 : }
1128 :
1129 : /*
1130 : * Construct a vacuum/analyze command to run based on the given options, in the
1131 : * given string buffer, which may contain previous garbage.
1132 : *
1133 : * The table name used must be already properly quoted. The command generated
1134 : * depends on the server version involved and it is semicolon-terminated.
1135 : */
1136 : static void
1137 9586 : prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
1138 : vacuumingOptions *vacopts, const char *table)
1139 : {
1140 9586 : const char *paren = " (";
1141 9586 : const char *comma = ", ";
1142 9586 : const char *sep = paren;
1143 :
1144 9586 : resetPQExpBuffer(sql);
1145 :
1146 9586 : if (vacopts->analyze_only)
1147 : {
1148 2474 : appendPQExpBufferStr(sql, "ANALYZE");
1149 :
1150 : /* parenthesized grammar of ANALYZE is supported since v11 */
1151 2474 : if (serverVersion >= 110000)
1152 : {
1153 2474 : if (vacopts->skip_locked)
1154 : {
1155 : /* SKIP_LOCKED is supported since v12 */
1156 : Assert(serverVersion >= 120000);
1157 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1158 136 : sep = comma;
1159 : }
1160 2474 : if (vacopts->verbose)
1161 : {
1162 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
1163 0 : sep = comma;
1164 : }
1165 2474 : if (vacopts->buffer_usage_limit)
1166 : {
1167 : Assert(serverVersion >= 160000);
1168 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1169 : vacopts->buffer_usage_limit);
1170 0 : sep = comma;
1171 : }
1172 2474 : if (sep != paren)
1173 136 : appendPQExpBufferChar(sql, ')');
1174 : }
1175 : else
1176 : {
1177 0 : if (vacopts->verbose)
1178 0 : appendPQExpBufferStr(sql, " VERBOSE");
1179 : }
1180 : }
1181 : else
1182 : {
1183 7112 : appendPQExpBufferStr(sql, "VACUUM");
1184 :
1185 : /* parenthesized grammar of VACUUM is supported since v9.0 */
1186 7112 : if (serverVersion >= 90000)
1187 : {
1188 7112 : if (vacopts->disable_page_skipping)
1189 : {
1190 : /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
1191 : Assert(serverVersion >= 90600);
1192 136 : appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
1193 136 : sep = comma;
1194 : }
1195 7112 : if (vacopts->no_index_cleanup)
1196 : {
1197 : /* "INDEX_CLEANUP FALSE" has been supported since v12 */
1198 : Assert(serverVersion >= 120000);
1199 : Assert(!vacopts->force_index_cleanup);
1200 136 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
1201 136 : sep = comma;
1202 : }
1203 7112 : if (vacopts->force_index_cleanup)
1204 : {
1205 : /* "INDEX_CLEANUP TRUE" has been supported since v12 */
1206 : Assert(serverVersion >= 120000);
1207 : Assert(!vacopts->no_index_cleanup);
1208 0 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
1209 0 : sep = comma;
1210 : }
1211 7112 : if (!vacopts->do_truncate)
1212 : {
1213 : /* TRUNCATE is supported since v12 */
1214 : Assert(serverVersion >= 120000);
1215 136 : appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
1216 136 : sep = comma;
1217 : }
1218 7112 : if (!vacopts->process_main)
1219 : {
1220 : /* PROCESS_MAIN is supported since v16 */
1221 : Assert(serverVersion >= 160000);
1222 136 : appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
1223 136 : sep = comma;
1224 : }
1225 7112 : if (!vacopts->process_toast)
1226 : {
1227 : /* PROCESS_TOAST is supported since v14 */
1228 : Assert(serverVersion >= 140000);
1229 136 : appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
1230 136 : sep = comma;
1231 : }
1232 7112 : if (vacopts->skip_database_stats)
1233 : {
1234 : /* SKIP_DATABASE_STATS is supported since v16 */
1235 : Assert(serverVersion >= 160000);
1236 7112 : appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
1237 7112 : sep = comma;
1238 : }
1239 7112 : if (vacopts->skip_locked)
1240 : {
1241 : /* SKIP_LOCKED is supported since v12 */
1242 : Assert(serverVersion >= 120000);
1243 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1244 136 : sep = comma;
1245 : }
1246 7112 : if (vacopts->full)
1247 : {
1248 136 : appendPQExpBuffer(sql, "%sFULL", sep);
1249 136 : sep = comma;
1250 : }
1251 7112 : if (vacopts->freeze)
1252 : {
1253 2312 : appendPQExpBuffer(sql, "%sFREEZE", sep);
1254 2312 : sep = comma;
1255 : }
1256 7112 : if (vacopts->verbose)
1257 : {
1258 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
1259 0 : sep = comma;
1260 : }
1261 7112 : if (vacopts->and_analyze)
1262 : {
1263 2318 : appendPQExpBuffer(sql, "%sANALYZE", sep);
1264 2318 : sep = comma;
1265 : }
1266 7112 : if (vacopts->parallel_workers >= 0)
1267 : {
1268 : /* PARALLEL is supported since v13 */
1269 : Assert(serverVersion >= 130000);
1270 272 : appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
1271 : vacopts->parallel_workers);
1272 272 : sep = comma;
1273 : }
1274 7112 : if (vacopts->buffer_usage_limit)
1275 : {
1276 : Assert(serverVersion >= 160000);
1277 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1278 : vacopts->buffer_usage_limit);
1279 0 : sep = comma;
1280 : }
1281 7112 : if (sep != paren)
1282 7112 : appendPQExpBufferChar(sql, ')');
1283 : }
1284 : else
1285 : {
1286 0 : if (vacopts->full)
1287 0 : appendPQExpBufferStr(sql, " FULL");
1288 0 : if (vacopts->freeze)
1289 0 : appendPQExpBufferStr(sql, " FREEZE");
1290 0 : if (vacopts->verbose)
1291 0 : appendPQExpBufferStr(sql, " VERBOSE");
1292 0 : if (vacopts->and_analyze)
1293 0 : appendPQExpBufferStr(sql, " ANALYZE");
1294 : }
1295 : }
1296 :
1297 9586 : appendPQExpBuffer(sql, " %s;", table);
1298 9586 : }
1299 :
1300 : /*
1301 : * Send a vacuum/analyze command to the server, returning after sending the
1302 : * command.
1303 : *
1304 : * Any errors during command execution are reported to stderr.
1305 : */
1306 : static void
1307 9738 : run_vacuum_command(PGconn *conn, const char *sql, bool echo,
1308 : const char *table)
1309 : {
1310 : bool status;
1311 :
1312 9738 : if (echo)
1313 966 : printf("%s\n", sql);
1314 :
1315 9738 : status = PQsendQuery(conn, sql) == 1;
1316 :
1317 9738 : if (!status)
1318 : {
1319 0 : if (table)
1320 0 : pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1321 : table, PQdb(conn), PQerrorMessage(conn));
1322 : else
1323 0 : pg_log_error("vacuuming of database \"%s\" failed: %s",
1324 : PQdb(conn), PQerrorMessage(conn));
1325 : }
1326 9738 : }
1327 :
1328 : static void
1329 2 : help(const char *progname)
1330 : {
1331 2 : printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1332 2 : printf(_("Usage:\n"));
1333 2 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1334 2 : printf(_("\nOptions:\n"));
1335 2 : printf(_(" -a, --all vacuum all databases\n"));
1336 2 : printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
1337 2 : printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1338 2 : printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1339 2 : printf(_(" -e, --echo show the commands being sent to the server\n"));
1340 2 : printf(_(" -f, --full do full vacuuming\n"));
1341 2 : printf(_(" -F, --freeze freeze row transaction information\n"));
1342 2 : printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1343 2 : printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1344 2 : printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1345 2 : printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1346 2 : printf(_(" --missing-stats-only only analyze relations with missing statistics\n"));
1347 2 : printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1348 2 : printf(_(" --no-process-main skip the main relation\n"));
1349 2 : printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1350 2 : printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1351 2 : printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
1352 2 : printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
1353 2 : printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1354 2 : printf(_(" -q, --quiet don't write any messages\n"));
1355 2 : printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1356 2 : printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1357 2 : printf(_(" -v, --verbose write a lot of output\n"));
1358 2 : printf(_(" -V, --version output version information, then exit\n"));
1359 2 : printf(_(" -z, --analyze update optimizer statistics\n"));
1360 2 : printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1361 2 : printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1362 : " stages for faster results; no vacuum\n"));
1363 2 : printf(_(" -?, --help show this help, then exit\n"));
1364 2 : printf(_("\nConnection options:\n"));
1365 2 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1366 2 : printf(_(" -p, --port=PORT database server port\n"));
1367 2 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
1368 2 : printf(_(" -w, --no-password never prompt for password\n"));
1369 2 : printf(_(" -W, --password force password prompt\n"));
1370 2 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1371 2 : printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1372 2 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1373 2 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1374 2 : }
|
