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