Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * vacuumdb
4 : *
5 : * Portions Copyright (c) 1996-2024, 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 : 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 'tables' 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. In this case and if the table list is empty, we first obtain
472 : * a list of tables from the database.
473 : */
474 : static void
475 134 : vacuum_one_database(ConnParams *cparams,
476 : vacuumingOptions *vacopts,
477 : int stage,
478 : SimpleStringList *objects,
479 : int concurrentCons,
480 : const char *progname, bool echo, bool quiet)
481 : {
482 : PQExpBufferData sql;
483 : PQExpBufferData buf;
484 : PQExpBufferData catalog_query;
485 : PGresult *res;
486 : PGconn *conn;
487 : SimpleStringListCell *cell;
488 : ParallelSlotArray *sa;
489 134 : SimpleStringList dbtables = {NULL, NULL};
490 : int i;
491 : int ntups;
492 134 : bool failed = false;
493 134 : bool objects_listed = false;
494 134 : bool has_where = false;
495 : const char *initcmd;
496 134 : const char *stage_commands[] = {
497 : "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
498 : "SET default_statistics_target=10; RESET vacuum_cost_delay;",
499 : "RESET default_statistics_target;"
500 : };
501 134 : const char *stage_messages[] = {
502 : gettext_noop("Generating minimal optimizer statistics (1 target)"),
503 : gettext_noop("Generating medium optimizer statistics (10 targets)"),
504 : gettext_noop("Generating default (full) optimizer statistics")
505 : };
506 :
507 : Assert(stage == ANALYZE_NO_STAGE ||
508 : (stage >= 0 && stage < ANALYZE_NUM_STAGES));
509 :
510 134 : conn = connectDatabase(cparams, progname, echo, false, true);
511 :
512 132 : if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
513 : {
514 0 : PQfinish(conn);
515 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
516 : "disable-page-skipping", "9.6");
517 : }
518 :
519 132 : if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
520 : {
521 0 : PQfinish(conn);
522 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
523 : "no-index-cleanup", "12");
524 : }
525 :
526 132 : if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
527 : {
528 0 : PQfinish(conn);
529 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
530 : "force-index-cleanup", "12");
531 : }
532 :
533 132 : if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
534 : {
535 0 : PQfinish(conn);
536 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
537 : "no-truncate", "12");
538 : }
539 :
540 132 : if (!vacopts->process_main && PQserverVersion(conn) < 160000)
541 : {
542 0 : PQfinish(conn);
543 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
544 : "no-process-main", "16");
545 : }
546 :
547 132 : if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
548 : {
549 0 : PQfinish(conn);
550 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
551 : "no-process-toast", "14");
552 : }
553 :
554 132 : if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
555 : {
556 0 : PQfinish(conn);
557 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
558 : "skip-locked", "12");
559 : }
560 :
561 132 : if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
562 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
563 : "--min-xid-age", "9.6");
564 :
565 132 : if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
566 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
567 : "--min-mxid-age", "9.6");
568 :
569 132 : if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
570 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
571 : "--parallel", "13");
572 :
573 132 : if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
574 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
575 : "--buffer-usage-limit", "16");
576 :
577 : /* skip_database_stats is used automatically if server supports it */
578 132 : vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
579 :
580 132 : if (!quiet)
581 : {
582 132 : if (stage != ANALYZE_NO_STAGE)
583 18 : printf(_("%s: processing database \"%s\": %s\n"),
584 : progname, PQdb(conn), _(stage_messages[stage]));
585 : else
586 114 : printf(_("%s: vacuuming database \"%s\"\n"),
587 : progname, PQdb(conn));
588 132 : fflush(stdout);
589 : }
590 :
591 : /*
592 : * Prepare the list of tables to process by querying the catalogs.
593 : *
594 : * Since we execute the constructed query with the default search_path
595 : * (which could be unsafe), everything in this query MUST be fully
596 : * qualified.
597 : *
598 : * First, build a WITH clause for the catalog query if any tables were
599 : * specified, with a set of values made of relation names and their
600 : * optional set of columns. This is used to match any provided column
601 : * lists with the generated qualified identifiers and to filter for the
602 : * tables provided via --table. If a listed table does not exist, the
603 : * catalog query will fail.
604 : */
605 132 : initPQExpBuffer(&catalog_query);
606 174 : for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
607 : {
608 42 : char *just_table = NULL;
609 42 : const char *just_columns = NULL;
610 :
611 42 : if (!objects_listed)
612 : {
613 38 : appendPQExpBufferStr(&catalog_query,
614 : "WITH listed_objects (object_oid, column_list) "
615 : "AS (\n VALUES (");
616 38 : objects_listed = true;
617 : }
618 : else
619 4 : appendPQExpBufferStr(&catalog_query, ",\n (");
620 :
621 42 : if (objfilter & (OBJFILTER_SCHEMA | OBJFILTER_SCHEMA_EXCLUDE))
622 : {
623 20 : appendStringLiteralConn(&catalog_query, cell->val, conn);
624 20 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
625 : }
626 :
627 42 : if (objfilter & OBJFILTER_TABLE)
628 : {
629 : /*
630 : * Split relation and column names given by the user, this is used
631 : * to feed the CTE with values on which are performed pre-run
632 : * validity checks as well. For now these happen only on the
633 : * relation name.
634 : */
635 22 : splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
636 : &just_table, &just_columns);
637 :
638 22 : appendStringLiteralConn(&catalog_query, just_table, conn);
639 22 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
640 : }
641 :
642 42 : if (just_columns && just_columns[0] != '\0')
643 10 : appendStringLiteralConn(&catalog_query, just_columns, conn);
644 : else
645 32 : appendPQExpBufferStr(&catalog_query, "NULL");
646 :
647 42 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
648 :
649 42 : pg_free(just_table);
650 : }
651 :
652 : /* Finish formatting the CTE */
653 132 : if (objects_listed)
654 38 : appendPQExpBufferStr(&catalog_query, "\n)\n");
655 :
656 132 : appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
657 :
658 132 : if (objects_listed)
659 38 : appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
660 :
661 132 : appendPQExpBufferStr(&catalog_query,
662 : " FROM pg_catalog.pg_class c\n"
663 : " JOIN pg_catalog.pg_namespace ns"
664 : " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
665 : " LEFT JOIN pg_catalog.pg_class t"
666 : " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
667 :
668 : /* Used to match the tables or schemas listed by the user */
669 132 : if (objects_listed)
670 : {
671 38 : appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
672 : " ON listed_objects.object_oid"
673 : " OPERATOR(pg_catalog.=) ");
674 :
675 38 : if (objfilter & OBJFILTER_TABLE)
676 22 : appendPQExpBufferStr(&catalog_query, "c.oid\n");
677 : else
678 16 : appendPQExpBufferStr(&catalog_query, "ns.oid\n");
679 :
680 38 : if (objfilter & OBJFILTER_SCHEMA_EXCLUDE)
681 8 : appendPQExpBuffer(&catalog_query,
682 : " WHERE listed_objects.object_oid IS NULL\n");
683 : else
684 30 : appendPQExpBuffer(&catalog_query,
685 : " WHERE listed_objects.object_oid IS NOT NULL\n");
686 38 : has_where = true;
687 : }
688 :
689 : /*
690 : * If no tables were listed, filter for the relevant relation types. If
691 : * tables were given via --table, don't bother filtering by relation type.
692 : * Instead, let the server decide whether a given relation can be
693 : * processed in which case the user will know about it.
694 : */
695 132 : if ((objfilter & OBJFILTER_TABLE) == 0)
696 : {
697 110 : appendPQExpBuffer(&catalog_query,
698 : " %s c.relkind OPERATOR(pg_catalog.=) ANY (array["
699 : CppAsString2(RELKIND_RELATION) ", "
700 : CppAsString2(RELKIND_MATVIEW) "])\n",
701 : has_where ? "AND" : "WHERE");
702 110 : has_where = true;
703 : }
704 :
705 : /*
706 : * For --min-xid-age and --min-mxid-age, the age of the relation is the
707 : * greatest of the ages of the main relation and its associated TOAST
708 : * table. The commands generated by vacuumdb will also process the TOAST
709 : * table for the relation if necessary, so it does not need to be
710 : * considered separately.
711 : */
712 132 : if (vacopts->min_xid_age != 0)
713 : {
714 2 : appendPQExpBuffer(&catalog_query,
715 : " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
716 : " pg_catalog.age(t.relfrozenxid)) "
717 : " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
718 : " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
719 : " '0'::pg_catalog.xid\n",
720 : has_where ? "AND" : "WHERE", vacopts->min_xid_age);
721 2 : has_where = true;
722 : }
723 :
724 132 : if (vacopts->min_mxid_age != 0)
725 : {
726 2 : appendPQExpBuffer(&catalog_query,
727 : " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
728 : " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
729 : " '%d'::pg_catalog.int4\n"
730 : " AND c.relminmxid OPERATOR(pg_catalog.!=)"
731 : " '0'::pg_catalog.xid\n",
732 : has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
733 2 : has_where = true;
734 : }
735 :
736 : /*
737 : * Execute the catalog query. We use the default search_path for this
738 : * query for consistency with table lookups done elsewhere by the user.
739 : */
740 132 : appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
741 132 : executeCommand(conn, "RESET search_path;", echo);
742 132 : res = executeQuery(conn, catalog_query.data, echo);
743 130 : termPQExpBuffer(&catalog_query);
744 130 : PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
745 :
746 : /*
747 : * If no rows are returned, there are no matching tables, so we are done.
748 : */
749 130 : ntups = PQntuples(res);
750 130 : if (ntups == 0)
751 : {
752 4 : PQclear(res);
753 4 : PQfinish(conn);
754 4 : return;
755 : }
756 :
757 : /*
758 : * Build qualified identifiers for each table, including the column list
759 : * if given.
760 : */
761 126 : initPQExpBuffer(&buf);
762 6974 : for (i = 0; i < ntups; i++)
763 : {
764 6848 : appendPQExpBufferStr(&buf,
765 6848 : fmtQualifiedId(PQgetvalue(res, i, 1),
766 6848 : PQgetvalue(res, i, 0)));
767 :
768 6848 : if (objects_listed && !PQgetisnull(res, i, 2))
769 10 : appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
770 :
771 6848 : simple_string_list_append(&dbtables, buf.data);
772 6848 : resetPQExpBuffer(&buf);
773 : }
774 126 : termPQExpBuffer(&buf);
775 126 : PQclear(res);
776 :
777 : /*
778 : * Ensure concurrentCons is sane. If there are more connections than
779 : * vacuumable relations, we don't need to use them all.
780 : */
781 126 : if (concurrentCons > ntups)
782 0 : concurrentCons = ntups;
783 126 : if (concurrentCons <= 0)
784 0 : concurrentCons = 1;
785 :
786 : /*
787 : * All slots need to be prepared to run the appropriate analyze stage, if
788 : * caller requested that mode. We have to prepare the initial connection
789 : * ourselves before setting up the slots.
790 : */
791 126 : if (stage == ANALYZE_NO_STAGE)
792 108 : initcmd = NULL;
793 : else
794 : {
795 18 : initcmd = stage_commands[stage];
796 18 : executeCommand(conn, initcmd, echo);
797 : }
798 :
799 : /*
800 : * Setup the database connections. We reuse the connection we already have
801 : * for the first slot. If not in parallel mode, the first slot in the
802 : * array contains the connection.
803 : */
804 126 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
805 126 : ParallelSlotsAdoptConn(sa, conn);
806 :
807 126 : initPQExpBuffer(&sql);
808 :
809 126 : cell = dbtables.head;
810 : do
811 : {
812 6848 : const char *tabname = cell->val;
813 : ParallelSlot *free_slot;
814 :
815 6848 : if (CancelRequested)
816 : {
817 0 : failed = true;
818 0 : goto finish;
819 : }
820 :
821 6848 : free_slot = ParallelSlotsGetIdle(sa, NULL);
822 6848 : if (!free_slot)
823 : {
824 0 : failed = true;
825 0 : goto finish;
826 : }
827 :
828 6848 : prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection),
829 : vacopts, tabname);
830 :
831 : /*
832 : * Execute the vacuum. All errors are handled in processQueryResult
833 : * through ParallelSlotsGetIdle.
834 : */
835 6848 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
836 6848 : run_vacuum_command(free_slot->connection, sql.data,
837 : echo, tabname);
838 :
839 6848 : cell = cell->next;
840 6848 : } while (cell != NULL);
841 :
842 126 : if (!ParallelSlotsWaitCompletion(sa))
843 : {
844 2 : failed = true;
845 2 : goto finish;
846 : }
847 :
848 : /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
849 124 : if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE)
850 : {
851 106 : const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
852 106 : ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
853 :
854 106 : if (!free_slot)
855 : {
856 0 : failed = true;
857 0 : goto finish;
858 : }
859 :
860 106 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
861 106 : run_vacuum_command(free_slot->connection, cmd, echo, NULL);
862 :
863 106 : if (!ParallelSlotsWaitCompletion(sa))
864 0 : failed = true;
865 : }
866 :
867 124 : finish:
868 126 : ParallelSlotsTerminate(sa);
869 126 : pg_free(sa);
870 :
871 126 : termPQExpBuffer(&sql);
872 :
873 126 : if (failed)
874 2 : exit(1);
875 : }
876 :
877 : /*
878 : * Vacuum/analyze all connectable databases.
879 : *
880 : * In analyze-in-stages mode, we process all databases in one stage before
881 : * moving on to the next stage. That ensure minimal stats are available
882 : * quickly everywhere before generating more detailed ones.
883 : */
884 : static void
885 26 : vacuum_all_databases(ConnParams *cparams,
886 : vacuumingOptions *vacopts,
887 : bool analyze_in_stages,
888 : SimpleStringList *objects,
889 : int concurrentCons,
890 : const char *progname, bool echo, bool quiet)
891 : {
892 : PGconn *conn;
893 : PGresult *result;
894 : int stage;
895 : int i;
896 :
897 26 : conn = connectMaintenanceDatabase(cparams, progname, echo);
898 26 : result = executeQuery(conn,
899 : "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
900 : echo);
901 26 : PQfinish(conn);
902 :
903 26 : if (analyze_in_stages)
904 : {
905 : /*
906 : * When analyzing all databases in stages, we analyze them all in the
907 : * fastest stage first, so that initial statistics become available
908 : * for all of them as soon as possible.
909 : *
910 : * This means we establish several times as many connections, but
911 : * that's a secondary consideration.
912 : */
913 8 : for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
914 : {
915 18 : for (i = 0; i < PQntuples(result); i++)
916 : {
917 12 : cparams->override_dbname = PQgetvalue(result, i, 0);
918 :
919 12 : vacuum_one_database(cparams, vacopts,
920 : stage,
921 : objects,
922 : concurrentCons,
923 : progname, echo, quiet);
924 : }
925 : }
926 : }
927 : else
928 : {
929 82 : for (i = 0; i < PQntuples(result); i++)
930 : {
931 58 : cparams->override_dbname = PQgetvalue(result, i, 0);
932 :
933 58 : vacuum_one_database(cparams, vacopts,
934 : ANALYZE_NO_STAGE,
935 : objects,
936 : concurrentCons,
937 : progname, echo, quiet);
938 : }
939 : }
940 :
941 26 : PQclear(result);
942 26 : }
943 :
944 : /*
945 : * Construct a vacuum/analyze command to run based on the given options, in the
946 : * given string buffer, which may contain previous garbage.
947 : *
948 : * The table name used must be already properly quoted. The command generated
949 : * depends on the server version involved and it is semicolon-terminated.
950 : */
951 : static void
952 6848 : prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
953 : vacuumingOptions *vacopts, const char *table)
954 : {
955 6848 : const char *paren = " (";
956 6848 : const char *comma = ", ";
957 6848 : const char *sep = paren;
958 :
959 6848 : resetPQExpBuffer(sql);
960 :
961 6848 : if (vacopts->analyze_only)
962 : {
963 2456 : appendPQExpBufferStr(sql, "ANALYZE");
964 :
965 : /* parenthesized grammar of ANALYZE is supported since v11 */
966 2456 : if (serverVersion >= 110000)
967 : {
968 2456 : if (vacopts->skip_locked)
969 : {
970 : /* SKIP_LOCKED is supported since v12 */
971 : Assert(serverVersion >= 120000);
972 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
973 136 : sep = comma;
974 : }
975 2456 : if (vacopts->verbose)
976 : {
977 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
978 0 : sep = comma;
979 : }
980 2456 : if (vacopts->buffer_usage_limit)
981 : {
982 : Assert(serverVersion >= 160000);
983 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
984 : vacopts->buffer_usage_limit);
985 0 : sep = comma;
986 : }
987 2456 : if (sep != paren)
988 136 : appendPQExpBufferChar(sql, ')');
989 : }
990 : else
991 : {
992 0 : if (vacopts->verbose)
993 0 : appendPQExpBufferStr(sql, " VERBOSE");
994 : }
995 : }
996 : else
997 : {
998 4392 : appendPQExpBufferStr(sql, "VACUUM");
999 :
1000 : /* parenthesized grammar of VACUUM is supported since v9.0 */
1001 4392 : if (serverVersion >= 90000)
1002 : {
1003 4392 : if (vacopts->disable_page_skipping)
1004 : {
1005 : /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
1006 : Assert(serverVersion >= 90600);
1007 136 : appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
1008 136 : sep = comma;
1009 : }
1010 4392 : if (vacopts->no_index_cleanup)
1011 : {
1012 : /* "INDEX_CLEANUP FALSE" has been supported since v12 */
1013 : Assert(serverVersion >= 120000);
1014 : Assert(!vacopts->force_index_cleanup);
1015 136 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
1016 136 : sep = comma;
1017 : }
1018 4392 : if (vacopts->force_index_cleanup)
1019 : {
1020 : /* "INDEX_CLEANUP TRUE" has been supported since v12 */
1021 : Assert(serverVersion >= 120000);
1022 : Assert(!vacopts->no_index_cleanup);
1023 0 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
1024 0 : sep = comma;
1025 : }
1026 4392 : if (!vacopts->do_truncate)
1027 : {
1028 : /* TRUNCATE is supported since v12 */
1029 : Assert(serverVersion >= 120000);
1030 136 : appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
1031 136 : sep = comma;
1032 : }
1033 4392 : if (!vacopts->process_main)
1034 : {
1035 : /* PROCESS_MAIN is supported since v16 */
1036 : Assert(serverVersion >= 160000);
1037 136 : appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
1038 136 : sep = comma;
1039 : }
1040 4392 : if (!vacopts->process_toast)
1041 : {
1042 : /* PROCESS_TOAST is supported since v14 */
1043 : Assert(serverVersion >= 140000);
1044 136 : appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
1045 136 : sep = comma;
1046 : }
1047 4392 : if (vacopts->skip_database_stats)
1048 : {
1049 : /* SKIP_DATABASE_STATS is supported since v16 */
1050 : Assert(serverVersion >= 160000);
1051 4392 : appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
1052 4392 : sep = comma;
1053 : }
1054 4392 : if (vacopts->skip_locked)
1055 : {
1056 : /* SKIP_LOCKED is supported since v12 */
1057 : Assert(serverVersion >= 120000);
1058 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1059 136 : sep = comma;
1060 : }
1061 4392 : if (vacopts->full)
1062 : {
1063 136 : appendPQExpBuffer(sql, "%sFULL", sep);
1064 136 : sep = comma;
1065 : }
1066 4392 : if (vacopts->freeze)
1067 : {
1068 952 : appendPQExpBuffer(sql, "%sFREEZE", sep);
1069 952 : sep = comma;
1070 : }
1071 4392 : if (vacopts->verbose)
1072 : {
1073 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
1074 0 : sep = comma;
1075 : }
1076 4392 : if (vacopts->and_analyze)
1077 : {
1078 958 : appendPQExpBuffer(sql, "%sANALYZE", sep);
1079 958 : sep = comma;
1080 : }
1081 4392 : if (vacopts->parallel_workers >= 0)
1082 : {
1083 : /* PARALLEL is supported since v13 */
1084 : Assert(serverVersion >= 130000);
1085 272 : appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
1086 : vacopts->parallel_workers);
1087 272 : sep = comma;
1088 : }
1089 4392 : if (vacopts->buffer_usage_limit)
1090 : {
1091 : Assert(serverVersion >= 160000);
1092 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1093 : vacopts->buffer_usage_limit);
1094 0 : sep = comma;
1095 : }
1096 4392 : if (sep != paren)
1097 4392 : appendPQExpBufferChar(sql, ')');
1098 : }
1099 : else
1100 : {
1101 0 : if (vacopts->full)
1102 0 : appendPQExpBufferStr(sql, " FULL");
1103 0 : if (vacopts->freeze)
1104 0 : appendPQExpBufferStr(sql, " FREEZE");
1105 0 : if (vacopts->verbose)
1106 0 : appendPQExpBufferStr(sql, " VERBOSE");
1107 0 : if (vacopts->and_analyze)
1108 0 : appendPQExpBufferStr(sql, " ANALYZE");
1109 : }
1110 : }
1111 :
1112 6848 : appendPQExpBuffer(sql, " %s;", table);
1113 6848 : }
1114 :
1115 : /*
1116 : * Send a vacuum/analyze command to the server, returning after sending the
1117 : * command.
1118 : *
1119 : * Any errors during command execution are reported to stderr.
1120 : */
1121 : static void
1122 6954 : run_vacuum_command(PGconn *conn, const char *sql, bool echo,
1123 : const char *table)
1124 : {
1125 : bool status;
1126 :
1127 6954 : if (echo)
1128 966 : printf("%s\n", sql);
1129 :
1130 6954 : status = PQsendQuery(conn, sql) == 1;
1131 :
1132 6954 : if (!status)
1133 : {
1134 0 : if (table)
1135 0 : pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1136 : table, PQdb(conn), PQerrorMessage(conn));
1137 : else
1138 0 : pg_log_error("vacuuming of database \"%s\" failed: %s",
1139 : PQdb(conn), PQerrorMessage(conn));
1140 : }
1141 6954 : }
1142 :
1143 : static void
1144 2 : help(const char *progname)
1145 : {
1146 2 : printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1147 2 : printf(_("Usage:\n"));
1148 2 : printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1149 2 : printf(_("\nOptions:\n"));
1150 2 : printf(_(" -a, --all vacuum all databases\n"));
1151 2 : printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
1152 2 : printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1153 2 : printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1154 2 : printf(_(" -e, --echo show the commands being sent to the server\n"));
1155 2 : printf(_(" -f, --full do full vacuuming\n"));
1156 2 : printf(_(" -F, --freeze freeze row transaction information\n"));
1157 2 : printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1158 2 : printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1159 2 : printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1160 2 : printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1161 2 : printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1162 2 : printf(_(" --no-process-main skip the main relation\n"));
1163 2 : printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1164 2 : printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1165 2 : printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
1166 2 : printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
1167 2 : printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1168 2 : printf(_(" -q, --quiet don't write any messages\n"));
1169 2 : printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1170 2 : printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1171 2 : printf(_(" -v, --verbose write a lot of output\n"));
1172 2 : printf(_(" -V, --version output version information, then exit\n"));
1173 2 : printf(_(" -z, --analyze update optimizer statistics\n"));
1174 2 : printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1175 2 : printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1176 : " stages for faster results; no vacuum\n"));
1177 2 : printf(_(" -?, --help show this help, then exit\n"));
1178 2 : printf(_("\nConnection options:\n"));
1179 2 : printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1180 2 : printf(_(" -p, --port=PORT database server port\n"));
1181 2 : printf(_(" -U, --username=USERNAME user name to connect as\n"));
1182 2 : printf(_(" -w, --no-password never prompt for password\n"));
1183 2 : printf(_(" -W, --password force password prompt\n"));
1184 2 : printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1185 2 : printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1186 2 : printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1187 2 : printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1188 2 : }
|