PostgreSQL Source Code  git master
vacuumdb.c File Reference
#include "postgres_fe.h"
#include <limits.h>
#include "catalog/pg_class_d.h"
#include "common.h"
#include "common/connect.h"
#include "common/logging.h"
#include "fe_utils/cancel.h"
#include "fe_utils/option_utils.h"
#include "fe_utils/parallel_slot.h"
#include "fe_utils/query_utils.h"
#include "fe_utils/simple_list.h"
#include "fe_utils/string_utils.h"
Include dependency graph for vacuumdb.c:

Go to the source code of this file.

Data Structures

struct  vacuumingOptions
 

Macros

#define ANALYZE_NO_STAGE   -1
 
#define ANALYZE_NUM_STAGES   3
 

Typedefs

typedef struct vacuumingOptions vacuumingOptions
 

Functions

static void vacuum_one_database (ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet)
 
static void vacuum_all_databases (ConnParams *cparams, vacuumingOptions *vacopts, bool analyze_in_stages, int concurrentCons, const char *progname, bool echo, bool quiet)
 
static void prepare_vacuum_command (PQExpBuffer sql, int serverVersion, vacuumingOptions *vacopts, const char *table)
 
static void run_vacuum_command (PGconn *conn, const char *sql, bool echo, const char *table)
 
static void help (const char *progname)
 
int main (int argc, char *argv[])
 

Macro Definition Documentation

◆ ANALYZE_NO_STAGE

#define ANALYZE_NO_STAGE   -1

Definition at line 72 of file vacuumdb.c.

Referenced by main(), vacuum_all_databases(), and vacuum_one_database().

◆ ANALYZE_NUM_STAGES

#define ANALYZE_NUM_STAGES   3

Definition at line 73 of file vacuumdb.c.

Referenced by main(), vacuum_all_databases(), and vacuum_one_database().

Typedef Documentation

◆ vacuumingOptions

Function Documentation

◆ help()

static void help ( const char *  progname)
static

Definition at line 1011 of file vacuumdb.c.

References _, and printf.

Referenced by main().

1012 {
1013  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
1014  printf(_("Usage:\n"));
1015  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
1016  printf(_("\nOptions:\n"));
1017  printf(_(" -a, --all vacuum all databases\n"));
1018  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
1019  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
1020  printf(_(" -e, --echo show the commands being sent to the server\n"));
1021  printf(_(" -f, --full do full vacuuming\n"));
1022  printf(_(" -F, --freeze freeze row transaction information\n"));
1023  printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1024  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1025  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1026  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1027  printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1028  printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1029  printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1030  printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1031  printf(_(" -q, --quiet don't write any messages\n"));
1032  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1033  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1034  printf(_(" -v, --verbose write a lot of output\n"));
1035  printf(_(" -V, --version output version information, then exit\n"));
1036  printf(_(" -z, --analyze update optimizer statistics\n"));
1037  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1038  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1039  " stages for faster results; no vacuum\n"));
1040  printf(_(" -?, --help show this help, then exit\n"));
1041  printf(_("\nConnection options:\n"));
1042  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1043  printf(_(" -p, --port=PORT database server port\n"));
1044  printf(_(" -U, --username=USERNAME user name to connect as\n"));
1045  printf(_(" -w, --no-password never prompt for password\n"));
1046  printf(_(" -W, --password force password prompt\n"));
1047  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1048  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1049  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1050  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1051 }
const char * progname
Definition: main.c:46
#define printf(...)
Definition: port.h:222
#define _(x)
Definition: elog.c:89

◆ main()

int main ( int  argc,
char *  argv[] 
)

Definition at line 77 of file vacuumdb.c.

References _, ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, vacuumingOptions::analyze_only, vacuumingOptions::and_analyze, dbname, _connParams::dbname, vacuumingOptions::disable_page_skipping, vacuumingOptions::do_truncate, vacuumingOptions::force_index_cleanup, fprintf, vacuumingOptions::freeze, vacuumingOptions::full, get_progname(), get_user_name_or_exit(), getopt_long(), handle_help_version_opts(), SimpleStringList::head, help(), vacuumingOptions::min_mxid_age, vacuumingOptions::min_xid_age, no_argument, vacuumingOptions::no_index_cleanup, optarg, optind, option_parse_int(), _connParams::override_dbname, vacuumingOptions::parallel_workers, pg_log_error, pg_logging_init(), pg_strdup(), PG_TEXTDOMAIN, _connParams::pghost, _connParams::pgport, _connParams::pguser, port, vacuumingOptions::process_toast, progname, _connParams::prompt_password, required_argument, set_pglocale_pgservice(), setup_cancel_handler(), simple_string_list_append(), vacuumingOptions::skip_locked, TRI_DEFAULT, TRI_NO, TRI_YES, username, vacuum_all_databases(), vacuum_one_database(), and vacuumingOptions::verbose.

