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