PostgreSQL Source Code  git master
vacuumdb.c
Go to the documentation of this file.
1 /*-------------------------------------------------------------------------
2  *
3  * vacuumdb
4  *
5  * Portions Copyright (c) 1996-2018, 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 #ifdef HAVE_SYS_SELECT_H
16 #include <sys/select.h>
17 #endif
18 
19 #include "catalog/pg_class_d.h"
20 
21 #include "common.h"
22 #include "fe_utils/simple_list.h"
23 #include "fe_utils/string_utils.h"
24 
25 
26 #define ERRCODE_UNDEFINED_TABLE "42P01"
27 
28 /* Parallel vacuuming stuff */
29 typedef struct ParallelSlot
30 {
31  PGconn *connection; /* One connection */
32  bool isFree; /* Is it known to be idle? */
33 } ParallelSlot;
34 
35 /* vacuum options controlled by user flags */
36 typedef struct vacuumingOptions
37 {
39  bool verbose;
41  bool full;
42  bool freeze;
44 
45 
46 static void vacuum_one_database(const char *dbname, vacuumingOptions *vacopts,
47  int stage,
48  SimpleStringList *tables,
49  const char *host, const char *port,
50  const char *username, enum trivalue prompt_password,
51  int concurrentCons,
52  const char *progname, bool echo, bool quiet);
53 
54 static void vacuum_all_databases(vacuumingOptions *vacopts,
55  bool analyze_in_stages,
56  const char *maintenance_db,
57  const char *host, const char *port,
58  const char *username, enum trivalue prompt_password,
59  int concurrentCons,
60  const char *progname, bool echo, bool quiet);
61 
63  vacuumingOptions *vacopts, const char *table,
64  bool table_pre_qualified,
65  const char *progname, bool echo);
66 
67 static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
68  const char *table, const char *progname, bool async);
69 
70 static ParallelSlot *GetIdleSlot(ParallelSlot slots[], int numslots,
71  const char *progname);
72 
73 static bool ProcessQueryResult(PGconn *conn, PGresult *result,
74  const char *progname);
75 
76 static bool GetQueryResult(PGconn *conn, const char *progname);
77 
78 static void DisconnectDatabase(ParallelSlot *slot);
79 
80 static int select_loop(int maxFd, fd_set *workerset, bool *aborting);
81 
82 static void init_slot(ParallelSlot *slot, PGconn *conn);
83 
84 static void help(const char *progname);
85 
86 /* For analyze-in-stages mode */
87 #define ANALYZE_NO_STAGE -1
88 #define ANALYZE_NUM_STAGES 3
89 
90 
91 int
92 main(int argc, char *argv[])
93 {
94  static struct option long_options[] = {
95  {"host", required_argument, NULL, 'h'},
96  {"port", required_argument, NULL, 'p'},
97  {"username", required_argument, NULL, 'U'},
98  {"no-password", no_argument, NULL, 'w'},
99  {"password", no_argument, NULL, 'W'},
100  {"echo", no_argument, NULL, 'e'},
101  {"quiet", no_argument, NULL, 'q'},
102  {"dbname", required_argument, NULL, 'd'},
103  {"analyze", no_argument, NULL, 'z'},
104  {"analyze-only", no_argument, NULL, 'Z'},
105  {"freeze", no_argument, NULL, 'F'},
106  {"all", no_argument, NULL, 'a'},
107  {"table", required_argument, NULL, 't'},
108  {"full", no_argument, NULL, 'f'},
109  {"verbose", no_argument, NULL, 'v'},
110  {"jobs", required_argument, NULL, 'j'},
111  {"maintenance-db", required_argument, NULL, 2},
112  {"analyze-in-stages", no_argument, NULL, 3},
113  {NULL, 0, NULL, 0}
114  };
115 
116  const char *progname;
117  int optindex;
118  int c;
119  const char *dbname = NULL;
120  const char *maintenance_db = NULL;
121  char *host = NULL;
122  char *port = NULL;
123  char *username = NULL;
124  enum trivalue prompt_password = TRI_DEFAULT;
125  bool echo = false;
126  bool quiet = false;
127  vacuumingOptions vacopts;
128  bool analyze_in_stages = false;
129  bool alldb = false;
130  SimpleStringList tables = {NULL, NULL};
131  int concurrentCons = 1;
132  int tbl_count = 0;
133 
134  /* initialize options to all false */
135  memset(&vacopts, 0, sizeof(vacopts));
136 
137  progname = get_progname(argv[0]);
138 
139  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
140 
141  handle_help_version_opts(argc, argv, "vacuumdb", help);
142 
143  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:", long_options, &optindex)) != -1)
144  {
145  switch (c)
146  {
147  case 'h':
148  host = pg_strdup(optarg);
149  break;
150  case 'p':
151  port = pg_strdup(optarg);
152  break;
153  case 'U':
154  username = pg_strdup(optarg);
155  break;
156  case 'w':
157  prompt_password = TRI_NO;
158  break;
159  case 'W':
160  prompt_password = TRI_YES;
161  break;
162  case 'e':
163  echo = true;
164  break;
165  case 'q':
166  quiet = true;
167  break;
168  case 'd':
169  dbname = pg_strdup(optarg);
170  break;
171  case 'z':
172  vacopts.and_analyze = true;
173  break;
174  case 'Z':
175  vacopts.analyze_only = true;
176  break;
177  case 'F':
178  vacopts.freeze = true;
179  break;
180  case 'a':
181  alldb = true;
182  break;
183  case 't':
184  {
186  tbl_count++;
187  break;
188  }
189  case 'f':
190  vacopts.full = true;
191  break;
192  case 'v':
193  vacopts.verbose = true;
194  break;
195  case 'j':
196  concurrentCons = atoi(optarg);
197  if (concurrentCons <= 0)
198  {
199  fprintf(stderr, _("%s: number of parallel jobs must be at least 1\n"),
200  progname);
201  exit(1);
202  }
203  if (concurrentCons > FD_SETSIZE - 1)
204  {
205  fprintf(stderr, _("%s: too many parallel jobs requested (maximum: %d)\n"),
206  progname, FD_SETSIZE - 1);
207  exit(1);
208  }
209  break;
210  case 2:
211  maintenance_db = pg_strdup(optarg);
212  break;
213  case 3:
214  analyze_in_stages = vacopts.analyze_only = true;
215  break;
216  default:
217  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
218  exit(1);
219  }
220  }
221 
222  /*
223  * Non-option argument specifies database name as long as it wasn't
224  * already specified with -d / --dbname
225  */
226  if (optind < argc && dbname == NULL)
227  {
228  dbname = argv[optind];
229  optind++;
230  }
231 
232  if (optind < argc)
233  {
234  fprintf(stderr, _("%s: too many command-line arguments (first is \"%s\")\n"),
235  progname, argv[optind]);
236  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
237  exit(1);
238  }
239 
240  if (vacopts.analyze_only)
241  {
242  if (vacopts.full)
243  {
244  fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
245  progname, "full");
246  exit(1);
247  }
248  if (vacopts.freeze)
249  {
250  fprintf(stderr, _("%s: cannot use the \"%s\" option when performing only analyze\n"),
251  progname, "freeze");
252  exit(1);
253  }
254  /* allow 'and_analyze' with 'analyze_only' */
255  }
256 
258 
259  /* Avoid opening extra connections. */
260  if (tbl_count && (concurrentCons > tbl_count))
261  concurrentCons = tbl_count;
262 
263  if (alldb)
264  {
265  if (dbname)
266  {
267  fprintf(stderr, _("%s: cannot vacuum all databases and a specific one at the same time\n"),
268  progname);
269  exit(1);
270  }
271  if (tables.head != NULL)
272  {
273  fprintf(stderr, _("%s: cannot vacuum specific table(s) in all databases\n"),
274  progname);
275  exit(1);
276  }
277 
278  vacuum_all_databases(&vacopts,
279  analyze_in_stages,
280  maintenance_db,
281  host, port, username, prompt_password,
282  concurrentCons,
283  progname, echo, quiet);
284  }
285  else
286  {
287  if (dbname == NULL)
288  {
289  if (getenv("PGDATABASE"))
290  dbname = getenv("PGDATABASE");
291  else if (getenv("PGUSER"))
292  dbname = getenv("PGUSER");
293  else
294  dbname = get_user_name_or_exit(progname);
295  }
296 
297  if (analyze_in_stages)
298  {
299  int stage;
300 
301  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
302  {
303  vacuum_one_database(dbname, &vacopts,
304  stage,
305  &tables,
306  host, port, username, prompt_password,
307  concurrentCons,
308  progname, echo, quiet);
309  }
310  }
311  else
312  vacuum_one_database(dbname, &vacopts,
314  &tables,
315  host, port, username, prompt_password,
316  concurrentCons,
317  progname, echo, quiet);
318  }
319 
320  exit(0);
321 }
322 
323 /*
324  * vacuum_one_database
325  *
326  * Process tables in the given database. If the 'tables' list is empty,
327  * process all tables in the database.
328  *
329  * Note that this function is only concerned with running exactly one stage
330  * when in analyze-in-stages mode; caller must iterate on us if necessary.
331  *
332  * If concurrentCons is > 1, multiple connections are used to vacuum tables
333  * in parallel. In this case and if the table list is empty, we first obtain
334  * a list of tables from the database.
335  */
336 static void
338  int stage,
339  SimpleStringList *tables,
340  const char *host, const char *port,
341  const char *username, enum trivalue prompt_password,
342  int concurrentCons,
343  const char *progname, bool echo, bool quiet)
344 {
345  PQExpBufferData sql;
346  PGconn *conn;
347  SimpleStringListCell *cell;
348  ParallelSlot *slots;
349  SimpleStringList dbtables = {NULL, NULL};
350  int i;
351  bool failed = false;
352  bool parallel = concurrentCons > 1;
353  const char *stage_commands[] = {
354  "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
355  "SET default_statistics_target=10; RESET vacuum_cost_delay;",
356  "RESET default_statistics_target;"
357  };
358  const char *stage_messages[] = {
359  gettext_noop("Generating minimal optimizer statistics (1 target)"),
360  gettext_noop("Generating medium optimizer statistics (10 targets)"),
361  gettext_noop("Generating default (full) optimizer statistics")
362  };
363 
364  Assert(stage == ANALYZE_NO_STAGE ||
365  (stage >= 0 && stage < ANALYZE_NUM_STAGES));
366 
367  conn = connectDatabase(dbname, host, port, username, prompt_password,
368  progname, echo, false, true);
369 
370  if (!quiet)
371  {
372  if (stage != ANALYZE_NO_STAGE)
373  printf(_("%s: processing database \"%s\": %s\n"),
374  progname, PQdb(conn), stage_messages[stage]);
375  else
376  printf(_("%s: vacuuming database \"%s\"\n"),
377  progname, PQdb(conn));
378  fflush(stdout);
379  }
380 
381  initPQExpBuffer(&sql);
382 
383  /*
384  * If a table list is not provided and we're using multiple connections,
385  * prepare the list of tables by querying the catalogs.
386  */
387  if (parallel && (!tables || !tables->head))
388  {
390  PGresult *res;
391  int ntups;
392 
393  initPQExpBuffer(&buf);
394 
395  res = executeQuery(conn,
396  "SELECT c.relname, ns.nspname"
397  " FROM pg_class c, pg_namespace ns\n"
398  " WHERE relkind IN ("
399  CppAsString2(RELKIND_RELATION) ", "
400  CppAsString2(RELKIND_MATVIEW) ")"
401  " AND c.relnamespace = ns.oid\n"
402  " ORDER BY c.relpages DESC;",
403  progname, echo);
404 
405  ntups = PQntuples(res);
406  for (i = 0; i < ntups; i++)
407  {
410  PQgetvalue(res, i, 1),
411  PQgetvalue(res, i, 0)));
412 
413  simple_string_list_append(&dbtables, buf.data);
414  resetPQExpBuffer(&buf);
415  }
416 
417  termPQExpBuffer(&buf);
418  tables = &dbtables;
419 
420  /*
421  * If there are more connections than vacuumable relations, we don't
422  * need to use them all.
423  */
424  if (concurrentCons > ntups)
425  concurrentCons = ntups;
426  if (concurrentCons <= 1)
427  parallel = false;
428  PQclear(res);
429  }
430 
431  /*
432  * Setup the database connections. We reuse the connection we already have
433  * for the first slot. If not in parallel mode, the first slot in the
434  * array contains the connection.
435  */
436  if (concurrentCons <= 0)
437  concurrentCons = 1;
438  slots = (ParallelSlot *) pg_malloc(sizeof(ParallelSlot) * concurrentCons);
439  init_slot(slots, conn);
440  if (parallel)
441  {
442  for (i = 1; i < concurrentCons; i++)
443  {
444  conn = connectDatabase(dbname, host, port, username, prompt_password,
445  progname, echo, false, true);
446  init_slot(slots + i, conn);
447  }
448  }
449 
450  /*
451  * Prepare all the connections to run the appropriate analyze stage, if
452  * caller requested that mode.
453  */
454  if (stage != ANALYZE_NO_STAGE)
455  {
456  int j;
457 
458  /* We already emitted the message above */
459 
460  for (j = 0; j < concurrentCons; j++)
461  executeCommand((slots + j)->connection,
462  stage_commands[stage], progname, echo);
463  }
464 
465  cell = tables ? tables->head : NULL;
466  do
467  {
468  const char *tabname = cell ? cell->val : NULL;
469  ParallelSlot *free_slot;
470 
471  if (CancelRequested)
472  {
473  failed = true;
474  goto finish;
475  }
476 
477  /*
478  * Get the connection slot to use. If in parallel mode, here we wait
479  * for one connection to become available if none already is. In
480  * non-parallel mode we simply use the only slot we have, which we
481  * know to be free.
482  */
483  if (parallel)
484  {
485  /*
486  * Get a free slot, waiting until one becomes free if none
487  * currently is.
488  */
489  free_slot = GetIdleSlot(slots, concurrentCons, progname);
490  if (!free_slot)
491  {
492  failed = true;
493  goto finish;
494  }
495 
496  free_slot->isFree = false;
497  }
498  else
499  free_slot = slots;
500 
501  /*
502  * Prepare the vacuum command. Note that in some cases this requires
503  * query execution, so be sure to use the free connection.
504  */
505  prepare_vacuum_command(&sql, free_slot->connection, vacopts, tabname,
506  tables == &dbtables, progname, echo);
507 
508  /*
509  * Execute the vacuum. If not in parallel mode, this terminates the
510  * program in case of an error. (The parallel case handles query
511  * errors in ProcessQueryResult through GetIdleSlot.)
512  */
513  run_vacuum_command(free_slot->connection, sql.data,
514  echo, tabname, progname, parallel);
515 
516  if (cell)
517  cell = cell->next;
518  } while (cell != NULL);
519 
520  if (parallel)
521  {
522  int j;
523 
524  /* wait for all connections to finish */
525  for (j = 0; j < concurrentCons; j++)
526  {
527  if (!GetQueryResult((slots + j)->connection, progname))
528  goto finish;
529  }
530  }
531 
532 finish:
533  for (i = 0; i < concurrentCons; i++)
534  DisconnectDatabase(slots + i);
535  pfree(slots);
536 
537  termPQExpBuffer(&sql);
538 
539  if (failed)
540  exit(1);
541 }
542 
543 /*
544  * Vacuum/analyze all connectable databases.
545  *
546  * In analyze-in-stages mode, we process all databases in one stage before
547  * moving on to the next stage. That ensure minimal stats are available
548  * quickly everywhere before generating more detailed ones.
549  */
550 static void
552  bool analyze_in_stages,
553  const char *maintenance_db, const char *host,
554  const char *port, const char *username,
555  enum trivalue prompt_password,
556  int concurrentCons,
557  const char *progname, bool echo, bool quiet)
558 {
559  PGconn *conn;
560  PGresult *result;
562  int stage;
563  int i;
564 
565  conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
566  prompt_password, progname, echo);
567  result = executeQuery(conn,
568  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
569  progname, echo);
570  PQfinish(conn);
571 
572  initPQExpBuffer(&connstr);
573  if (analyze_in_stages)
574  {
575  /*
576  * When analyzing all databases in stages, we analyze them all in the
577  * fastest stage first, so that initial statistics become available
578  * for all of them as soon as possible.
579  *
580  * This means we establish several times as many connections, but
581  * that's a secondary consideration.
582  */
583  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
584  {
585  for (i = 0; i < PQntuples(result); i++)
586  {
587  resetPQExpBuffer(&connstr);
588  appendPQExpBuffer(&connstr, "dbname=");
589  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
590 
591  vacuum_one_database(connstr.data, vacopts,
592  stage,
593  NULL,
594  host, port, username, prompt_password,
595  concurrentCons,
596  progname, echo, quiet);
597  }
598  }
599  }
600  else
601  {
602  for (i = 0; i < PQntuples(result); i++)
603  {
604  resetPQExpBuffer(&connstr);
605  appendPQExpBuffer(&connstr, "dbname=");
606  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
607 
608  vacuum_one_database(connstr.data, vacopts,
610  NULL,
611  host, port, username, prompt_password,
612  concurrentCons,
613  progname, echo, quiet);
614  }
615  }
616  termPQExpBuffer(&connstr);
617 
618  PQclear(result);
619 }
620 
621 /*
622  * Construct a vacuum/analyze command to run based on the given options, in the
623  * given string buffer, which may contain previous garbage.
624  *
625  * An optional table name can be passed; this must be already be properly
626  * quoted. The command is semicolon-terminated.
627  */
628 static void
630  vacuumingOptions *vacopts, const char *table,
631  bool table_pre_qualified,
632  const char *progname, bool echo)
633 {
634  resetPQExpBuffer(sql);
635 
636  if (vacopts->analyze_only)
637  {
638  appendPQExpBufferStr(sql, "ANALYZE");
639  if (vacopts->verbose)
640  appendPQExpBufferStr(sql, " VERBOSE");
641  }
642  else
643  {
644  appendPQExpBufferStr(sql, "VACUUM");
645  if (PQserverVersion(conn) >= 90000)
646  {
647  const char *paren = " (";
648  const char *comma = ", ";
649  const char *sep = paren;
650 
651  if (vacopts->full)
652  {
653  appendPQExpBuffer(sql, "%sFULL", sep);
654  sep = comma;
655  }
656  if (vacopts->freeze)
657  {
658  appendPQExpBuffer(sql, "%sFREEZE", sep);
659  sep = comma;
660  }
661  if (vacopts->verbose)
662  {
663  appendPQExpBuffer(sql, "%sVERBOSE", sep);
664  sep = comma;
665  }
666  if (vacopts->and_analyze)
667  {
668  appendPQExpBuffer(sql, "%sANALYZE", sep);
669  sep = comma;
670  }
671  if (sep != paren)
672  appendPQExpBufferChar(sql, ')');
673  }
674  else
675  {
676  if (vacopts->full)
677  appendPQExpBufferStr(sql, " FULL");
678  if (vacopts->freeze)
679  appendPQExpBufferStr(sql, " FREEZE");
680  if (vacopts->verbose)
681  appendPQExpBufferStr(sql, " VERBOSE");
682  if (vacopts->and_analyze)
683  appendPQExpBufferStr(sql, " ANALYZE");
684  }
685  }
686 
687  if (table)
688  {
689  appendPQExpBufferChar(sql, ' ');
690  if (table_pre_qualified)
691  appendPQExpBufferStr(sql, table);
692  else
693  appendQualifiedRelation(sql, table, conn, progname, echo);
694  }
695  appendPQExpBufferChar(sql, ';');
696 }
697 
698 /*
699  * Send a vacuum/analyze command to the server. In async mode, return after
700  * sending the command; else, wait for it to finish.
701  *
702  * Any errors during command execution are reported to stderr. If async is
703  * false, this function exits the program after reporting the error.
704  */
705 static void
706 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
707  const char *table, const char *progname, bool async)
708 {
709  bool status;
710 
711  if (async)
712  {
713  if (echo)
714  printf("%s\n", sql);
715 
716  status = PQsendQuery(conn, sql) == 1;
717  }
718  else
719  status = executeMaintenanceCommand(conn, sql, echo);
720 
721  if (!status)
722  {
723  if (table)
724  fprintf(stderr,
725  _("%s: vacuuming of table \"%s\" in database \"%s\" failed: %s"),
726  progname, table, PQdb(conn), PQerrorMessage(conn));
727  else
728  fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
729  progname, PQdb(conn), PQerrorMessage(conn));
730 
731  if (!async)
732  {
733  PQfinish(conn);
734  exit(1);
735  }
736  }
737 }
738 
739 /*
740  * GetIdleSlot
741  * Return a connection slot that is ready to execute a command.
742  *
743  * We return the first slot we find that is marked isFree, if one is;
744  * otherwise, we loop on select() until one socket becomes available. When
745  * this happens, we read the whole set and mark as free all sockets that become
746  * available.
747  *
748  * If an error occurs, NULL is returned.
749  */
750 static ParallelSlot *
751 GetIdleSlot(ParallelSlot slots[], int numslots,
752  const char *progname)
753 {
754  int i;
755  int firstFree = -1;
756 
757  /* Any connection already known free? */
758  for (i = 0; i < numslots; i++)
759  {
760  if (slots[i].isFree)
761  return slots + i;
762  }
763 
764  /*
765  * No free slot found, so wait until one of the connections has finished
766  * its task and return the available slot.
767  */
768  while (firstFree < 0)
769  {
770  fd_set slotset;
771  int maxFd = 0;
772  bool aborting;
773 
774  /* We must reconstruct the fd_set for each call to select_loop */
775  FD_ZERO(&slotset);
776 
777  for (i = 0; i < numslots; i++)
778  {
779  int sock = PQsocket(slots[i].connection);
780 
781  /*
782  * We don't really expect any connections to lose their sockets
783  * after startup, but just in case, cope by ignoring them.
784  */
785  if (sock < 0)
786  continue;
787 
788  FD_SET(sock, &slotset);
789  if (sock > maxFd)
790  maxFd = sock;
791  }
792 
793  SetCancelConn(slots->connection);
794  i = select_loop(maxFd, &slotset, &aborting);
795  ResetCancelConn();
796 
797  if (aborting)
798  {
799  /*
800  * We set the cancel-receiving connection to the one in the zeroth
801  * slot above, so fetch the error from there.
802  */
803  GetQueryResult(slots->connection, progname);
804  return NULL;
805  }
806  Assert(i != 0);
807 
808  for (i = 0; i < numslots; i++)
809  {
810  int sock = PQsocket(slots[i].connection);
811 
812  if (sock >= 0 && FD_ISSET(sock, &slotset))
813  {
814  /* select() says input is available, so consume it */
815  PQconsumeInput(slots[i].connection);
816  }
817 
818  /* Collect result(s) as long as any are available */
819  while (!PQisBusy(slots[i].connection))
820  {
821  PGresult *result = PQgetResult(slots[i].connection);
822 
823  if (result != NULL)
824  {
825  /* Check and discard the command result */
826  if (!ProcessQueryResult(slots[i].connection, result,
827  progname))
828  return NULL;
829  }
830  else
831  {
832  /* This connection has become idle */
833  slots[i].isFree = true;
834  if (firstFree < 0)
835  firstFree = i;
836  break;
837  }
838  }
839  }
840  }
841 
842  return slots + firstFree;
843 }
844 
845 /*
846  * ProcessQueryResult
847  *
848  * Process (and delete) a query result. Returns true if there's no error,
849  * false otherwise -- but errors about trying to vacuum a missing relation
850  * are reported and subsequently ignored.
851  */
852 static bool
854 {
855  /*
856  * If it's an error, report it. Errors about a missing table are harmless
857  * so we continue processing; but die for other errors.
858  */
859  if (PQresultStatus(result) != PGRES_COMMAND_OK)
860  {
861  char *sqlState = PQresultErrorField(result, PG_DIAG_SQLSTATE);
862 
863  fprintf(stderr, _("%s: vacuuming of database \"%s\" failed: %s"),
864  progname, PQdb(conn), PQerrorMessage(conn));
865 
866  if (sqlState && strcmp(sqlState, ERRCODE_UNDEFINED_TABLE) != 0)
867  {
868  PQclear(result);
869  return false;
870  }
871  }
872 
873  PQclear(result);
874  return true;
875 }
876 
877 /*
878  * GetQueryResult
879  *
880  * Pump the conn till it's dry of results; return false if any are errors.
881  * Note that this will block if the conn is busy.
882  */
883 static bool
885 {
886  bool ok = true;
887  PGresult *result;
888 
889  SetCancelConn(conn);
890  while ((result = PQgetResult(conn)) != NULL)
891  {
892  if (!ProcessQueryResult(conn, result, progname))
893  ok = false;
894  }
895  ResetCancelConn();
896  return ok;
897 }
898 
899 /*
900  * DisconnectDatabase
901  * Disconnect the connection associated with the given slot
902  */
903 static void
905 {
906  char errbuf[256];
907 
908  if (!slot->connection)
909  return;
910 
912  {
913  PGcancel *cancel;
914 
915  if ((cancel = PQgetCancel(slot->connection)))
916  {
917  (void) PQcancel(cancel, errbuf, sizeof(errbuf));
918  PQfreeCancel(cancel);
919  }
920  }
921 
922  PQfinish(slot->connection);
923  slot->connection = NULL;
924 }
925 
926 /*
927  * Loop on select() until a descriptor from the given set becomes readable.
928  *
929  * If we get a cancel request while we're waiting, we forego all further
930  * processing and set the *aborting flag to true. The return value must be
931  * ignored in this case. Otherwise, *aborting is set to false.
932  */
933 static int
934 select_loop(int maxFd, fd_set *workerset, bool *aborting)
935 {
936  int i;
937  fd_set saveSet = *workerset;
938 
939  if (CancelRequested)
940  {
941  *aborting = true;
942  return -1;
943  }
944  else
945  *aborting = false;
946 
947  for (;;)
948  {
949  /*
950  * On Windows, we need to check once in a while for cancel requests;
951  * on other platforms we rely on select() returning when interrupted.
952  */
953  struct timeval *tvp;
954 #ifdef WIN32
955  struct timeval tv = {0, 1000000};
956 
957  tvp = &tv;
958 #else
959  tvp = NULL;
960 #endif
961 
962  *workerset = saveSet;
963  i = select(maxFd + 1, workerset, NULL, NULL, tvp);
964 
965 #ifdef WIN32
966  if (i == SOCKET_ERROR)
967  {
968  i = -1;
969 
970  if (WSAGetLastError() == WSAEINTR)
971  errno = EINTR;
972  }
973 #endif
974 
975  if (i < 0 && errno == EINTR)
976  continue; /* ignore this */
977  if (i < 0 || CancelRequested)
978  *aborting = true; /* but not this */
979  if (i == 0)
980  continue; /* timeout (Win32 only) */
981  break;
982  }
983 
984  return i;
985 }
986 
987 static void
989 {
990  slot->connection = conn;
991  /* Initially assume connection is idle */
992  slot->isFree = true;
993 }
994 
995 static void
996 help(const char *progname)
997 {
998  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
999  printf(_("Usage:\n"));
1000  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1001  printf(_("\nOptions:\n"));
1002  printf(_(" -a, --all vacuum all databases\n"));
1003  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1004  printf(_(" -e, --echo show the commands being sent to the server\n"));
1005  printf(_(" -f, --full do full vacuuming\n"));
1006  printf(_(" -F, --freeze freeze row transaction information\n"));
1007  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1008  printf(_(" -q, --quiet don't write any messages\n"));
1009  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1010  printf(_(" -v, --verbose write a lot of output\n"));
1011  printf(_(" -V, --version output version information, then exit\n"));
1012  printf(_(" -z, --analyze update optimizer statistics\n"));
1013  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1014  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1015  " stages for faster results; no vacuum\n"));
1016  printf(_(" -?, --help show this help, then exit\n"));
1017  printf(_("\nConnection options:\n"));
1018  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1019  printf(_(" -p, --port=PORT database server port\n"));
1020  printf(_(" -U, --username=USERNAME user name to connect as\n"));
1021  printf(_(" -w, --no-password never prompt for password\n"));
1022  printf(_(" -W, --password force password prompt\n"));
1023  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1024  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1025  printf(_("\nReport bugs to <pgsql-bugs@postgresql.org>.\n"));
1026 }
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:159
static int select_loop(int maxFd, fd_set *workerset, bool *aborting)
Definition: vacuumdb.c:934
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1768
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6116
bool isFree
Definition: vacuumdb.c:32
void appendQualifiedRelation(PQExpBuffer buf, const char *spec, PGconn *conn, const char *progname, bool echo)
Definition: common.c:305
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3118
void * pg_malloc(size_t size)
Definition: fe_memutils.c:47
const char * get_progname(const char *argv0)
Definition: path.c:453
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:128
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:385
void PQfreeCancel(PGcancel *cancel)
Definition: fe-connect.c:3782
static ParallelSlot * GetIdleSlot(ParallelSlot slots[], int numslots, const char *progname)
Definition: vacuumdb.c:751
struct vacuumingOptions vacuumingOptions
#define gettext_noop(x)
Definition: c.h:1036
void appendConnStrVal(PQExpBuffer buf, const char *str)
Definition: string_utils.c:551
void PQfinish(PGconn *conn)
Definition: fe-connect.c:3638
static void setup_cancel_handler(void)
Definition: parallel.c:622
static bool ProcessQueryResult(PGconn *conn, PGresult *result, const char *progname)
Definition: vacuumdb.c:853
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:87
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6106
const char * progname
Definition: pg_standby.c:37
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2724
#define PG_DIAG_SQLSTATE
Definition: postgres_ext.h:57
ExecStatusType PQresultStatus(const PGresult *res)
Definition: fe-exec.c:2647
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:337
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1183
#define required_argument
Definition: getopt_long.h:25
void pfree(void *pointer)
Definition: mcxt.c:1031
int optind
Definition: getopt.c:51
PGconn * conn
Definition: streamutil.c:55
int main(int argc, char *argv[])
Definition: vacuumdb.c:92
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:262
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1794
char * c
static char * buf
Definition: pg_test_fsync.c:67
bool executeMaintenanceCommand(PGconn *conn, const char *query, bool echo)
Definition: common.c:242
PGcancel * PQgetCancel(PGconn *conn)
Definition: fe-connect.c:3759
struct SimpleStringListCell * next
Definition: simple_list.h:34
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
#define select(n, r, w, e, timeout)
Definition: win32_port.h:447
bool and_analyze
Definition: vacuumdb.c:40
struct ParallelSlot ParallelSlot
PGconn * connection
Definition: vacuumdb.c:31
void SetCancelConn(void)
Definition: common.c:446
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:37
static void DisconnectDatabase(ParallelSlot *slot)
Definition: vacuumdb.c:904
PGTransactionStatusType PQtransactionStatus(const PGconn *conn)
Definition: fe-connect.c:6071
trivalue
Definition: vacuumlo.c:32
static void help(const char *progname)
Definition: vacuumdb.c:996
#define CppAsString2(x)
Definition: c.h:224
#define no_argument
Definition: getopt_long.h:24
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1054
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:551
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table, const char *progname, bool async)
Definition: vacuumdb.c:706
int PQconsumeInput(PGconn *conn)
Definition: fe-exec.c:1682
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:396
static char * username
Definition: initdb.c:132
static void init_slot(ParallelSlot *slot, PGconn *conn)
Definition: vacuumdb.c:988
void PQclear(PGresult *res)
Definition: fe-exec.c:671
static void prepare_vacuum_command(PQExpBuffer sql, PGconn *conn, vacuumingOptions *vacopts, const char *table, bool table_pre_qualified, const char *progname, bool echo)
Definition: vacuumdb.c:629
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:5983
char * PQresultErrorField(const PGresult *res, int fieldcode)
Definition: fe-exec.c:2709
#define Assert(condition)
Definition: c.h:699
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:1522
static bool GetQueryResult(PGconn *conn, const char *progname)
Definition: vacuumdb.c:884
int PQisBusy(PGconn *conn)
Definition: fe-exec.c:1732
#define ERRCODE_UNDEFINED_TABLE
Definition: vacuumdb.c:26
SimpleStringListCell * head
Definition: simple_list.h:42
char * dbname
Definition: streamutil.c:51
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:88
int PQcancel(PGcancel *cancel, char *errbuf, int errbufsize)
Definition: fe-connect.c:3914
bool CancelRequested
Definition: common.c:26
bool analyze_only
Definition: vacuumdb.c:38
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:550
char * optarg
Definition: getopt.c:53
int i
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
#define EINTR
Definition: win32_port.h:334
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:145
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:225
void ResetCancelConn(void)
Definition: common.c:476
#define _(x)
Definition: elog.c:84
int PQsocket(const PGconn *conn)
Definition: fe-connect.c:6134
const char * fmtQualifiedId(int remoteVersion, const char *schema, const char *id)
Definition: string_utils.c:150
PGresult * PQgetResult(PGconn *conn)
Definition: fe-exec.c:1753
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:89
static char * connstr
Definition: pg_dumpall.c:59
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74