78 {
79  static struct option long_options[] = {
80  {"host", required_argument, NULL, 'h'},
81  {"port", required_argument, NULL, 'p'},
82  {"username", required_argument, NULL, 'U'},
83  {"no-password", no_argument, NULL, 'w'},
84  {"password", no_argument, NULL, 'W'},
85  {"echo", no_argument, NULL, 'e'},
86  {"quiet", no_argument, NULL, 'q'},
87  {"dbname", required_argument, NULL, 'd'},
88  {"analyze", no_argument, NULL, 'z'},
89  {"analyze-only", no_argument, NULL, 'Z'},
90  {"freeze", no_argument, NULL, 'F'},
91  {"all", no_argument, NULL, 'a'},
92  {"table", required_argument, NULL, 't'},
93  {"full", no_argument, NULL, 'f'},
94  {"verbose", no_argument, NULL, 'v'},
95  {"jobs", required_argument, NULL, 'j'},
96  {"parallel", required_argument, NULL, 'P'},
97  {"maintenance-db", required_argument, NULL, 2},
98  {"analyze-in-stages", no_argument, NULL, 3},
99  {"disable-page-skipping", no_argument, NULL, 4},
100  {"skip-locked", no_argument, NULL, 5},
101  {"min-xid-age", required_argument, NULL, 6},
102  {"min-mxid-age", required_argument, NULL, 7},
103  {"no-index-cleanup", no_argument, NULL, 8},
104  {"force-index-cleanup", no_argument, NULL, 9},
105  {"no-truncate", no_argument, NULL, 10},
106  {"no-process-toast", no_argument, NULL, 11},
107  {NULL, 0, NULL, 0}
108  };
109 
110  const char *progname;
111  int optindex;
112  int c;
113  const char *dbname = NULL;
114  const char *maintenance_db = NULL;
115  char *host = NULL;
116  char *port = NULL;
117  char *username = NULL;
118  enum trivalue prompt_password = TRI_DEFAULT;
119  ConnParams cparams;
120  bool echo = false;
121  bool quiet = false;
122  vacuumingOptions vacopts;
123  bool analyze_in_stages = false;
124  bool alldb = false;
125  SimpleStringList tables = {NULL, NULL};
126  int concurrentCons = 1;
127  int tbl_count = 0;
128 
129  /* initialize options */
130  memset(&vacopts, 0, sizeof(vacopts));
131  vacopts.parallel_workers = -1;
132  vacopts.no_index_cleanup = false;
133  vacopts.force_index_cleanup = false;
134  vacopts.do_truncate = true;
135  vacopts.process_toast = true;
136 
137  pg_logging_init(argv[0]);
138  progname = get_progname(argv[0]);
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:P:", 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  if (!option_parse_int(optarg, "-j/--jobs", 1, INT_MAX,
197  &concurrentCons))
198  exit(1);
199  break;
200  case 'P':
201  if (!option_parse_int(optarg, "-P/--parallel", 0, INT_MAX,
202  &vacopts.parallel_workers))
203  exit(1);
204  break;
205  case 2:
206  maintenance_db = pg_strdup(optarg);
207  break;
208  case 3:
209  analyze_in_stages = vacopts.analyze_only = true;
210  break;
211  case 4:
212  vacopts.disable_page_skipping = true;
213  break;
214  case 5:
215  vacopts.skip_locked = true;
216  break;
217  case 6:
218  if (!option_parse_int(optarg, "--min-xid-age", 1, INT_MAX,
219  &vacopts.min_xid_age))
220  exit(1);
221  break;
222  case 7:
223  if (!option_parse_int(optarg, "--min-mxid-age", 1, INT_MAX,
224  &vacopts.min_mxid_age))
225  exit(1);
226  break;
227  case 8:
228  vacopts.no_index_cleanup = true;
229  break;
230  case 9:
231  vacopts.force_index_cleanup = true;
232  break;
233  case 10:
234  vacopts.do_truncate = false;
235  break;
236  case 11:
237  vacopts.process_toast = false;
238  break;
239  default:
240  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
241  exit(1);
242  }
243  }
244 
245  /*
246  * Non-option argument specifies database name as long as it wasn't
247  * already specified with -d / --dbname
248  */
249  if (optind < argc && dbname == NULL)
250  {
251  dbname = argv[optind];
252  optind++;
253  }
254 
255  if (optind < argc)
256  {
257  pg_log_error("too many command-line arguments (first is \"%s\")",
258  argv[optind]);
259  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
260  exit(1);
261  }
262 
263  if (vacopts.analyze_only)
264  {
265  if (vacopts.full)
266  {
267  pg_log_error("cannot use the \"%s\" option when performing only analyze",
268  "full");
269  exit(1);
270  }
271  if (vacopts.freeze)
272  {
273  pg_log_error("cannot use the \"%s\" option when performing only analyze",
274  "freeze");
275  exit(1);
276  }
277  if (vacopts.disable_page_skipping)
278  {
279  pg_log_error("cannot use the \"%s\" option when performing only analyze",
280  "disable-page-skipping");
281  exit(1);
282  }
283  if (vacopts.no_index_cleanup)
284  {
285  pg_log_error("cannot use the \"%s\" option when performing only analyze",
286  "no-index-cleanup");
287  exit(1);
288  }
289  if (vacopts.force_index_cleanup)
290  {
291  pg_log_error("cannot use the \"%s\" option when performing only analyze",
292  "force-index-cleanup");
293  exit(1);
294  }
295  if (!vacopts.do_truncate)
296  {
297  pg_log_error("cannot use the \"%s\" option when performing only analyze",
298  "no-truncate");
299  exit(1);
300  }
301  if (!vacopts.process_toast)
302  {
303  pg_log_error("cannot use the \"%s\" option when performing only analyze",
304  "no-process-toast");
305  exit(1);
306  }
307  /* allow 'and_analyze' with 'analyze_only' */
308  }
309 
310  /* Prohibit full and analyze_only options with parallel option */
311  if (vacopts.parallel_workers >= 0)
312  {
313  if (vacopts.analyze_only)
314  {
315  pg_log_error("cannot use the \"%s\" option when performing only analyze",
316  "parallel");
317  exit(1);
318  }
319  if (vacopts.full)
320  {
321  pg_log_error("cannot use the \"%s\" option when performing full vacuum",
322  "parallel");
323  exit(1);
324  }
325  }
326 
327  /* Prohibit --no-index-cleanup and --force-index-cleanup together */
328  if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
329  {
330  pg_log_error("cannot use the \"%s\" option with the \"%s\" option",
331  "no-index-cleanup", "force-index-cleanup");
332  exit(1);
333  }
334 
335  /* fill cparams except for dbname, which is set below */
336  cparams.pghost = host;
337  cparams.pgport = port;
338  cparams.pguser = username;
339  cparams.prompt_password = prompt_password;
340  cparams.override_dbname = NULL;
341 
342  setup_cancel_handler(NULL);
343 
344  /* Avoid opening extra connections. */
345  if (tbl_count && (concurrentCons > tbl_count))
346  concurrentCons = tbl_count;
347 
348  if (alldb)
349  {
350  if (dbname)
351  {
352  pg_log_error("cannot vacuum all databases and a specific one at the same time");
353  exit(1);
354  }
355  if (tables.head != NULL)
356  {
357  pg_log_error("cannot vacuum specific table(s) in all databases");
358  exit(1);
359  }
360 
361  cparams.dbname = maintenance_db;
362 
363  vacuum_all_databases(&cparams, &vacopts,
364  analyze_in_stages,
365  concurrentCons,
366  progname, echo, quiet);
367  }
368  else
369  {
370  if (dbname == NULL)
371  {
372  if (getenv("PGDATABASE"))
373  dbname = getenv("PGDATABASE");
374  else if (getenv("PGUSER"))
375  dbname = getenv("PGUSER");
376  else
377  dbname = get_user_name_or_exit(progname);
378  }
379 
380  cparams.dbname = dbname;
381 
382  if (analyze_in_stages)
383  {
384  int stage;
385 
386  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
387  {
388  vacuum_one_database(&cparams, &vacopts,
389  stage,
390  &tables,
391  concurrentCons,
392  progname, echo, quiet);
393  }
394  }
395  else
396  vacuum_one_database(&cparams, &vacopts,
398  &tables,
399  concurrentCons,
400  progname, echo, quiet);
401  }
402 
403  exit(0);
404 }
const char * progname
Definition: main.c:46
char * pgport
Definition: pg_backup.h:66
static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:420
bool skip_locked
Definition: vacuumdb.c:38
bool option_parse_int(const char *optarg, const char *optname, int min_range, int max_range, int *result)
Definition: option_utils.c:50
const char * get_progname(const char *argv0)
Definition: path.c:453
#define pg_log_error(...)
Definition: logging.h:80
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
void pg_logging_init(const char *argv0)
Definition: logging.c:81
static void setup_cancel_handler(void)
Definition: parallel.c:613
int parallel_workers
Definition: vacuumdb.c:41
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:72
#define fprintf
Definition: port.h:220
bool process_toast
Definition: vacuumdb.c:46
char * dbname
Definition: pg_backup.h:65
#define required_argument
Definition: getopt_long.h:25
bool do_truncate
Definition: vacuumdb.c:45
int optind
Definition: getopt.c:50
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: option_utils.c:24
char * c
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
bool and_analyze
Definition: vacuumdb.c:34
const char * username
Definition: pgbench.c:282
char * override_dbname
Definition: pg_backup.h:72
static int port
Definition: pg_regress.c:92
char * pghost
Definition: pg_backup.h:67
enum trivalue prompt_password
Definition: connect_utils.h:32
trivalue
Definition: vacuumlo.c:34
static void help(const char *progname)
Definition: vacuumdb.c:1011
#define no_argument
Definition: getopt_long.h:24
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1215
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
SimpleStringListCell * head
Definition: simple_list.h:42
char * dbname
Definition: streamutil.c:51
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:73
bool disable_page_skipping
Definition: vacuumdb.c:37
bool analyze_only
Definition: vacuumdb.c:32
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:433
static void vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, bool analyze_in_stages, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:781
char * optarg
Definition: getopt.c:52
bool force_index_cleanup
Definition: vacuumdb.c:44
#define _(x)
Definition: elog.c:89
bool no_index_cleanup
Definition: vacuumdb.c:43
const char * pguser
Definition: connect_utils.h:31
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74

