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