PostgreSQL Source Code  git master
vacuumdb.c File Reference
#include "postgres_fe.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 70 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 71 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 985 of file vacuumdb.c.

References _, and printf.

Referenced by main().

986 {
987  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
988  printf(_("Usage:\n"));
989  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
990  printf(_("\nOptions:\n"));
991  printf(_(" -a, --all vacuum all databases\n"));
992  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
993  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
994  printf(_(" -e, --echo show the commands being sent to the server\n"));
995  printf(_(" -f, --full do full vacuuming\n"));
996  printf(_(" -F, --freeze freeze row transaction information\n"));
997  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
998  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
999  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1000  printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1001  printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1002  printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
1003  printf(_(" -P, --parallel=PARALLEL_DEGREE use this many background workers for vacuum, if available\n"));
1004  printf(_(" -q, --quiet don't write any messages\n"));
1005  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
1006  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1007  printf(_(" -v, --verbose write a lot of output\n"));
1008  printf(_(" -V, --version output version information, then exit\n"));
1009  printf(_(" -z, --analyze update optimizer statistics\n"));
1010  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1011  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
1012  " stages for faster results; no vacuum\n"));
1013  printf(_(" -?, --help show this help, then exit\n"));
1014  printf(_("\nConnection options:\n"));
1015  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
1016  printf(_(" -p, --port=PORT database server port\n"));
1017  printf(_(" -U, --username=USERNAME user name to connect as\n"));
1018  printf(_(" -w, --no-password never prompt for password\n"));
1019  printf(_(" -W, --password force password prompt\n"));
1020  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
1021  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
1022  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1023  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1024 }
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 75 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_index_cleanup, vacuumingOptions::do_truncate, 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, optarg, optind, _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.