◆ prepare_vacuum_command()

static void prepare_vacuum_command ( PQExpBuffer  sql,
int  serverVersion,
vacuumingOptions vacopts,
const char *  table 
)
static

Definition at line 847 of file vacuumdb.c.

References vacuumingOptions::analyze_only, vacuumingOptions::and_analyze, appendPQExpBuffer(), appendPQExpBufferChar(), appendPQExpBufferStr(), Assert, vacuumingOptions::disable_page_skipping, vacuumingOptions::do_truncate, vacuumingOptions::force_index_cleanup, vacuumingOptions::freeze, vacuumingOptions::full, vacuumingOptions::no_index_cleanup, vacuumingOptions::parallel_workers, vacuumingOptions::process_toast, resetPQExpBuffer(), vacuumingOptions::skip_locked, and vacuumingOptions::verbose.

Referenced by vacuum_one_database().

849 {
850  const char *paren = " (";
851  const char *comma = ", ";
852  const char *sep = paren;
853 
854  resetPQExpBuffer(sql);
855 
856  if (vacopts->analyze_only)
857  {
858  appendPQExpBufferStr(sql, "ANALYZE");
859 
860  /* parenthesized grammar of ANALYZE is supported since v11 */
861  if (serverVersion >= 110000)
862  {
863  if (vacopts->skip_locked)
864  {
865  /* SKIP_LOCKED is supported since v12 */
866  Assert(serverVersion >= 120000);
867  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
868  sep = comma;
869  }
870  if (vacopts->verbose)
871  {
872  appendPQExpBuffer(sql, "%sVERBOSE", sep);
873  sep = comma;
874  }
875  if (sep != paren)
876  appendPQExpBufferChar(sql, ')');
877  }
878  else
879  {
880  if (vacopts->verbose)
881  appendPQExpBufferStr(sql, " VERBOSE");
882  }
883  }
884  else
885  {
886  appendPQExpBufferStr(sql, "VACUUM");
887 
888  /* parenthesized grammar of VACUUM is supported since v9.0 */
889  if (serverVersion >= 90000)
890  {
891  if (vacopts->disable_page_skipping)
892  {
893  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
894  Assert(serverVersion >= 90600);
895  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
896  sep = comma;
897  }
898  if (vacopts->no_index_cleanup)
899  {
900  /* "INDEX_CLEANUP FALSE" has been supported since v12 */
901  Assert(serverVersion >= 120000);
902  Assert(!vacopts->force_index_cleanup);
903  appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
904  sep = comma;
905  }
906  if (vacopts->force_index_cleanup)
907  {
908  /* "INDEX_CLEANUP TRUE" has been supported since v12 */
909  Assert(serverVersion >= 120000);
910  Assert(!vacopts->no_index_cleanup);
911  appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
912  sep = comma;
913  }
914  if (!vacopts->do_truncate)
915  {
916  /* TRUNCATE is supported since v12 */
917  Assert(serverVersion >= 120000);
918  appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
919  sep = comma;
920  }
921  if (!vacopts->process_toast)
922  {
923  /* PROCESS_TOAST is supported since v14 */
924  Assert(serverVersion >= 140000);
925  appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
926  sep = comma;
927  }
928  if (vacopts->skip_locked)
929  {
930  /* SKIP_LOCKED is supported since v12 */
931  Assert(serverVersion >= 120000);
932  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
933  sep = comma;
934  }
935  if (vacopts->full)
936  {
937  appendPQExpBuffer(sql, "%sFULL", sep);
938  sep = comma;
939  }
940  if (vacopts->freeze)
941  {
942  appendPQExpBuffer(sql, "%sFREEZE", sep);
943  sep = comma;
944  }
945  if (vacopts->verbose)
946  {
947  appendPQExpBuffer(sql, "%sVERBOSE", sep);
948  sep = comma;
949  }
950  if (vacopts->and_analyze)
951  {
952  appendPQExpBuffer(sql, "%sANALYZE", sep);
953  sep = comma;
954  }
955  if (vacopts->parallel_workers >= 0)
956  {
957  /* PARALLEL is supported since v13 */
958  Assert(serverVersion >= 130000);
959  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
960  vacopts->parallel_workers);
961  sep = comma;
962  }
963  if (sep != paren)
964  appendPQExpBufferChar(sql, ')');
965  }
966  else
967  {
968  if (vacopts->full)
969  appendPQExpBufferStr(sql, " FULL");
970  if (vacopts->freeze)
971  appendPQExpBufferStr(sql, " FREEZE");
972  if (vacopts->verbose)
973  appendPQExpBufferStr(sql, " VERBOSE");
974  if (vacopts->and_analyze)
975  appendPQExpBufferStr(sql, " ANALYZE");
976  }
977  }
978 
979  appendPQExpBuffer(sql, " %s;", table);
980 }
bool skip_locked
Definition: vacuumdb.c:38
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
int parallel_workers
Definition: vacuumdb.c:41
bool process_toast
Definition: vacuumdb.c:46
bool do_truncate
Definition: vacuumdb.c:45
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
bool and_analyze
Definition: vacuumdb.c:34
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
#define Assert(condition)
Definition: c.h:804
bool disable_page_skipping
Definition: vacuumdb.c:37
bool analyze_only
Definition: vacuumdb.c:32
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
bool force_index_cleanup
Definition: vacuumdb.c:44
bool no_index_cleanup
Definition: vacuumdb.c:43

