PostgreSQL Source Code  git master
vacuumdb.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * vacuumdb
4  *
5  * Portions Copyright (c) 1996-2020, 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 "catalog/pg_class_d.h"
16 
17 #include "common.h"
18 #include "common/connect.h"
19 #include "common/logging.h"
20 #include "fe_utils/cancel.h"
21 #include "fe_utils/simple_list.h"
22 #include "fe_utils/string_utils.h"
23 #include "scripts_parallel.h"
24 
25 
26 /* vacuum options controlled by user flags */
27 typedef struct vacuumingOptions
28 {
30  bool verbose;
32  bool full;
33  bool freeze;
38  int parallel_workers; /* >= 0 indicates user specified the
39  * parallel degree, otherwise -1 */
43 
44 
45 static void vacuum_one_database(const ConnParams *cparams,
46  vacuumingOptions *vacopts,
47  int stage,
48  SimpleStringList *tables,
49  int concurrentCons,
50  const char *progname, bool echo, bool quiet);
51 
52 static void vacuum_all_databases(ConnParams *cparams,
53  vacuumingOptions *vacopts,
54  bool analyze_in_stages,
55  int concurrentCons,
56  const char *progname, bool echo, bool quiet);
57 
58 static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
59  vacuumingOptions *vacopts, const char *table);
60 
61 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
62  const char *table);
63 
64 static void help(const char *progname);
65 
66 /* For analyze-in-stages mode */
67 #define ANALYZE_NO_STAGE -1
68 #define ANALYZE_NUM_STAGES 3
69 
70 
71 int
72 main(int argc, char *argv[])
73 {
74  static struct option long_options[] = {
75  {"host", required_argument, NULL, 'h'},
76  {"port", required_argument, NULL, 'p'},
77  {"username", required_argument, NULL, 'U'},
78  {"no-password", no_argument, NULL, 'w'},
79  {"password", no_argument, NULL, 'W'},
80  {"echo", no_argument, NULL, 'e'},
81  {"quiet", no_argument, NULL, 'q'},
82  {"dbname", required_argument, NULL, 'd'},
83  {"analyze", no_argument, NULL, 'z'},
84  {"analyze-only", no_argument, NULL, 'Z'},
85  {"freeze", no_argument, NULL, 'F'},
86  {"all", no_argument, NULL, 'a'},
87  {"table", required_argument, NULL, 't'},
88  {"full", no_argument, NULL, 'f'},
89  {"verbose", no_argument, NULL, 'v'},
90  {"jobs", required_argument, NULL, 'j'},
91  {"parallel", required_argument, NULL, 'P'},
92  {"maintenance-db", required_argument, NULL, 2},
93  {"analyze-in-stages", no_argument, NULL, 3},
94  {"disable-page-skipping", no_argument, NULL, 4},
95  {"skip-locked", no_argument, NULL, 5},
96  {"min-xid-age", required_argument, NULL, 6},
97  {"min-mxid-age", required_argument, NULL, 7},
98  {"no-index-cleanup", no_argument, NULL, 8},
99  {"no-truncate", no_argument, NULL, 9},
100  {NULL, 0, NULL, 0}
101  };
102 
103  const char *progname;
104  int optindex;
105  int c;
106  const char *dbname = NULL;
107  const char *maintenance_db = NULL;
108  char *host = NULL;
109  char *port = NULL;
110  char *username = NULL;
111  enum trivalue prompt_password = TRI_DEFAULT;
112  ConnParams cparams;
113  bool echo = false;
114  bool quiet = false;
115  vacuumingOptions vacopts;
116  bool analyze_in_stages = false;
117  bool alldb = false;
118  SimpleStringList tables = {NULL, NULL};
119  int concurrentCons = 1;
120  int tbl_count = 0;
121 
122  /* initialize options */
123  memset(&vacopts, 0, sizeof(vacopts));
124  vacopts.parallel_workers = -1;
125  vacopts.do_index_cleanup = true;
126  vacopts.do_truncate = true;
127 
128  pg_logging_init(argv[0]);
129  progname = get_progname(argv[0]);
130  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
131 
132  handle_help_version_opts(argc, argv, "vacuumdb", help);
133 
134  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:", long_options, &optindex)) != -1)
135  {
136  switch (c)
137  {
138  case 'h':
139  host = pg_strdup(optarg);
140  break;
141  case 'p':
142  port = pg_strdup(optarg);
143  break;
144  case 'U':
145  username = pg_strdup(optarg);
146  break;
147  case 'w':
148  prompt_password = TRI_NO;
149  break;
150  case 'W':
151  prompt_password = TRI_YES;
152  break;
153  case 'e':
154  echo = true;
155  break;
156  case 'q':
157  quiet = true;
158  break;
159  case 'd':
160  dbname = pg_strdup(optarg);
161  break;
162  case 'z':
163  vacopts.and_analyze = true;
164  break;
165  case 'Z':
166  vacopts.analyze_only = true;
167  break;
168  case 'F':
169  vacopts.freeze = true;
170  break;
171  case 'a':
172  alldb = true;
173  break;
174  case 't':
175  {
177  tbl_count++;
178  break;
179  }
180  case 'f':
181  vacopts.full = true;
182  break;
183  case 'v':
184  vacopts.verbose = true;
185  break;
186  case 'j':
187  concurrentCons = atoi(optarg);
188  if (concurrentCons <= 0)
189  {
190  pg_log_error("number of parallel jobs must be at least 1");
191  exit(1);
192  }
193  break;
194  case 'P':
195  vacopts.parallel_workers = atoi(optarg);
196  if (vacopts.parallel_workers < 0)
197  {
198  pg_log_error("parallel vacuum degree must be a non-negative integer");
199  exit(1);
200  }
201  break;
202  case 2:
203  maintenance_db = pg_strdup(optarg);
204  break;
205  case 3:
206  analyze_in_stages = vacopts.analyze_only = true;
207  break;
208  case 4:
209  vacopts.disable_page_skipping = true;
210  break;
211  case 5:
212  vacopts.skip_locked = true;
213  break;
214  case 6:
215  vacopts.min_xid_age = atoi(optarg);
216  if (vacopts.min_xid_age <= 0)
217  {
218  pg_log_error("minimum transaction ID age must be at least 1");
219  exit(1);
220  }
221  break;
222  case 7:
223  vacopts.min_mxid_age = atoi(optarg);
224  if (vacopts.min_mxid_age <= 0)
225  {
226  pg_log_error("minimum multixact ID age must be at least 1");
227  exit(1);
228  }
229  break;
230  case 8:
231  vacopts.do_index_cleanup = false;
232  break;
233  case 9:
234  vacopts.do_truncate = false;
235  break;
236  default:
237  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
238  exit(1);
239  }
240  }
241 
242  /*
243  * Non-option argument specifies database name as long as it wasn't
244  * already specified with -d / --dbname
245  */
246  if (optind < argc && dbname == NULL)
247  {
248  dbname = argv[optind];
249  optind++;
250  }
251 
252  if (optind < argc)
253  {
254  pg_log_error("too many command-line arguments (first is \"%s\")",
255  argv[optind]);
256  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
257  exit(1);
258  }
259 
260  if (vacopts.analyze_only)
261  {
262  if (vacopts.full)
263  {
264  pg_log_error("cannot use the \"%s\" option when performing only analyze",
265  "full");
266  exit(1);
267  }
268  if (vacopts.freeze)
269  {
270  pg_log_error("cannot use the \"%s\" option when performing only analyze",
271  "freeze");
272  exit(1);
273  }
274  if (vacopts.disable_page_skipping)
275  {
276  pg_log_error("cannot use the \"%s\" option when performing only analyze",
277  "disable-page-skipping");
278  exit(1);
279  }
280  if (!vacopts.do_index_cleanup)
281  {
282  pg_log_error("cannot use the \"%s\" option when performing only analyze",
283  "no-index-cleanup");
284  exit(1);
285  }
286  if (!vacopts.do_truncate)
287  {
288  pg_log_error("cannot use the \"%s\" option when performing only analyze",
289  "no-truncate");
290  exit(1);
291  }
292  /* allow 'and_analyze' with 'analyze_only' */
293  }
294 
295  /* Prohibit full and analyze_only options with parallel option */
296  if (vacopts.parallel_workers >= 0)
297  {
298  if (vacopts.analyze_only)
299  {
300  pg_log_error("cannot use the \"%s\" option when performing only analyze",
301  "parallel");
302  exit(1);
303  }
304  if (vacopts.full)
305  {
306  pg_log_error("cannot use the \"%s\" option when performing full vacuum",
307  "parallel");
308  exit(1);
309  }
310  }
311 
312  /* fill cparams except for dbname, which is set below */
313  cparams.pghost = host;
314  cparams.pgport = port;
315  cparams.pguser = username;
316  cparams.prompt_password = prompt_password;
317  cparams.override_dbname = NULL;
318 
319  setup_cancel_handler(NULL);
320 
321  /* Avoid opening extra connections. */
322  if (tbl_count && (concurrentCons > tbl_count))
323  concurrentCons = tbl_count;
324 
325  if (alldb)
326  {
327  if (dbname)
328  {
329  pg_log_error("cannot vacuum all databases and a specific one at the same time");
330  exit(1);
331  }
332  if (tables.head != NULL)
333  {
334  pg_log_error("cannot vacuum specific table(s) in all databases");
335  exit(1);
336  }
337 
338  cparams.dbname = maintenance_db;
339 
340  vacuum_all_databases(&cparams, &vacopts,
341  analyze_in_stages,
342  concurrentCons,
343  progname, echo, quiet);
344  }
345  else
346  {
347  if (dbname == NULL)
348  {
349  if (getenv("PGDATABASE"))
350  dbname = getenv("PGDATABASE");
351  else if (getenv("PGUSER"))
352  dbname = getenv("PGUSER");
353  else
354  dbname = get_user_name_or_exit(progname);
355  }
356 
357  cparams.dbname = dbname;
358 
359  if (analyze_in_stages)
360  {
361  int stage;
362 
363  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
364  {
365  vacuum_one_database(&cparams, &vacopts,
366  stage,
367  &tables,
368  concurrentCons,
369  progname, echo, quiet);
370  }
371  }
372  else
373  vacuum_one_database(&cparams, &vacopts,
375  &tables,
376  concurrentCons,
377  progname, echo, quiet);
378  }
379 
380  exit(0);
381 }
382 
383 /*
384  * vacuum_one_database
385  *
386  * Process tables in the given database. If the 'tables' list is empty,
387  * process all tables in the database.
388  *
389  * Note that this function is only concerned with running exactly one stage
390  * when in analyze-in-stages mode; caller must iterate on us if necessary.
391  *
392  * If concurrentCons is > 1, multiple connections are used to vacuum tables
393  * in parallel. In this case and if the table list is empty, we first obtain
394  * a list of tables from the database.
395  */
396 static void
398  vacuumingOptions *vacopts,
399  int stage,
400  SimpleStringList *tables,
401  int concurrentCons,
402  const char *progname, bool echo, bool quiet)
403 {
404  PQExpBufferData sql;
406  PQExpBufferData catalog_query;
407  PGresult *res;
408  PGconn *conn;
409  SimpleStringListCell *cell;
410  ParallelSlot *slots;
411  SimpleStringList dbtables = {NULL, NULL};
412  int i;
413  int ntups;
414  bool failed = false;
415  bool tables_listed = false;
416  bool has_where = false;
417  const char *stage_commands[] = {
418  "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
419  "SET default_statistics_target=10; RESET vacuum_cost_delay;",
420  "RESET default_statistics_target;"
421  };
422  const char *stage_messages[] = {
423  gettext_noop("Generating minimal optimizer statistics (1 target)"),
424  gettext_noop("Generating medium optimizer statistics (10 targets)"),
425  gettext_noop("Generating default (full) optimizer statistics")
426  };
427 
428  Assert(stage == ANALYZE_NO_STAGE ||
429  (stage >= 0 && stage < ANALYZE_NUM_STAGES));
430 
431  conn = connectDatabase(cparams, progname, echo, false, true);
432 
433  if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
434  {
435  PQfinish(conn);
436  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
437  "disable-page-skipping", "9.6");
438  exit(1);
439  }
440 
441  if (!vacopts->do_index_cleanup && PQserverVersion(conn) < 120000)
442  {
443  PQfinish(conn);
444  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
445  "no-index-cleanup", "12");
446  exit(1);
447  }
448 
449  if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
450  {
451  PQfinish(conn);
452  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
453  "no-truncate", "12");
454  exit(1);
455  }
456 
457  if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
458  {
459  PQfinish(conn);
460  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
461  "skip-locked", "12");
462  exit(1);
463  }
464 
465  if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
466  {
467  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
468  "--min-xid-age", "9.6");
469  exit(1);
470  }
471 
472  if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
473  {
474  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
475  "--min-mxid-age", "9.6");
476  exit(1);
477  }
478 
479  if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
480  {
481  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
482  "--parallel", "13");
483  exit(1);
484  }
485 
486  if (!quiet)
487  {
488  if (stage != ANALYZE_NO_STAGE)
489  printf(_("%s: processing database \"%s\": %s\n"),
490  progname, PQdb(conn), _(stage_messages[stage]));
491  else
492  printf(_("%s: vacuuming database \"%s\"\n"),
493  progname, PQdb(conn));
494  fflush(stdout);
495  }
496 
497  /*
498  * Prepare the list of tables to process by querying the catalogs.
499  *
500  * Since we execute the constructed query with the default search_path
501  * (which could be unsafe), everything in this query MUST be fully
502  * qualified.
503  *
504  * First, build a WITH clause for the catalog query if any tables were
505  * specified, with a set of values made of relation names and their
506  * optional set of columns. This is used to match any provided column
507  * lists with the generated qualified identifiers and to filter for the
508  * tables provided via --table. If a listed table does not exist, the
509  * catalog query will fail.
510  */
511  initPQExpBuffer(&catalog_query);
512  for (cell = tables ? tables->head : NULL; cell; cell = cell->next)
513  {
514  char *just_table;
515  const char *just_columns;
516 
517  /*
518  * Split relation and column names given by the user, this is used to
519  * feed the CTE with values on which are performed pre-run validity
520  * checks as well. For now these happen only on the relation name.
521  */
523  &just_table, &just_columns);
524 
525  if (!tables_listed)
526  {
527  appendPQExpBufferStr(&catalog_query,
528  "WITH listed_tables (table_oid, column_list) "
529  "AS (\n VALUES (");
530  tables_listed = true;
531  }
532  else
533  appendPQExpBufferStr(&catalog_query, ",\n (");
534 
535  appendStringLiteralConn(&catalog_query, just_table, conn);
536  appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
537 
538  if (just_columns && just_columns[0] != '\0')
539  appendStringLiteralConn(&catalog_query, just_columns, conn);
540  else
541  appendPQExpBufferStr(&catalog_query, "NULL");
542 
543  appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
544 
545  pg_free(just_table);
546  }
547 
548  /* Finish formatting the CTE */
549  if (tables_listed)
550  appendPQExpBufferStr(&catalog_query, "\n)\n");
551 
552  appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
553 
554  if (tables_listed)
555  appendPQExpBufferStr(&catalog_query, ", listed_tables.column_list");
556 
557  appendPQExpBufferStr(&catalog_query,
558  " FROM pg_catalog.pg_class c\n"
559  " JOIN pg_catalog.pg_namespace ns"
560  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
561  " LEFT JOIN pg_catalog.pg_class t"
562  " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
563 
564  /* Used to match the tables listed by the user */
565  if (tables_listed)
566  appendPQExpBufferStr(&catalog_query, " JOIN listed_tables"
567  " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n");
568 
569  /*
570  * If no tables were listed, filter for the relevant relation types. If
571  * tables were given via --table, don't bother filtering by relation type.
572  * Instead, let the server decide whether a given relation can be
573  * processed in which case the user will know about it.
574  */
575  if (!tables_listed)
576  {
577  appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
578  CppAsString2(RELKIND_RELATION) ", "
579  CppAsString2(RELKIND_MATVIEW) "])\n");
580  has_where = true;
581  }
582 
583  /*
584  * For --min-xid-age and --min-mxid-age, the age of the relation is the
585  * greatest of the ages of the main relation and its associated TOAST
586  * table. The commands generated by vacuumdb will also process the TOAST
587  * table for the relation if necessary, so it does not need to be
588  * considered separately.
589  */
590  if (vacopts->min_xid_age != 0)
591  {
592  appendPQExpBuffer(&catalog_query,
593  " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
594  " pg_catalog.age(t.relfrozenxid)) "
595  " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
596  " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
597  " '0'::pg_catalog.xid\n",
598  has_where ? "AND" : "WHERE", vacopts->min_xid_age);
599  has_where = true;
600  }
601 
602  if (vacopts->min_mxid_age != 0)
603  {
604  appendPQExpBuffer(&catalog_query,
605  " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
606  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
607  " '%d'::pg_catalog.int4\n"
608  " AND c.relminmxid OPERATOR(pg_catalog.!=)"
609  " '0'::pg_catalog.xid\n",
610  has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
611  has_where = true;
612  }
613 
614  /*
615  * Execute the catalog query. We use the default search_path for this
616  * query for consistency with table lookups done elsewhere by the user.
617  */
618  appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
619  executeCommand(conn, "RESET search_path;", echo);
620  res = executeQuery(conn, catalog_query.data, echo);
621  termPQExpBuffer(&catalog_query);
623 
624  /*
625  * If no rows are returned, there are no matching tables, so we are done.
626  */
627  ntups = PQntuples(res);
628  if (ntups == 0)
629  {
630  PQclear(res);
631  PQfinish(conn);
632  return;
633  }
634 
635  /*
636  * Build qualified identifiers for each table, including the column list
637  * if given.
638  */
639  initPQExpBuffer(&buf);
640  for (i = 0; i < ntups; i++)
641  {
643  fmtQualifiedId(PQgetvalue(res, i, 1),
644  PQgetvalue(res, i, 0)));
645 
646  if (tables_listed && !PQgetisnull(res, i, 2))
647  appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
648 
649  simple_string_list_append(&dbtables, buf.data);
650  resetPQExpBuffer(&buf);
651  }
652  termPQExpBuffer(&buf);
653  PQclear(res);
654 
655  /*
656  * Ensure concurrentCons is sane. If there are more connections than
657  * vacuumable relations, we don't need to use them all.
658  */
659  if (concurrentCons > ntups)
660  concurrentCons = ntups;
661  if (concurrentCons <= 0)
662  concurrentCons = 1;
663 
664  /*
665  * Setup the database connections. We reuse the connection we already have
666  * for the first slot. If not in parallel mode, the first slot in the
667  * array contains the connection.
668  */
669  slots = ParallelSlotsSetup(cparams, progname, echo, conn, concurrentCons);
670 
671  /*
672  * Prepare all the connections to run the appropriate analyze stage, if
673  * caller requested that mode.
674  */
675  if (stage != ANALYZE_NO_STAGE)
676  {
677  int j;
678 
679  /* We already emitted the message above */
680 
681  for (j = 0; j < concurrentCons; j++)
682  executeCommand((slots + j)->connection,
683  stage_commands[stage], echo);
684  }
685 
686  initPQExpBuffer(&sql);
687 
688  cell = dbtables.head;
689  do
690  {
691  const char *tabname = cell->val;
692  ParallelSlot *free_slot;
693 
694  if (CancelRequested)
695  {
696  failed = true;
697  goto finish;
698  }
699 
700  free_slot = ParallelSlotsGetIdle(slots, concurrentCons);
701  if (!free_slot)
702  {
703  failed = true;
704  goto finish;
705  }
706 
708  vacopts, tabname);
709 
710  /*
711  * Execute the vacuum. All errors are handled in processQueryResult
712  * through ParallelSlotsGetIdle.
713  */
714  run_vacuum_command(free_slot->connection, sql.data,
715  echo, tabname);
716 
717  cell = cell->next;
718  } while (cell != NULL);
719 
720  if (!ParallelSlotsWaitCompletion(slots, concurrentCons))
721  failed = true;
722 
723 finish:
724  ParallelSlotsTerminate(slots, concurrentCons);
725  pg_free(slots);
726 
727  termPQExpBuffer(&sql);
728 
729  if (failed)
730  exit(1);
731 }
732 
733 /*
734  * Vacuum/analyze all connectable databases.
735  *
736  * In analyze-in-stages mode, we process all databases in one stage before
737  * moving on to the next stage. That ensure minimal stats are available
738  * quickly everywhere before generating more detailed ones.
739  */
740 static void
742  vacuumingOptions *vacopts,
743  bool analyze_in_stages,
744  int concurrentCons,
745  const char *progname, bool echo, bool quiet)
746 {
747  PGconn *conn;
748  PGresult *result;
749  int stage;
750  int i;
751 
752  conn = connectMaintenanceDatabase(cparams, progname, echo);
753  result = executeQuery(conn,
754  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
755  echo);
756  PQfinish(conn);
757 
758  if (analyze_in_stages)
759  {
760  /*
761  * When analyzing all databases in stages, we analyze them all in the
762  * fastest stage first, so that initial statistics become available
763  * for all of them as soon as possible.
764  *
765  * This means we establish several times as many connections, but
766  * that's a secondary consideration.
767  */
768  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
769  {
770  for (i = 0; i < PQntuples(result); i++)
771  {
772  cparams->override_dbname = PQgetvalue(result, i, 0);
773 
774  vacuum_one_database(cparams, vacopts,
775  stage,
776  NULL,
777  concurrentCons,
778  progname, echo, quiet);
779  }
780  }
781  }
782  else
783  {
784  for (i = 0; i < PQntuples(result); i++)
785  {
786  cparams->override_dbname = PQgetvalue(result, i, 0);
787 
788  vacuum_one_database(cparams, vacopts,
790  NULL,
791  concurrentCons,
792  progname, echo, quiet);
793  }
794  }
795 
796  PQclear(result);
797 }
798 
799 /*
800  * Construct a vacuum/analyze command to run based on the given options, in the
801  * given string buffer, which may contain previous garbage.
802  *
803  * The table name used must be already properly quoted. The command generated
804  * depends on the server version involved and it is semicolon-terminated.
805  */
806 static void
807 prepare_vacuum_command(PQExpBuffer sql, int serverVersion,
808  vacuumingOptions *vacopts, const char *table)
809 {
810  const char *paren = " (";
811  const char *comma = ", ";
812  const char *sep = paren;
813 
814  resetPQExpBuffer(sql);
815 
816  if (vacopts->analyze_only)
817  {
818  appendPQExpBufferStr(sql, "ANALYZE");
819 
820  /* parenthesized grammar of ANALYZE is supported since v11 */
821  if (serverVersion >= 110000)
822  {
823  if (vacopts->skip_locked)
824  {
825  /* SKIP_LOCKED is supported since v12 */
826  Assert(serverVersion >= 120000);
827  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
828  sep = comma;
829  }
830  if (vacopts->verbose)
831  {
832  appendPQExpBuffer(sql, "%sVERBOSE", sep);
833  sep = comma;
834  }
835  if (sep != paren)
836  appendPQExpBufferChar(sql, ')');
837  }
838  else
839  {
840  if (vacopts->verbose)
841  appendPQExpBufferStr(sql, " VERBOSE");
842  }
843  }
844  else
845  {
846  appendPQExpBufferStr(sql, "VACUUM");
847 
848  /* parenthesized grammar of VACUUM is supported since v9.0 */
849  if (serverVersion >= 90000)
850  {
851  if (vacopts->disable_page_skipping)
852  {
853  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
854  Assert(serverVersion >= 90600);
855  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
856  sep = comma;
857  }
858  if (!vacopts->do_index_cleanup)
859  {
860  /* INDEX_CLEANUP is supported since v12 */
861  Assert(serverVersion >= 120000);
862  appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
863  sep = comma;
864  }
865  if (!vacopts->do_truncate)
866  {
867  /* TRUNCATE is supported since v12 */
868  Assert(serverVersion >= 120000);
869  appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
870  sep = comma;
871  }
872  if (vacopts->skip_locked)
873  {
874  /* SKIP_LOCKED is supported since v12 */
875  Assert(serverVersion >= 120000);
876  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
877  sep = comma;
878  }
879  if (vacopts->full)
880  {
881  appendPQExpBuffer(sql, "%sFULL", sep);
882  sep = comma;
883  }
884  if (vacopts->freeze)
885  {
886  appendPQExpBuffer(sql, "%sFREEZE", sep);
887  sep = comma;
888  }
889  if (vacopts->verbose)
890  {
891  appendPQExpBuffer(sql, "%sVERBOSE", sep);
892  sep = comma;
893  }
894  if (vacopts->and_analyze)
895  {
896  appendPQExpBuffer(sql, "%sANALYZE", sep);
897  sep = comma;
898  }
899  if (vacopts->parallel_workers >= 0)
900  {
901  /* PARALLEL is supported since v13 */
902  Assert(serverVersion >= 130000);
903  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
904  vacopts->parallel_workers);
905  sep = comma;
906  }
907  if (sep != paren)
908  appendPQExpBufferChar(sql, ')');
909  }
910  else
911  {
912  if (vacopts->full)
913  appendPQExpBufferStr(sql, " FULL");
914  if (vacopts->freeze)
915  appendPQExpBufferStr(sql, " FREEZE");
916  if (vacopts->verbose)
917  appendPQExpBufferStr(sql, " VERBOSE");
918  if (vacopts->and_analyze)
919  appendPQExpBufferStr(sql, " ANALYZE");
920  }
921  }
922 
923  appendPQExpBuffer(sql, " %s;", table);
924 }
925 
926 /*
927  * Send a vacuum/analyze command to the server, returning after sending the
928  * command.
929  *
930  * Any errors during command execution are reported to stderr.
931  */
932 static void
933 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
934  const char *table)
935 {
936  bool status;
937 
938  if (echo)
939  printf("%s\n", sql);
940 
941  status = PQsendQuery(conn, sql) == 1;
942 
943  if (!status)
944  {
945  if (table)
946  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
947  table, PQdb(conn), PQerrorMessage(conn));
948  else
949  pg_log_error("vacuuming of database \"%s\" failed: %s",
950  PQdb(conn), PQerrorMessage(conn));
951  }
952 }
953 
954 static void
955 help(const char *progname)
956 {
957  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
958  printf(_("Usage:\n"));
959  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
960  printf(_("\nOptions:\n"));
961  printf(_(" -a, --all vacuum all databases\n"));
962  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
963  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
964  printf(_(" -e, --echo show the commands being sent to the server\n"));
965  printf(_(" -f, --full do full vacuuming\n"));
966  printf(_(" -F, --freeze freeze row transaction information\n"));
967  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
968  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
969  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
970  printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
971  printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
972  printf(_(" -P, --parallel=PARALLEL_DEGREE use this many background workers for vacuum, if available\n"));
973  printf(_(" -q, --quiet don't write any messages\n"));
974  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
975  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
976  printf(_(" -v, --verbose write a lot of output\n"));
977  printf(_(" -V, --version output version information, then exit\n"));
978  printf(_(" -z, --analyze update optimizer statistics\n"));
979  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
980  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
981  " stages for faster results; no vacuum\n"));
982  printf(_(" -?, --help show this help, then exit\n"));
983  printf(_("\nConnection options:\n"));
984  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
985  printf(_(" -p, --port=PORT database server port\n"));
986  printf(_(" -U, --username=USERNAME user name to connect as\n"));
987  printf(_(" -w, --no-password never prompt for password\n"));
988  printf(_(" -W, --password force password prompt\n"));
989  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
990  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
991  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
992  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
993 }
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1875
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6669
char * pgport
Definition: pg_backup.h:66
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
bool skip_locked
Definition: vacuumdb.c:35
static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion, vacuumingOptions *vacopts, const char *table)
Definition: vacuumdb.c:807
const char * get_progname(const char *argv0)
Definition: path.c:453
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
#define pg_log_error(...)
Definition: logging.h:80
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
void pg_logging_init(const char *argv0)
Definition: logging.c:81
struct vacuumingOptions vacuumingOptions
#define gettext_noop(x)
Definition: c.h:1139
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4174
#define printf(...)
Definition: port.h:221
static void setup_cancel_handler(void)
Definition: parallel.c:613
int parallel_workers
Definition: vacuumdb.c:38
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:67
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6659
const char * progname
Definition: pg_standby.c:36
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
#define fprintf
Definition: port.h:219
int PQclientEncoding(const PGconn *conn)
Definition: fe-connect.c:6729
char * dbname
Definition: pg_backup.h:65
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1234
#define required_argument
Definition: getopt_long.h:25
bool do_truncate
Definition: vacuumdb.c:41
int optind
Definition: getopt.c:50
PGconn * conn
Definition: streamutil.c:54
int main(int argc, char *argv[])
Definition: vacuumdb.c:72
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1898
bool ParallelSlotsWaitCompletion(ParallelSlot *slots, int numslots)
char * c
static char * buf
Definition: pg_test_fsync.c:68
bool do_index_cleanup
Definition: vacuumdb.c:40
struct SimpleStringListCell * next
Definition: simple_list.h:34
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
bool and_analyze
Definition: vacuumdb.c:31
PGconn * connection
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
Definition: vacuumdb.c:933
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
Definition: common.c:173
void ParallelSlotsTerminate(ParallelSlot *slots, int numslots)
char * override_dbname
Definition: pg_backup.h:72
static int port
Definition: pg_regress.c:92
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: common.c:34
char * pghost
Definition: pg_backup.h:67
enum trivalue prompt_password
Definition: common.h:32
ParallelSlot * ParallelSlotsGetIdle(ParallelSlot *slots, int numslots)
trivalue
Definition: vacuumlo.c:34
static void help(const char *progname)
Definition: vacuumdb.c:955
#define CppAsString2(x)
Definition: c.h:223
#define no_argument
Definition: getopt_long.h:24
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1157
ParallelSlot * ParallelSlotsSetup(const ConnParams *cparams, const char *progname, bool echo, PGconn *conn, int numslots)
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
static char * username
Definition: initdb.c:134
void PQclear(PGresult *res)
Definition: fe-exec.c:694
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6519
#define Assert(condition)
Definition: c.h:746
static PGconn * connectDatabase(const char *dbname, const char *connstr, const char *pghost, const char *pgport, const char *pguser, trivalue prompt_password, bool fail_on_error)
Definition: pg_dumpall.c:1636
SimpleStringListCell * head
Definition: simple_list.h:42
char * dbname
Definition: streamutil.c:51
const char * fmtQualifiedId(const char *schema, const char *id)
Definition: string_utils.c:145
void pg_free(void *ptr)
Definition: fe_memutils.c:105
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:68
bool disable_page_skipping
Definition: vacuumdb.c:34
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
Definition: string_utils.c:293
bool analyze_only
Definition: vacuumdb.c:29
volatile sig_atomic_t CancelRequested
Definition: cancel.c:52
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:434
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:741
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
Definition: common.c:351
char * optarg
Definition: getopt.c:52
static void vacuum_one_database(const ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:397
int i
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:227
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3188
#define _(x)
Definition: elog.c:88
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
const char * pguser
Definition: common.h:31
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74