76 {
77  static struct option long_options[] = {
78  {"host", required_argument, NULL, 'h'},
79  {"port", required_argument, NULL, 'p'},
80  {"username", required_argument, NULL, 'U'},
81  {"no-password", no_argument, NULL, 'w'},
82  {"password", no_argument, NULL, 'W'},
83  {"echo", no_argument, NULL, 'e'},
84  {"quiet", no_argument, NULL, 'q'},
85  {"dbname", required_argument, NULL, 'd'},
86  {"analyze", no_argument, NULL, 'z'},
87  {"analyze-only", no_argument, NULL, 'Z'},
88  {"freeze", no_argument, NULL, 'F'},
89  {"all", no_argument, NULL, 'a'},
90  {"table", required_argument, NULL, 't'},
91  {"full", no_argument, NULL, 'f'},
92  {"verbose", no_argument, NULL, 'v'},
93  {"jobs", required_argument, NULL, 'j'},
94  {"parallel", required_argument, NULL, 'P'},
95  {"maintenance-db", required_argument, NULL, 2},
96  {"analyze-in-stages", no_argument, NULL, 3},
97  {"disable-page-skipping", no_argument, NULL, 4},
98  {"skip-locked", no_argument, NULL, 5},
99  {"min-xid-age", required_argument, NULL, 6},
100  {"min-mxid-age", required_argument, NULL, 7},
101  {"no-index-cleanup", no_argument, NULL, 8},
102  {"no-truncate", no_argument, NULL, 9},
103  {"no-process-toast", no_argument, NULL, 10},
104  {NULL, 0, NULL, 0}
105  };
106 
107  const char *progname;
108  int optindex;
109  int c;
110  const char *dbname = NULL;
111  const char *maintenance_db = NULL;
112  char *host = NULL;
113  char *port = NULL;
114  char *username = NULL;
115  enum trivalue prompt_password = TRI_DEFAULT;
116  ConnParams cparams;
117  bool echo = false;
118  bool quiet = false;
119  vacuumingOptions vacopts;
120  bool analyze_in_stages = false;
121  bool alldb = false;
122  SimpleStringList tables = {NULL, NULL};
123  int concurrentCons = 1;
124  int tbl_count = 0;
125 
126  /* initialize options */
127  memset(&vacopts, 0, sizeof(vacopts));
128  vacopts.parallel_workers = -1;
129  vacopts.do_index_cleanup = true;
130  vacopts.do_truncate = true;
131  vacopts.process_toast = true;
132 
133  pg_logging_init(argv[0]);
134  progname = get_progname(argv[0]);
135  set_pglocale_pgservice(argv[0], PG_TEXTDOMAIN("pgscripts"));
136 
137  handle_help_version_opts(argc, argv, "vacuumdb", help);
138 
139  while ((c = getopt_long(argc, argv, "h:p:U:wWeqd:zZFat:fvj:P:", long_options, &optindex)) != -1)
140  {
141  switch (c)
142  {
143  case 'h':
144  host = pg_strdup(optarg);
145  break;
146  case 'p':
147  port = pg_strdup(optarg);
148  break;
149  case 'U':
150  username = pg_strdup(optarg);
151  break;
152  case 'w':
153  prompt_password = TRI_NO;
154  break;
155  case 'W':
156  prompt_password = TRI_YES;
157  break;
158  case 'e':
159  echo = true;
160  break;
161  case 'q':
162  quiet = true;
163  break;
164  case 'd':
165  dbname = pg_strdup(optarg);
166  break;
167  case 'z':
168  vacopts.and_analyze = true;
169  break;
170  case 'Z':
171  vacopts.analyze_only = true;
172  break;
173  case 'F':
174  vacopts.freeze = true;
175  break;
176  case 'a':
177  alldb = true;
178  break;
179  case 't':
180  {
182  tbl_count++;
183  break;
184  }
185  case 'f':
186  vacopts.full = true;
187  break;
188  case 'v':
189  vacopts.verbose = true;
190  break;
191  case 'j':
192  concurrentCons = atoi(optarg);
193  if (concurrentCons <= 0)
194  {
195  pg_log_error("number of parallel jobs must be at least 1");
196  exit(1);
197  }
198  break;
199  case 'P':
200  vacopts.parallel_workers = atoi(optarg);
201  if (vacopts.parallel_workers < 0)
202  {
203  pg_log_error("parallel vacuum degree must be a non-negative integer");
204  exit(1);
205  }
206  break;
207  case 2:
208  maintenance_db = pg_strdup(optarg);
209  break;
210  case 3:
211  analyze_in_stages = vacopts.analyze_only = true;
212  break;
213  case 4:
214  vacopts.disable_page_skipping = true;
215  break;
216  case 5:
217  vacopts.skip_locked = true;
218  break;
219  case 6:
220  vacopts.min_xid_age = atoi(optarg);
221  if (vacopts.min_xid_age <= 0)
222  {
223  pg_log_error("minimum transaction ID age must be at least 1");
224  exit(1);
225  }
226  break;
227  case 7:
228  vacopts.min_mxid_age = atoi(optarg);
229  if (vacopts.min_mxid_age <= 0)
230  {
231  pg_log_error("minimum multixact ID age must be at least 1");
232  exit(1);
233  }
234  break;
235  case 8:
236  vacopts.do_index_cleanup = false;
237  break;
238  case 9:
239  vacopts.do_truncate = false;
240  break;
241  case 10:
242  vacopts.process_toast = false;
243  break;
244  default:
245  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
246  exit(1);
247  }
248  }
249 
250  /*
251  * Non-option argument specifies database name as long as it wasn't
252  * already specified with -d / --dbname
253  */
254  if (optind < argc && dbname == NULL)
255  {
256  dbname = argv[optind];
257  optind++;
258  }
259 
260  if (optind < argc)
261  {
262  pg_log_error("too many command-line arguments (first is \"%s\")",
263  argv[optind]);
264  fprintf(stderr, _("Try \"%s --help\" for more information.\n"), progname);
265  exit(1);
266  }
267 
268  if (vacopts.analyze_only)
269  {
270  if (vacopts.full)
271  {
272  pg_log_error("cannot use the \"%s\" option when performing only analyze",
273  "full");
274  exit(1);
275  }
276  if (vacopts.freeze)
277  {
278  pg_log_error("cannot use the \"%s\" option when performing only analyze",
279  "freeze");
280  exit(1);
281  }
282  if (vacopts.disable_page_skipping)
283  {
284  pg_log_error("cannot use the \"%s\" option when performing only analyze",
285  "disable-page-skipping");
286  exit(1);
287  }
288  if (!vacopts.do_index_cleanup)
289  {
290  pg_log_error("cannot use the \"%s\" option when performing only analyze",
291  "no-index-cleanup");
292  exit(1);
293  }
294  if (!vacopts.do_truncate)
295  {
296  pg_log_error("cannot use the \"%s\" option when performing only analyze",
297  "no-truncate");
298  exit(1);
299  }
300  if (!vacopts.process_toast)
301  {
302  pg_log_error("cannot use the \"%s\" option when performing only analyze",
303  "no-process-toast");
304  exit(1);
305  }
306  /* allow 'and_analyze' with 'analyze_only' */
307  }
308 
309  /* Prohibit full and analyze_only options with parallel option */
310  if (vacopts.parallel_workers >= 0)
311  {
312  if (vacopts.analyze_only)
313  {
314  pg_log_error("cannot use the \"%s\" option when performing only analyze",
315  "parallel");
316  exit(1);
317  }
318  if (vacopts.full)
319  {
320  pg_log_error("cannot use the \"%s\" option when performing full vacuum",
321  "parallel");
322  exit(1);
323  }
324  }
325 
326  /* fill cparams except for dbname, which is set below */
327  cparams.pghost = host;
328  cparams.pgport = port;
329  cparams.pguser = username;
330  cparams.prompt_password = prompt_password;
331  cparams.override_dbname = NULL;
332 
333  setup_cancel_handler(NULL);
334 
335  /* Avoid opening extra connections. */
336  if (tbl_count && (concurrentCons > tbl_count))
337  concurrentCons = tbl_count;
338 
339  if (alldb)
340  {
341  if (dbname)
342  {
343  pg_log_error("cannot vacuum all databases and a specific one at the same time");
344  exit(1);
345  }
346  if (tables.head != NULL)
347  {
348  pg_log_error("cannot vacuum specific table(s) in all databases");
349  exit(1);
350  }
351 
352  cparams.dbname = maintenance_db;
353 
354  vacuum_all_databases(&cparams, &vacopts,
355  analyze_in_stages,
356  concurrentCons,
357  progname, echo, quiet);
358  }
359  else
360  {
361  if (dbname == NULL)
362  {
363  if (getenv("PGDATABASE"))
364  dbname = getenv("PGDATABASE");
365  else if (getenv("PGUSER"))
366  dbname = getenv("PGUSER");
367  else
368  dbname = get_user_name_or_exit(progname);
369  }
370 
371  cparams.dbname = dbname;
372 
373  if (analyze_in_stages)
374  {
375  int stage;
376 
377  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
378  {
379  vacuum_one_database(&cparams, &vacopts,
380  stage,
381  &tables,
382  concurrentCons,
383  progname, echo, quiet);
384  }
385  }
386  else
387  vacuum_one_database(&cparams, &vacopts,
389  &tables,
390  concurrentCons,
391  progname, echo, quiet);
392  }
393 
394  exit(0);
395 }
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:411
bool skip_locked
Definition: vacuumdb.c:37
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:40
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:70
#define fprintf
Definition: port.h:220
bool process_toast
Definition: vacuumdb.c:44
char * dbname
Definition: pg_backup.h:65
#define required_argument
Definition: getopt_long.h:25
bool do_truncate
Definition: vacuumdb.c:43
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:22
char * c
bool do_index_cleanup
Definition: vacuumdb.c:42
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
bool and_analyze
Definition: vacuumdb.c:33
const char * username
Definition: pgbench.c:280
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:985
#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:71
bool disable_page_skipping
Definition: vacuumdb.c:36
bool analyze_only
Definition: vacuumdb.c:31
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:764
char * optarg
Definition: getopt.c:52
#define _(x)
Definition: elog.c:89
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 830 of file vacuumdb.c.

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