◆ run_vacuum_command()

static void run_vacuum_command ( PGconn conn,
const char *  sql,
bool  echo,
const char *  table 
)
static

Definition at line 989 of file vacuumdb.c.

References pg_log_error, PQdb(), PQerrorMessage(), PQsendQuery(), printf, and status().

Referenced by vacuum_one_database().

991 {
992  bool status;
993 
994  if (echo)
995  printf("%s\n", sql);
996 
997  status = PQsendQuery(conn, sql) == 1;
998 
999  if (!status)
1000  {
1001  if (table)
1002  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
1003  table, PQdb(conn), PQerrorMessage(conn));
1004  else
1005  pg_log_error("vacuuming of database \"%s\" failed: %s",
1006  PQdb(conn), PQerrorMessage(conn));
1007  }
1008 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6744
#define pg_log_error(...)
Definition: logging.h:80
#define printf(...)
Definition: port.h:222
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1326
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6590
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:227

◆ vacuum_all_databases()

static void vacuum_all_databases ( ConnParams cparams,
vacuumingOptions vacopts,
bool  analyze_in_stages,
int  concurrentCons,
const char *  progname,
bool  echo,
bool  quiet 
)
static

Definition at line 781 of file vacuumdb.c.

References ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, conn, connectMaintenanceDatabase(), executeQuery(), i, _connParams::override_dbname, PQclear(), PQfinish(), PQgetvalue(), PQntuples(), and vacuum_one_database().

