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