Referenced by vacuum_one_database().

832 {
833  const char *paren = " (";
834  const char *comma = ", ";
835  const char *sep = paren;
836 
837  resetPQExpBuffer(sql);
838 
839  if (vacopts->analyze_only)
840  {
841  appendPQExpBufferStr(sql, "ANALYZE");
842 
843  /* parenthesized grammar of ANALYZE is supported since v11 */
844  if (serverVersion >= 110000)
845  {
846  if (vacopts->skip_locked)
847  {
848  /* SKIP_LOCKED is supported since v12 */
849  Assert(serverVersion >= 120000);
850  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
851  sep = comma;
852  }
853  if (vacopts->verbose)
854  {
855  appendPQExpBuffer(sql, "%sVERBOSE", sep);
856  sep = comma;
857  }
858  if (sep != paren)
859  appendPQExpBufferChar(sql, ')');
860  }
861  else
862  {
863  if (vacopts->verbose)
864  appendPQExpBufferStr(sql, " VERBOSE");
865  }
866  }
867  else
868  {
869  appendPQExpBufferStr(sql, "VACUUM");
870 
871  /* parenthesized grammar of VACUUM is supported since v9.0 */
872  if (serverVersion >= 90000)
873  {
874  if (vacopts->disable_page_skipping)
875  {
876  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
877  Assert(serverVersion >= 90600);
878  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
879  sep = comma;
880  }
881  if (!vacopts->do_index_cleanup)
882  {
883  /* INDEX_CLEANUP is supported since v12 */
884  Assert(serverVersion >= 120000);
885  appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
886  sep = comma;
887  }
888  if (!vacopts->do_truncate)
889  {
890  /* TRUNCATE is supported since v12 */
891  Assert(serverVersion >= 120000);
892  appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
893  sep = comma;
894  }
895  if (!vacopts->process_toast)
896  {
897  /* PROCESS_TOAST is supported since v14 */
898  Assert(serverVersion >= 140000);
899  appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
900  sep = comma;
901  }
902  if (vacopts->skip_locked)
903  {
904  /* SKIP_LOCKED is supported since v12 */
905  Assert(serverVersion >= 120000);
906  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
907  sep = comma;
908  }
909  if (vacopts->full)
910  {
911  appendPQExpBuffer(sql, "%sFULL", sep);
912  sep = comma;
913  }
914  if (vacopts->freeze)
915  {
916  appendPQExpBuffer(sql, "%sFREEZE", sep);
917  sep = comma;
918  }
919  if (vacopts->verbose)
920  {
921  appendPQExpBuffer(sql, "%sVERBOSE", sep);
922  sep = comma;
923  }
924  if (vacopts->and_analyze)
925  {
926  appendPQExpBuffer(sql, "%sANALYZE", sep);
927  sep = comma;
928  }
929  if (vacopts->parallel_workers >= 0)
930  {
931  /* PARALLEL is supported since v13 */
932  Assert(serverVersion >= 130000);
933  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
934  vacopts->parallel_workers);
935  sep = comma;
936  }
937  if (sep != paren)
938  appendPQExpBufferChar(sql, ')');
939  }
940  else
941  {
942  if (vacopts->full)
943  appendPQExpBufferStr(sql, " FULL");
944  if (vacopts->freeze)
945  appendPQExpBufferStr(sql, " FREEZE");
946  if (vacopts->verbose)
947  appendPQExpBufferStr(sql, " VERBOSE");
948  if (vacopts->and_analyze)
949  appendPQExpBufferStr(sql, " ANALYZE");
950  }
951  }
952 
953  appendPQExpBuffer(sql, " %s;", table);
954 }
bool skip_locked
Definition: vacuumdb.c:37
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
int parallel_workers
Definition: vacuumdb.c:40
bool process_toast
Definition: vacuumdb.c:44
bool do_truncate
Definition: vacuumdb.c:43
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
bool do_index_cleanup
Definition: vacuumdb.c:42
bool and_analyze
Definition: vacuumdb.c:33
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
#define Assert(condition)
Definition: c.h:804
bool disable_page_skipping
Definition: vacuumdb.c:36
bool analyze_only
Definition: vacuumdb.c:31
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148

