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