?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Remove useless configure check
[pgsql.git] / src / bin / scripts / vacuumdb.c
blob170f34d92744bdebb38f37a2877cedf051792ada
1 /*-------------------------------------------------------------------------
2 *
3 * vacuumdb
4 *
5 * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
6 * Portions Copyright (c) 1994, Regents of the University of California
7 *
8 * src/bin/scripts/vacuumdb.c
9 *
10 *-------------------------------------------------------------------------
11 */
12
13 #include "postgres_fe.h"
14
15 #include <limits.h>
16
17 #include "catalog/pg_class_d.h"
18 #include "common.h"
19 #include "common/connect.h"
20 #include "common/logging.h"
21 #include "fe_utils/cancel.h"
22 #include "fe_utils/option_utils.h"
23 #include "fe_utils/parallel_slot.h"
24 #include "fe_utils/query_utils.h"
25 #include "fe_utils/simple_list.h"
26 #include "fe_utils/string_utils.h"
27
28
29 /* vacuum options controlled by user flags */
30 typedef struct vacuumingOptions
31 {
32 bool analyze_only;
33 bool verbose;
34 bool and_analyze;
35 bool full;
36 bool freeze;
37 bool disable_page_skipping;
38 bool skip_locked;
39 int min_xid_age;
40 int min_mxid_age;
41 int parallel_workers; /* >= 0 indicates user specified the
42 * parallel degree, otherwise -1 */
43 bool no_index_cleanup;
44 bool force_index_cleanup;
45 bool do_truncate;
46 bool process_main;
47 bool process_toast;
48 bool skip_database_stats;
49 char *buffer_usage_limit;
50 } vacuumingOptions;
51
52 /* object filter options */
53 typedef enum
54 {
55 OBJFILTER_NONE = 0, /* no filter used */
56 OBJFILTER_ALL_DBS = (1 << 0), /* -a | --all */
57 OBJFILTER_DATABASE = (1 << 1), /* -d | --dbname */
58 OBJFILTER_TABLE = (1 << 2), /* -t | --table */
59 OBJFILTER_SCHEMA = (1 << 3), /* -n | --schema */
60 OBJFILTER_SCHEMA_EXCLUDE = (1 << 4), /* -N | --exclude-schema */
61 } VacObjFilter;
62
63 static VacObjFilter objfilter = OBJFILTER_NONE;
64
65 static void vacuum_one_database(ConnParams *cparams,
66 vacuumingOptions *vacopts,
67 int stage,
68 SimpleStringList *objects,
69 int concurrentCons,
70 const char *progname, bool echo, bool quiet);
71
72 static void vacuum_all_databases(ConnParams *cparams,
73 vacuumingOptions *vacopts,
74 bool analyze_in_stages,
75 SimpleStringList *objects,
76 int concurrentCons,
77 const char *progname, bool echo, bool quiet);
78
79 static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
80 vacuumingOptions *vacopts, const char *table);
81
82 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
83 const char *table);
84
85 static void help(const char *progname);
86
87 void check_objfilter(void);
88
89 static char *escape_quotes(const char *src);
90
91 /* For analyze-in-stages mode */
92 #define ANALYZE_NO_STAGE -1
93 #define ANALYZE_NUM_STAGES 3
94
95
96 int
97 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 const char *dbname = NULL;
138 const char *maintenance_db = NULL;
139 char *host = NULL;
140 char *port = NULL;
141 char *username = NULL;
142 enum trivalue prompt_password = TRI_DEFAULT;
143 ConnParams cparams;
144 bool echo = false;
145 bool quiet = false;
146 vacuumingOptions vacopts;
147 bool analyze_in_stages = false;
148 SimpleStringList objects = {NULL, NULL};
149 int concurrentCons = 1;
150 int tbl_count = 0;
151
152 /* initialize options */
153 memset(&vacopts, 0, sizeof(vacopts));
154 vacopts.parallel_workers = -1;
155 vacopts.buffer_usage_limit = NULL;
156 vacopts.no_index_cleanup = false;
157 vacopts.force_index_cleanup = false;
158 vacopts.do_truncate = true;
159 vacopts.process_main = true;
160 vacopts.process_toast = true;
161
162 pg_logging_init(argv[0]);
163 progname = get_progname(argv[0]);
164 set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
165
166 handle_help_version_opts(argc, argv, "vacuumdb", help);
167
168 while ((c = getopt_long(argc, argv, "ad:efFh:j:n:N:p:P:qt:U:vwWzZ", long_options, &optindex)) != -1)
169 {
170 switch (c)
171 {
172 case 'a':
173 objfilter |= OBJFILTER_ALL_DBS;
174 break;
175 case 'd':
176 objfilter |= OBJFILTER_DATABASE;
177 dbname = pg_strdup(optarg);
178 break;
179 case 'e':
180 echo = true;
181 break;
182 case 'f':
183 vacopts.full = true;
184 break;
185 case 'F':
186 vacopts.freeze = true;
187 break;
188 case 'h':
189 host = pg_strdup(optarg);
190 break;
191 case 'j':
192 if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
193 &concurrentCons))
194 exit(1);
195 break;
196 case 'n':
197 objfilter |= OBJFILTER_SCHEMA;
198 simple_string_list_append(&objects, optarg);
199 break;
200 case 'N':
201 objfilter |= OBJFILTER_SCHEMA_EXCLUDE;
202 simple_string_list_append(&objects, optarg);
203 break;
204 case 'p':
205 port = pg_strdup(optarg);
206 break;
207 case 'P':
208 if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
209 &vacopts.parallel_workers))
210 exit(1);
211 break;
212 case 'q':
213 quiet = true;
214 break;
215 case 't':
216 objfilter |= OBJFILTER_TABLE;
217 simple_string_list_append(&objects, optarg);
218 tbl_count++;
219 break;
220 case 'U':
221 username = pg_strdup(optarg);
222 break;
223 case 'v':
224 vacopts.verbose = true;
225 break;
226 case 'w':
227 prompt_password = TRI_NO;
228 break;
229 case 'W':
230 prompt_password = TRI_YES;
231 break;
232 case 'z':
233 vacopts.and_analyze = true;
234 break;
235 case 'Z':
236 vacopts.analyze_only = true;
237 break;
238 case 2:
239 maintenance_db = pg_strdup(optarg);
240 break;
241 case 3:
242 analyze_in_stages = vacopts.analyze_only = true;
243 break;
244 case 4:
245 vacopts.disable_page_skipping = true;
246 break;
247 case 5:
248 vacopts.skip_locked = true;
249 break;
250 case 6:
251 if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
252 &vacopts.min_xid_age))
253 exit(1);
254 break;
255 case 7:
256 if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
257 &vacopts.min_mxid_age))
258 exit(1);
259 break;
260 case 8:
261 vacopts.no_index_cleanup = true;
262 break;
263 case 9:
264 vacopts.force_index_cleanup = true;
265 break;
266 case 10:
267 vacopts.do_truncate = false;
268 break;
269 case 11:
270 vacopts.process_toast = false;
271 break;
272 case 12:
273 vacopts.process_main = false;
274 break;
275 case 13:
276 vacopts.buffer_usage_limit = escape_quotes(optarg);
277 break;
278 default:
279 /* getopt_long already emitted a complaint */
280 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
281 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 if (optind < argc && dbname == NULL)
290 {
291 objfilter |= OBJFILTER_DATABASE;
292 dbname = argv[optind];
293 optind++;
294 }
295
296 if (optind < argc)
297 {
298 pg_log_error("too many command-line arguments (first is \"%s\")",
299 argv[optind]);
300 pg_log_error_hint("Try \"%s --help\" for more information.", progname);
301 exit(1);
302 }
303
304 /*
305 * Validate the combination of filters specified in the command-line
306 * options.
307 */
308 check_objfilter();
309
310 if (vacopts.analyze_only)
311 {
312 if (vacopts.full)
313 pg_fatal("cannot use the \"%s\" option when performing only analyze",
314 "full");
315 if (vacopts.freeze)
316 pg_fatal("cannot use the \"%s\" option when performing only analyze",
317 "freeze");
318 if (vacopts.disable_page_skipping)
319 pg_fatal("cannot use the \"%s\" option when performing only analyze",
320 "disable-page-skipping");
321 if (vacopts.no_index_cleanup)
322 pg_fatal("cannot use the \"%s\" option when performing only analyze",
323 "no-index-cleanup");
324 if (vacopts.force_index_cleanup)
325 pg_fatal("cannot use the \"%s\" option when performing only analyze",
326 "force-index-cleanup");
327 if (!vacopts.do_truncate)
328 pg_fatal("cannot use the \"%s\" option when performing only analyze",
329 "no-truncate");
330 if (!vacopts.process_main)
331 pg_fatal("cannot use the \"%s\" option when performing only analyze",
332 "no-process-main");
333 if (!vacopts.process_toast)
334 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 if (vacopts.parallel_workers >= 0)
341 {
342 if (vacopts.analyze_only)
343 pg_fatal("cannot use the \"%s\" option when performing only analyze",
344 "parallel");
345 if (vacopts.full)
346 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 if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
352 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 if (vacopts.buffer_usage_limit && vacopts.full && !vacopts.and_analyze)
360 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 cparams.pghost = host;
365 cparams.pgport = port;
366 cparams.pguser = username;
367 cparams.prompt_password = prompt_password;
368 cparams.override_dbname = NULL;
369
370 setup_cancel_handler(NULL);
371
372 /* Avoid opening extra connections. */
373 if (tbl_count && (concurrentCons > tbl_count))
374 concurrentCons = tbl_count;
375
376 if (objfilter & OBJFILTER_ALL_DBS)
377 {
378 cparams.dbname = maintenance_db;
379
380 vacuum_all_databases(&cparams, &vacopts,
381 analyze_in_stages,
382 &objects,
383 concurrentCons,
384 progname, echo, quiet);
385 }
386 else
387 {
388 if (dbname == NULL)
389 {
390 if (getenv("PGDATABASE"))
391 dbname = getenv("PGDATABASE");
392 else if (getenv("PGUSER"))
393 dbname = getenv("PGUSER");
394 else
395 dbname = get_user_name_or_exit(progname);
396 }
397
398 cparams.dbname = dbname;
399
400 if (analyze_in_stages)
401 {
402 int stage;
403
404 for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
405 {
406 vacuum_one_database(&cparams, &vacopts,
407 stage,
408 &objects,
409 concurrentCons,
410 progname, echo, quiet);
411 }
412 }
413 else
414 vacuum_one_database(&cparams, &vacopts,
415 ANALYZE_NO_STAGE,
416 &objects,
417 concurrentCons,
418 progname, echo, quiet);
419 }
420
421 exit(0);
422 }
423
424 /*
425 * Verify that the filters used at command line are compatible.
426 */
427 void
428 check_objfilter(void)
429 {
430 if ((objfilter & OBJFILTER_ALL_DBS) &&
431 (objfilter & OBJFILTER_DATABASE))
432 pg_fatal("cannot vacuum all databases and a specific one at the same time");
433
434 if ((objfilter & OBJFILTER_TABLE) &&
435 (objfilter & OBJFILTER_SCHEMA))
436 pg_fatal("cannot vacuum all tables in schema(s) and specific table(s) at the same time");
437
438 if ((objfilter & OBJFILTER_TABLE) &&
439 (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
440 pg_fatal("cannot vacuum specific table(s) and exclude schema(s) at the same time");
441
442 if ((objfilter & OBJFILTER_SCHEMA) &&
443 (objfilter & OBJFILTER_SCHEMA_EXCLUDE))
444 pg_fatal("cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
445 }
446
447 /*
448 * Returns a newly malloc'd version of 'src' with escaped single quotes and
449 * backslashes.
450 */
451 static char *
452 escape_quotes(const char *src)
453 {
454 char *result = escape_single_quotes_ascii(src);
455
456 if (!result)
457 pg_fatal("out of memory");
458 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 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 SimpleStringList dbtables = {NULL, NULL};
490 int i;
491 int ntups;
492 bool failed = false;
493 bool objects_listed = false;
494 const char *initcmd;
495 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 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 conn = connectDatabase(cparams, progname, echo, false, true);
510
511 if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
512 {
513 PQfinish(conn);
514 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
515 "disable-page-skipping", "9.6");
516 }
517
518 if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
519 {
520 PQfinish(conn);
521 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
522 "no-index-cleanup", "12");
523 }
524
525 if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
526 {
527 PQfinish(conn);
528 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
529 "force-index-cleanup", "12");
530 }
531
532 if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
533 {
534 PQfinish(conn);
535 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
536 "no-truncate", "12");
537 }
538
539 if (!vacopts->process_main && PQserverVersion(conn) < 160000)
540 {
541 PQfinish(conn);
542 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
543 "no-process-main", "16");
544 }
545
546 if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
547 {
548 PQfinish(conn);
549 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
550 "no-process-toast", "14");
551 }
552
553 if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
554 {
555 PQfinish(conn);
556 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
557 "skip-locked", "12");
558 }
559
560 if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
561 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
562 "--min-xid-age", "9.6");
563
564 if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
565 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
566 "--min-mxid-age", "9.6");
567
568 if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
569 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
570 "--parallel", "13");
571
572 if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
573 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 vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
578
579 if (!quiet)
580 {
581 if (stage != ANALYZE_NO_STAGE)
582 printf(_("%s: processing database \"%s\": %s\n"),
583 progname, PQdb(conn), _(stage_messages[stage]));
584 else
585 printf(_("%s: vacuuming database \"%s\"\n"),
586 progname, PQdb(conn));
587 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 initPQExpBuffer(&catalog_query);
605 for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
606 {
607 char *just_table = NULL;
608 const char *just_columns = NULL;
609
610 if (!objects_listed)
611 {
612 appendPQExpBufferStr(&catalog_query,
613 "WITH listed_objects (object_oid, column_list) "
614 "AS (\n VALUES (");
615 objects_listed = true;
616 }
617 else
618 appendPQExpBufferStr(&catalog_query, ",\n (");
619
620 if (objfilter & (OBJFILTER_SCHEMA | OBJFILTER_SCHEMA_EXCLUDE))
621 {
622 appendStringLiteralConn(&catalog_query, cell->val, conn);
623 appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
624 }
625
626 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 splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
635 &just_table, &just_columns);
636
637 appendStringLiteralConn(&catalog_query, just_table, conn);
638 appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
639 }
640
641 if (just_columns && just_columns[0] != '\0')
642 appendStringLiteralConn(&catalog_query, just_columns, conn);
643 else
644 appendPQExpBufferStr(&catalog_query, "NULL");
645
646 appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
647
648 pg_free(just_table);
649 }
650
651 /* Finish formatting the CTE */
652 if (objects_listed)
653 appendPQExpBufferStr(&catalog_query, "\n)\n");
654
655 appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
656
657 if (objects_listed)
658 appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
659
660 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 if (objects_listed)
672 {
673 appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
674 " ON listed_objects.object_oid"
675 " OPERATOR(pg_catalog.=) ");
676
677 if (objfilter & OBJFILTER_TABLE)
678 appendPQExpBufferStr(&catalog_query, "c.oid\n");
679 else
680 appendPQExpBufferStr(&catalog_query, "ns.oid\n");
681 }
682
683 /*
684 * Exclude temporary tables, beginning the WHERE clause.
685 */
686 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 if (objects_listed)
695 {
696 if (objfilter & OBJFILTER_SCHEMA_EXCLUDE)
697 appendPQExpBuffer(&catalog_query,
698 " AND listed_objects.object_oid IS NULL\n");
699 else
700 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 if ((objfilter & OBJFILTER_TABLE) == 0)
711 {
712 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 if (vacopts->min_xid_age != 0)
726 {
727 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 if (vacopts->min_mxid_age != 0)
737 {
738 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 appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
752 executeCommand(conn, "RESET search_path;", echo);
753 res = executeQuery(conn, catalog_query.data, echo);
754 termPQExpBuffer(&catalog_query);
755 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 ntups = PQntuples(res);
761 if (ntups == 0)
762 {
763 PQclear(res);
764 PQfinish(conn);
765 return;
766 }
767
768 /*
769 * Build qualified identifiers for each table, including the column list
770 * if given.
771 */
772 initPQExpBuffer(&buf);
773 for (i = 0; i < ntups; i++)
774 {
775 appendPQExpBufferStr(&buf,
776 fmtQualifiedId(PQgetvalue(res, i, 1),
777 PQgetvalue(res, i, 0)));
778
779 if (objects_listed && !PQgetisnull(res, i, 2))
780 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
781
782 simple_string_list_append(&dbtables, buf.data);
783 resetPQExpBuffer(&buf);
784 }
785 termPQExpBuffer(&buf);
786 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 if (concurrentCons > ntups)
793 concurrentCons = ntups;
794 if (concurrentCons <= 0)
795 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 if (stage == ANALYZE_NO_STAGE)
803 initcmd = NULL;
804 else
805 {
806 initcmd = stage_commands[stage];
807 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 sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
816 ParallelSlotsAdoptConn(sa, conn);
817
818 initPQExpBuffer(&sql);
819
820 cell = dbtables.head;
821 do
822 {
823 const char *tabname = cell->val;
824 ParallelSlot *free_slot;
825
826 if (CancelRequested)
827 {
828 failed = true;
829 goto finish;
830 }
831
832 free_slot = ParallelSlotsGetIdle(sa, NULL);
833 if (!free_slot)
834 {
835 failed = true;
836 goto finish;
837 }
838
839 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 ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
847 run_vacuum_command(free_slot->connection, sql.data,
848 echo, tabname);
849
850 cell = cell->next;
851 } while (cell != NULL);
852
853 if (!ParallelSlotsWaitCompletion(sa))
854 {
855 failed = true;
856 goto finish;
857 }
858
859 /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
860 if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE)
861 {
862 const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
863 ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
864
865 if (!free_slot)
866 {
867 failed = true;
868 goto finish;
869 }
870
871 ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
872 run_vacuum_command(free_slot->connection, cmd, echo, NULL);
873
874 if (!ParallelSlotsWaitCompletion(sa))
875 failed = true;
876 }
877
878 finish:
879 ParallelSlotsTerminate(sa);
880 pg_free(sa);
881
882 termPQExpBuffer(&sql);
883
884 if (failed)
885 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 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 conn = connectMaintenanceDatabase(cparams, progname, echo);
909 result = executeQuery(conn,
910 "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
911 echo);
912 PQfinish(conn);
913
914 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 for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
925 {
926 for (i = 0; i < PQntuples(result); i++)
927 {
928 cparams->override_dbname = PQgetvalue(result, i, 0);
929
930 vacuum_one_database(cparams, vacopts,
931 stage,
932 objects,
933 concurrentCons,
934 progname, echo, quiet);
935 }
936 }
937 }
938 else
939 {
940 for (i = 0; i < PQntuples(result); i++)
941 {
942 cparams->override_dbname = PQgetvalue(result, i, 0);
943
944 vacuum_one_database(cparams, vacopts,
945 ANALYZE_NO_STAGE,
946 objects,
947 concurrentCons,
948 progname, echo, quiet);
949 }
950 }
951
952 PQclear(result);
953 }
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 prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
964 vacuumingOptions *vacopts, const char *table)
965 {
966 const char *paren = " (";
967 const char *comma = ", ";
968 const char *sep = paren;
969
970 resetPQExpBuffer(sql);
971
972 if (vacopts->analyze_only)
973 {
974 appendPQExpBufferStr(sql, "ANALYZE");
975
976 /* parenthesized grammar of ANALYZE is supported since v11 */
977 if (serverVersion >= 110000)
978 {
979 if (vacopts->skip_locked)
980 {
981 /* SKIP_LOCKED is supported since v12 */
982 Assert(serverVersion >= 120000);
983 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
984 sep = comma;
985 }
986 if (vacopts->verbose)
987 {
988 appendPQExpBuffer(sql, "%sVERBOSE", sep);
989 sep = comma;
990 }
991 if (vacopts->buffer_usage_limit)
992 {
993 Assert(serverVersion >= 160000);
994 appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
995 vacopts->buffer_usage_limit);
996 sep = comma;
997 }
998 if (sep != paren)
999 appendPQExpBufferChar(sql, ')');
1000 }
1001 else
1002 {
1003 if (vacopts->verbose)
1004 appendPQExpBufferStr(sql, " VERBOSE");
1005 }
1006 }
1007 else
1008 {
1009 appendPQExpBufferStr(sql, "VACUUM");
1010
1011 /* parenthesized grammar of VACUUM is supported since v9.0 */
1012 if (serverVersion >= 90000)
1013 {
1014 if (vacopts->disable_page_skipping)
1015 {
1016 /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
1017 Assert(serverVersion >= 90600);
1018 appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
1019 sep = comma;
1020 }
1021 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 appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
1027 sep = comma;
1028 }
1029 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 appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
1035 sep = comma;
1036 }
1037 if (!vacopts->do_truncate)
1038 {
1039 /* TRUNCATE is supported since v12 */
1040 Assert(serverVersion >= 120000);
1041 appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
1042 sep = comma;
1043 }
1044 if (!vacopts->process_main)
1045 {
1046 /* PROCESS_MAIN is supported since v16 */
1047 Assert(serverVersion >= 160000);
1048 appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
1049 sep = comma;
1050 }
1051 if (!vacopts->process_toast)
1052 {
1053 /* PROCESS_TOAST is supported since v14 */
1054 Assert(serverVersion >= 140000);
1055 appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
1056 sep = comma;
1057 }
1058 if (vacopts->skip_database_stats)
1059 {
1060 /* SKIP_DATABASE_STATS is supported since v16 */
1061 Assert(serverVersion >= 160000);
1062 appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
1063 sep = comma;
1064 }
1065 if (vacopts->skip_locked)
1066 {
1067 /* SKIP_LOCKED is supported since v12 */
1068 Assert(serverVersion >= 120000);
1069 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
1070 sep = comma;
1071 }
1072 if (vacopts->full)
1073 {
1074 appendPQExpBuffer(sql, "%sFULL", sep);
1075 sep = comma;
1076 }
1077 if (vacopts->freeze)
1078 {
1079 appendPQExpBuffer(sql, "%sFREEZE", sep);
1080 sep = comma;
1081 }
1082 if (vacopts->verbose)
1083 {
1084 appendPQExpBuffer(sql, "%sVERBOSE", sep);
1085 sep = comma;
1086 }
1087 if (vacopts->and_analyze)
1088 {
1089 appendPQExpBuffer(sql, "%sANALYZE", sep);
1090 sep = comma;
1091 }
1092 if (vacopts->parallel_workers >= 0)
1093 {
1094 /* PARALLEL is supported since v13 */
1095 Assert(serverVersion >= 130000);
1096 appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
1097 vacopts->parallel_workers);
1098 sep = comma;
1099 }
1100 if (vacopts->buffer_usage_limit)
1101 {
1102 Assert(serverVersion >= 160000);
1103 appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
1104 vacopts->buffer_usage_limit);
1105 sep = comma;
1106 }
1107 if (sep != paren)
1108 appendPQExpBufferChar(sql, ')');
1109 }
1110 else
1111 {
1112 if (vacopts->full)
1113 appendPQExpBufferStr(sql, " FULL");
1114 if (vacopts->freeze)
1115 appendPQExpBufferStr(sql, " FREEZE");
1116 if (vacopts->verbose)
1117 appendPQExpBufferStr(sql, " VERBOSE");
1118 if (vacopts->and_analyze)
1119 appendPQExpBufferStr(sql, " ANALYZE");
1120 }
1121 }
1122
1123 appendPQExpBuffer(sql, " %s;", table);
1124 }
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 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
1134 const char *table)
1135 {
1136 bool status;
1137
1138 if (echo)
1139 printf("%s\n", sql);
1140
1141 status = PQsendQuery(conn, sql) == 1;
1142
1143 if (!status)
1144 {
1145 if (table)
1146 pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1147 table, PQdb(conn), PQerrorMessage(conn));
1148 else
1149 pg_log_error("vacuuming of database \"%s\" failed: %s",
1150 PQdb(conn), PQerrorMessage(conn));
1151 }
1152 }
1153
1154 static void
1155 help(const char *progname)
1156 {
1157 printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1158 printf(_("Usage:\n"));
1159 printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1160 printf(_("\nOptions:\n"));
1161 printf(_(" -a, --all vacuum all databases\n"));
1162 printf(_(" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
1163 printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1164 printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1165 printf(_(" -e, --echo show the commands being sent to the server\n"));
1166 printf(_(" -f, --full do full vacuuming\n"));
1167 printf(_(" -F, --freeze freeze row transaction information\n"));
1168 printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1169 printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1170 printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1171 printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1172 printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1173 printf(_(" --no-process-main skip the main relation\n"));
1174 printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1175 printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1176 printf(_(" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
1177 printf(_(" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
1178 printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1179 printf(_(" -q, --quiet don't write any messages\n"));
1180 printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1181 printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1182 printf(_(" -v, --verbose write a lot of output\n"));
1183 printf(_(" -V, --version output version information, then exit\n"));
1184 printf(_(" -z, --analyze update optimizer statistics\n"));
1185 printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1186 printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1187 " stages for faster results; no vacuum\n"));
1188 printf(_(" -?, --help show this help, then exit\n"));
1189 printf(_("\nConnection options:\n"));
1190 printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1191 printf(_(" -p, --port=PORT database server port\n"));
1192 printf(_(" -U, --username=USERNAME user name to connect as\n"));
1193 printf(_(" -w, --no-password never prompt for password\n"));
1194 printf(_(" -W, --password force password prompt\n"));
1195 printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1196 printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1197 printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1198 printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1199 }
Postgresql Clone from git://git.postgresql.org/git/postgresql.git