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