◆ run_vacuum_command()

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

Definition at line 963 of file vacuumdb.c.

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

Referenced by vacuum_one_database().

965 {
966  bool status;
967 
968  if (echo)
969  printf("%s\n", sql);
970 
971  status = PQsendQuery(conn, sql) == 1;
972 
973  if (!status)
974  {
975  if (table)
976  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
977  table, PQdb(conn), PQerrorMessage(conn));
978  else
979  pg_log_error("vacuuming of database \"%s\" failed: %s",
980  PQdb(conn), PQerrorMessage(conn));
981  }
982 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6727
#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:1279
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6573
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 764 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().

769 {
770  PGconn *conn;
771  PGresult *result;
772  int stage;
773  int i;
774 
775  conn = connectMaintenanceDatabase(cparams, progname, echo);
776  result = executeQuery(conn,
777  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
778  echo);
779  PQfinish(conn);
780 
781  if (analyze_in_stages)
782  {
783  /*
784  * When analyzing all databases in stages, we analyze them all in the
785  * fastest stage first, so that initial statistics become available
786  * for all of them as soon as possible.
787  *
788  * This means we establish several times as many connections, but
789  * that's a secondary consideration.
790  */
791  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
792  {
793  for (i = 0; i < PQntuples(result); i++)
794  {
795  cparams->override_dbname = PQgetvalue(result, i, 0);
796 
797  vacuum_one_database(cparams, vacopts,
798  stage,
799  NULL,
800  concurrentCons,
801  progname, echo, quiet);
802  }
803  }
804  }
805  else
806  {
807  for (i = 0; i < PQntuples(result); i++)
808  {
809  cparams->override_dbname = PQgetvalue(result, i, 0);
810 
811  vacuum_one_database(cparams, vacopts,
813  NULL,
814  concurrentCons,
815  progname, echo, quiet);
816  }
817  }
818 
819  PQclear(result);
820 }
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1874
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:411
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3561
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4221
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:70
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3167
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:680
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:71
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 411 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_index_cleanup, vacuumingOptions::do_truncate, executeCommand(), executeQuery(), fmtQualifiedId(), gettext_noop, SimpleStringList::head, i, initPQExpBuffer(), vacuumingOptions::min_mxid_age, vacuumingOptions::min_xid_age, SimpleStringListCell::next, 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().

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