Referenced by main().

786 {
787  PGconn *conn;
788  PGresult *result;
789  int stage;
790  int i;
791 
792  conn = connectMaintenanceDatabase(cparams, progname, echo);
793  result = executeQuery(conn,
794  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
795  echo);
796  PQfinish(conn);
797 
798  if (analyze_in_stages)
799  {
800  /*
801  * When analyzing all databases in stages, we analyze them all in the
802  * fastest stage first, so that initial statistics become available
803  * for all of them as soon as possible.
804  *
805  * This means we establish several times as many connections, but
806  * that's a secondary consideration.
807  */
808  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
809  {
810  for (i = 0; i < PQntuples(result); i++)
811  {
812  cparams->override_dbname = PQgetvalue(result, i, 0);
813 
814  vacuum_one_database(cparams, vacopts,
815  stage,
816  NULL,
817  concurrentCons,
818  progname, echo, quiet);
819  }
820  }
821  }
822  else
823  {
824  for (i = 0; i < PQntuples(result); i++)
825  {
826  cparams->override_dbname = PQgetvalue(result, i, 0);
827 
828  vacuum_one_database(cparams, vacopts,
830  NULL,
831  concurrentCons,
832  progname, echo, quiet);
833  }
834  }
835 
836  PQclear(result);
837 }
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1879
const char * progname
Definition: main.c:46
static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:420
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3642
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4231
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:72
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3248
PGconn * conn
Definition: streamutil.c:54
char * override_dbname
Definition: pg_backup.h:72
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
void PQclear(PGresult *res)
Definition: fe-exec.c:694
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:73
int i

