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