◆ vacuum_one_database()

static void vacuum_one_database ( ConnParams cparams,
vacuumingOptions vacopts,
int  stage,
SimpleStringList tables,
int  concurrentCons,
const char *  progname,
bool  echo,
bool  quiet 
)
static

Definition at line 420 of file vacuumdb.c.

References _, ALWAYS_SECURE_SEARCH_PATH_SQL, ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, appendPQExpBuffer(), appendPQExpBufferStr(), appendStringLiteralConn(), Assert, buf, CancelRequested, conn, connectDatabase(), ParallelSlot::connection, CppAsString2, PQExpBufferData::data, vacuumingOptions::disable_page_skipping, vacuumingOptions::do_truncate, executeCommand(), executeQuery(), fmtQualifiedId(), vacuumingOptions::force_index_cleanup, gettext_noop, SimpleStringList::head, i, initPQExpBuffer(), vacuumingOptions::min_mxid_age, vacuumingOptions::min_xid_age, SimpleStringListCell::next, vacuumingOptions::no_index_cleanup, vacuumingOptions::parallel_workers, ParallelSlotsAdoptConn(), ParallelSlotSetHandler(), ParallelSlotsGetIdle(), ParallelSlotsSetup(), ParallelSlotsTerminate(), ParallelSlotsWaitCompletion(), pg_free(), pg_log_error, PQclear(), PQclientEncoding(), PQdb(), PQfinish(), PQgetisnull(), PQgetvalue(), PQntuples(), PQserverVersion(), prepare_vacuum_command(), printf, vacuumingOptions::process_toast, resetPQExpBuffer(), run_vacuum_command(), simple_string_list_append(), vacuumingOptions::skip_locked, splitTableColumnsSpec(), generate_unaccent_rules::stdout, TableCommandResultHandler(), termPQExpBuffer(), and SimpleStringListCell::val.

Referenced by main(), and vacuum_all_databases().

426 {
427  PQExpBufferData sql;
429  PQExpBufferData catalog_query;
430  PGresult *res;
431  PGconn *conn;
432  SimpleStringListCell *cell;
434  SimpleStringList dbtables = {NULL, NULL};
435  int i;
436  int ntups;
437  bool failed = false;
438  bool tables_listed = false;
439  bool has_where = false;
440  const char *initcmd;
441  const char *stage_commands[] = {
442  "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
443  "SET default_statistics_target=10; RESET vacuum_cost_delay;",
444  "RESET default_statistics_target;"
445  };
446  const char *stage_messages[] = {
447  gettext_noop("Generating minimal optimizer statistics (1 target)"),
448  gettext_noop("Generating medium optimizer statistics (10 targets)"),
449  gettext_noop("Generating default (full) optimizer statistics")
450  };
451 
452  Assert(stage == ANALYZE_NO_STAGE ||
453  (stage >= 0 && stage < ANALYZE_NUM_STAGES));
454 
455  conn = connectDatabase(cparams, progname, echo, false, true);
456 
457  if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
458  {
459  PQfinish(conn);
460  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
461  "disable-page-skipping", "9.6");
462  exit(1);
463  }
464 
465  if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
466  {
467  PQfinish(conn);
468  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
469  "no-index-cleanup", "12");
470  exit(1);
471  }
472 
473  if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
474  {
475  PQfinish(conn);
476  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
477  "force-index-cleanup", "12");
478  exit(1);
479  }
480 
481  if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
482  {
483  PQfinish(conn);
484  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
485  "no-truncate", "12");
486  exit(1);
487  }
488 
489  if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
490  {
491  PQfinish(conn);
492  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
493  "no-process-toast", "14");
494  exit(1);
495  }
496 
497  if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
498  {
499  PQfinish(conn);
500  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
501  "skip-locked", "12");
502  exit(1);
503  }
504 
505  if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
506  {
507  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
508  "--min-xid-age", "9.6");
509  exit(1);
510  }
511 
512  if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
513  {
514  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
515  "--min-mxid-age", "9.6");
516  exit(1);
517  }
518 
519  if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
520  {
521  pg_log_error("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
522  "--parallel", "13");
523  exit(1);
524  }
525 
526  if (!quiet)
527  {
528  if (stage != ANALYZE_NO_STAGE)
529  printf(_("%s: processing database \"%s\": %s\n"),
530  progname, PQdb(conn), _(stage_messages[stage]));
531  else
532  printf(_("%s: vacuuming database \"%s\"\n"),
533  progname, PQdb(conn));
534  fflush(stdout);
535  }
536 
537  /*
538  * Prepare the list of tables to process by querying the catalogs.
539  *
540  * Since we execute the constructed query with the default search_path
541  * (which could be unsafe), everything in this query MUST be fully
542  * qualified.
543  *
544  * First, build a WITH clause for the catalog query if any tables were
545  * specified, with a set of values made of relation names and their
546  * optional set of columns. This is used to match any provided column
547  * lists with the generated qualified identifiers and to filter for the
548  * tables provided via --table. If a listed table does not exist, the
549  * catalog query will fail.
550  */
551  initPQExpBuffer(&catalog_query);
552  for (cell = tables ? tables->head : NULL; cell; cell = cell->next)
553  {
554  char *just_table;
555  const char *just_columns;
556 
557  /*
558  * Split relation and column names given by the user, this is used to
559  * feed the CTE with values on which are performed pre-run validity
560  * checks as well. For now these happen only on the relation name.
561  */
563  &just_table, &just_columns);
564 
565  if (!tables_listed)
566  {
567  appendPQExpBufferStr(&catalog_query,
568  "WITH listed_tables (table_oid, column_list) "
569  "AS (\n VALUES (");
570  tables_listed = true;
571  }
572  else
573  appendPQExpBufferStr(&catalog_query, ",\n (");
574 
575  appendStringLiteralConn(&catalog_query, just_table, conn);
576  appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
577 
578  if (just_columns && just_columns[0] != '\0')
579  appendStringLiteralConn(&catalog_query, just_columns, conn);
580  else
581  appendPQExpBufferStr(&catalog_query, "NULL");
582 
583  appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
584 
585  pg_free(just_table);
586  }
587 
588  /* Finish formatting the CTE */
589  if (tables_listed)
590  appendPQExpBufferStr(&catalog_query, "\n)\n");
591 
592  appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
593 
594  if (tables_listed)
595  appendPQExpBufferStr(&catalog_query, ", listed_tables.column_list");
596 
597  appendPQExpBufferStr(&catalog_query,
598  " FROM pg_catalog.pg_class c\n"
599  " JOIN pg_catalog.pg_namespace ns"
600  " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
601  " LEFT JOIN pg_catalog.pg_class t"
602  " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
603 
604  /* Used to match the tables listed by the user */
605  if (tables_listed)
606  appendPQExpBufferStr(&catalog_query, " JOIN listed_tables"
607  " ON listed_tables.table_oid OPERATOR(pg_catalog.=) c.oid\n");
608 
609  /*
610  * If no tables were listed, filter for the relevant relation types. If
611  * tables were given via --table, don't bother filtering by relation type.
612  * Instead, let the server decide whether a given relation can be
613  * processed in which case the user will know about it.
614  */
615  if (!tables_listed)
616  {
617  appendPQExpBufferStr(&catalog_query, " WHERE c.relkind OPERATOR(pg_catalog.=) ANY (array["
618  CppAsString2(RELKIND_RELATION) ", "
619  CppAsString2(RELKIND_MATVIEW) "])\n");
620  has_where = true;
621  }
622 
623  /*
624  * For --min-xid-age and --min-mxid-age, the age of the relation is the
625  * greatest of the ages of the main relation and its associated TOAST
626  * table. The commands generated by vacuumdb will also process the TOAST
627  * table for the relation if necessary, so it does not need to be
628  * considered separately.
629  */
630  if (vacopts->min_xid_age != 0)
631  {
632  appendPQExpBuffer(&catalog_query,
633  " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
634  " pg_catalog.age(t.relfrozenxid)) "
635  " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
636  " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
637  " '0'::pg_catalog.xid\n",
638  has_where ? "AND" : "WHERE", vacopts->min_xid_age);
639  has_where = true;
640  }
641 
642  if (vacopts->min_mxid_age != 0)
643  {
644  appendPQExpBuffer(&catalog_query,
645  " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
646  " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
647  " '%d'::pg_catalog.int4\n"
648  " AND c.relminmxid OPERATOR(pg_catalog.!=)"
649  " '0'::pg_catalog.xid\n",
650  has_where ? "AND" : "WHERE", vacopts->min_mxid_age);
651  has_where = true;
652  }
653 
654  /*
655  * Execute the catalog query. We use the default search_path for this
656  * query for consistency with table lookups done elsewhere by the user.
657  */
658  appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
659  executeCommand(conn, "RESET search_path;", echo);
660  res = executeQuery(conn, catalog_query.data, echo);
661  termPQExpBuffer(&catalog_query);
663 
664  /*
665  * If no rows are returned, there are no matching tables, so we are done.
666  */
667  ntups = PQntuples(res);
668  if (ntups == 0)
669  {
670  PQclear(res);
671  PQfinish(conn);
672  return;
673  }
674 
675  /*
676  * Build qualified identifiers for each table, including the column list
677  * if given.
678  */
679  initPQExpBuffer(&buf);
680  for (i = 0; i < ntups; i++)
681  {
683  fmtQualifiedId(PQgetvalue(res, i, 1),
684  PQgetvalue(res, i, 0)));
685 
686  if (tables_listed && !PQgetisnull(res, i, 2))
687  appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
688 
689  simple_string_list_append(&dbtables, buf.data);
690  resetPQExpBuffer(&buf);
691  }
692  termPQExpBuffer(&buf);
693  PQclear(res);
694 
695  /*
696  * Ensure concurrentCons is sane. If there are more connections than
697  * vacuumable relations, we don't need to use them all.
698  */
699  if (concurrentCons > ntups)
700  concurrentCons = ntups;
701  if (concurrentCons <= 0)
702  concurrentCons = 1;
703 
704  /*
705  * All slots need to be prepared to run the appropriate analyze stage, if
706  * caller requested that mode. We have to prepare the initial connection
707  * ourselves before setting up the slots.
708  */
709  if (stage == ANALYZE_NO_STAGE)
710  initcmd = NULL;
711  else
712  {
713  initcmd = stage_commands[stage];
714  executeCommand(conn, initcmd, echo);
715  }
716 
717  /*
718  * Setup the database connections. We reuse the connection we already have
719  * for the first slot. If not in parallel mode, the first slot in the
720  * array contains the connection.
721  */
722  sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
723  ParallelSlotsAdoptConn(sa, conn);
724 
725  initPQExpBuffer(&sql);
726 
727  cell = dbtables.head;
728  do
729  {
730  const char *tabname = cell->val;
731  ParallelSlot *free_slot;
732 
733  if (CancelRequested)
734  {
735  failed = true;
736  goto finish;
737  }
738 
739  free_slot = ParallelSlotsGetIdle(sa, NULL);
740  if (!free_slot)
741  {
742  failed = true;
743  goto finish;
744  }
745 
747  vacopts, tabname);
748 
749  /*
750  * Execute the vacuum. All errors are handled in processQueryResult
751  * through ParallelSlotsGetIdle.
752  */
754  run_vacuum_command(free_slot->connection, sql.data,
755  echo, tabname);
756 
757  cell = cell->next;
758  } while (cell != NULL);
759 
761  failed = true;
762 
763 finish:
765  pg_free(sa);
766 
767  termPQExpBuffer(&sql);
768 
769  if (failed)
770  exit(1);
771 }
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1879
const char * progname
Definition: main.c:46
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3642
bool skip_locked
Definition: vacuumdb.c:38
static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion, vacuumingOptions *vacopts, const char *table)
Definition: vacuumdb.c:847
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
#define pg_log_error(...)
Definition: logging.h:80
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
void ParallelSlotsAdoptConn(ParallelSlotArray *sa, PGconn *conn)
#define gettext_noop(x)
Definition: c.h:1197
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4231
static void ParallelSlotSetHandler(ParallelSlot *slot, ParallelSlotResultHandler handler, void *context)
Definition: parallel_slot.h:47
#define printf(...)
Definition: port.h:222
int parallel_workers
Definition: vacuumdb.c:41
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:72
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:6734
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3248
int PQclientEncoding(const PGconn *conn)
Definition: fe-connect.c:6821
bool process_toast
Definition: vacuumdb.c:46
ParallelSlotArray * ParallelSlotsSetup(int numslots, ConnParams *cparams, const char *progname, bool echo, const char *initcmd)
ParallelSlot * ParallelSlotsGetIdle(ParallelSlotArray *sa, const char *dbname)
bool do_truncate
Definition: vacuumdb.c:45
PGconn * conn
Definition: streamutil.c:54
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1902
static char * buf
Definition: pg_test_fsync.c:68
struct SimpleStringListCell * next
Definition: simple_list.h:34
PGconn * connection
Definition: parallel_slot.h:23
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
Definition: vacuumdb.c:989
#define CppAsString2(x)
Definition: c.h:289
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
bool ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
void PQclear(PGresult *res)
Definition: fe-exec.c:694
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6590
#define Assert(condition)
Definition: c.h:804
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:1641
SimpleStringListCell * head
Definition: simple_list.h:42
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:73
bool disable_page_skipping
Definition: vacuumdb.c:37
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
Definition: string_utils.c:293
volatile sig_atomic_t CancelRequested
Definition: cancel.c:52
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
Definition: common.c:34
void ParallelSlotsTerminate(ParallelSlotArray *sa)
int i
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
bool TableCommandResultHandler(PGresult *res, PGconn *conn, void *context)
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
bool force_index_cleanup
Definition: vacuumdb.c:44
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3667
#define _(x)
Definition: elog.c:89
bool no_index_cleanup
Definition: vacuumdb.c:43
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92