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.

◆ ANALYZE_NUM_STAGES

#define ANALYZE_NUM_STAGES   3

Definition at line 73 of file vacuumdb.c.

Typedef Documentation

◆ vacuumingOptions

Function Documentation

◆ help()

static void help ( const char *  progname)
static

Definition at line 961 of file vacuumdb.c.

962 {
963  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
964  printf(_("Usage:\n"));
965  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
966  printf(_("\nOptions:\n"));
967  printf(_(" -a, --all vacuum all databases\n"));
968  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
969  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
970  printf(_(" -e, --echo show the commands being sent to the server\n"));
971  printf(_(" -f, --full do full vacuuming\n"));
972  printf(_(" -F, --freeze freeze row transaction information\n"));
973  printf(_(" --force-index-cleanup always remove index entries that point to dead tuples\n"));
974  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
975  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
976  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
977  printf(_(" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
978  printf(_(" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
979  printf(_(" --no-truncate don't truncate empty pages at the end of the table\n"));
980  printf(_(" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
981  printf(_(" -q, --quiet don't write any messages\n"));
982  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
983  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
984  printf(_(" -v, --verbose write a lot of output\n"));
985  printf(_(" -V, --version output version information, then exit\n"));
986  printf(_(" -z, --analyze update optimizer statistics\n"));
987  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
988  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
989  " stages for faster results; no vacuum\n"));
990  printf(_(" -?, --help show this help, then exit\n"));
991  printf(_("\nConnection options:\n"));
992  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
993  printf(_(" -p, --port=PORT database server port\n"));
994  printf(_(" -U, --username=USERNAME user name to connect as\n"));
995  printf(_(" -w, --no-password never prompt for password\n"));
996  printf(_(" -W, --password force password prompt\n"));
997  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
998  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
999  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1000  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
1001 }
#define _(x)
Definition: elog.c:89
const char * progname
Definition: main.c:50
#define printf(...)
Definition: port.h:231

References _, printf, and progname.

Referenced by main().

◆ main()

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

Definition at line 77 of file vacuumdb.c.

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':
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':
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  /* getopt_long already emitted a complaint */
241  pg_log_error_hint("Try \"%s --help\" for more information.", progname);
242  exit(1);
243  }
244  }
245 
246  /*
247  * Non-option argument specifies database name as long as it wasn't
248  * already specified with -d / --dbname
249  */
250  if (optind < argc && dbname == NULL)
251  {
252  dbname = argv[optind];
253  optind++;
254  }
255 
256  if (optind < argc)
257  {
258  pg_log_error("too many command-line arguments (first is \"%s\")",
259  argv[optind]);
260  pg_log_error_hint("Try \"%s --help\" for more information.", progname);
261  exit(1);
262  }
263 
264  if (vacopts.analyze_only)
265  {
266  if (vacopts.full)
267  pg_fatal("cannot use the \"%s\" option when performing only analyze",
268  "full");
269  if (vacopts.freeze)
270  pg_fatal("cannot use the \"%s\" option when performing only analyze",
271  "freeze");
272  if (vacopts.disable_page_skipping)
273  pg_fatal("cannot use the \"%s\" option when performing only analyze",
274  "disable-page-skipping");
275  if (vacopts.no_index_cleanup)
276  pg_fatal("cannot use the \"%s\" option when performing only analyze",
277  "no-index-cleanup");
278  if (vacopts.force_index_cleanup)
279  pg_fatal("cannot use the \"%s\" option when performing only analyze",
280  "force-index-cleanup");
281  if (!vacopts.do_truncate)
282  pg_fatal("cannot use the \"%s\" option when performing only analyze",
283  "no-truncate");
284  if (!vacopts.process_toast)
285  pg_fatal("cannot use the \"%s\" option when performing only analyze",
286  "no-process-toast");
287  /* allow 'and_analyze' with 'analyze_only' */
288  }
289 
290  /* Prohibit full and analyze_only options with parallel option */
291  if (vacopts.parallel_workers >= 0)
292  {
293  if (vacopts.analyze_only)
294  pg_fatal("cannot use the \"%s\" option when performing only analyze",
295  "parallel");
296  if (vacopts.full)
297  pg_fatal("cannot use the \"%s\" option when performing full vacuum",
298  "parallel");
299  }
300 
301  /* Prohibit --no-index-cleanup and --force-index-cleanup together */
302  if (vacopts.no_index_cleanup && vacopts.force_index_cleanup)
303  pg_fatal("cannot use the \"%s\" option with the \"%s\" option",
304  "no-index-cleanup", "force-index-cleanup");
305 
306  /* fill cparams except for dbname, which is set below */
307  cparams.pghost = host;
308  cparams.pgport = port;
309  cparams.pguser = username;
310  cparams.prompt_password = prompt_password;
311  cparams.override_dbname = NULL;
312 
313  setup_cancel_handler(NULL);
314 
315  /* Avoid opening extra connections. */
316  if (tbl_count && (concurrentCons > tbl_count))
317  concurrentCons = tbl_count;
318 
319  if (alldb)
320  {
321  if (dbname)
322  pg_fatal("cannot vacuum all databases and a specific one at the same time");
323  if (tables.head != NULL)
324  pg_fatal("cannot vacuum specific table(s) in all databases");
325 
326  cparams.dbname = maintenance_db;
327 
328  vacuum_all_databases(&cparams, &vacopts,
329  analyze_in_stages,
330  concurrentCons,
331  progname, echo, quiet);
332  }
333  else
334  {
335  if (dbname == NULL)
336  {
337  if (getenv("PGDATABASE"))
338  dbname = getenv("PGDATABASE");
339  else if (getenv("PGUSER"))
340  dbname = getenv("PGUSER");
341  else
343  }
344 
345  cparams.dbname = dbname;
346 
347  if (analyze_in_stages)
348  {
349  int stage;
350 
351  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
352  {
353  vacuum_one_database(&cparams, &vacopts,
354  stage,
355  &tables,
356  concurrentCons,
357  progname, echo, quiet);
358  }
359  }
360  else
361  vacuum_one_database(&cparams, &vacopts,
363  &tables,
364  concurrentCons,
365  progname, echo, quiet);
366  }
367 
368  exit(0);
369 }
static void setup_cancel_handler(void)
Definition: parallel.c:610
#define PG_TEXTDOMAIN(domain)
Definition: c.h:1212
void set_pglocale_pgservice(const char *argv0, const char *app)
Definition: exec.c:446
char * pg_strdup(const char *in)
Definition: fe_memutils.c:85
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
Definition: getopt_long.c:57
#define no_argument
Definition: getopt_long.h:24
#define required_argument
Definition: getopt_long.h:25
exit(1)
void pg_logging_init(const char *argv0)
Definition: logging.c:83
#define pg_log_error(...)
Definition: logging.h:106
#define pg_log_error_hint(...)
Definition: logging.h:112
bool option_parse_int(const char *optarg, const char *optname, int min_range, int max_range, int *result)
Definition: option_utils.c:50
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
Definition: option_utils.c:24
#define pg_fatal(...)
PGDLLIMPORT int optind
Definition: getopt.c:50
PGDLLIMPORT char * optarg
Definition: getopt.c:52
static int port
Definition: pg_regress.c:92
const char * username
Definition: pgbench.c:309
const char * get_progname(const char *argv0)
Definition: path.c:574
char * c
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
char * dbname
Definition: streamutil.c:51
SimpleStringListCell * head
Definition: simple_list.h:42
const char * pguser
Definition: connect_utils.h:31
char * override_dbname
Definition: pg_backup.h:89
char * pgport
Definition: pg_backup.h:83
char * pghost
Definition: pg_backup.h:84
char * dbname
Definition: pg_backup.h:82
enum trivalue prompt_password
Definition: connect_utils.h:32
bool force_index_cleanup
Definition: vacuumdb.c:44
bool no_index_cleanup
Definition: vacuumdb.c:43
int parallel_workers
Definition: vacuumdb.c:41
bool disable_page_skipping
Definition: vacuumdb.c:37
bool skip_locked
Definition: vacuumdb.c:38
bool process_toast
Definition: vacuumdb.c:46
bool do_truncate
Definition: vacuumdb.c:45
bool and_analyze
Definition: vacuumdb.c:34
bool analyze_only
Definition: vacuumdb.c:32
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74
static void help(const char *progname)
Definition: vacuumdb.c:961
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:73
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:731
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:72
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:385
trivalue
Definition: vacuumlo.c:35
@ TRI_YES
Definition: vacuumlo.c:38
@ TRI_DEFAULT
Definition: vacuumlo.c:36
@ TRI_NO
Definition: vacuumlo.c:37

References ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, vacuumingOptions::analyze_only, vacuumingOptions::and_analyze, dbname, _connParams::dbname, vacuumingOptions::disable_page_skipping, vacuumingOptions::do_truncate, exit(), vacuumingOptions::force_index_cleanup, 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_fatal, pg_log_error, pg_log_error_hint, 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.

◆ prepare_vacuum_command()

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

Definition at line 797 of file vacuumdb.c.

799 {
800  const char *paren = " (";
801  const char *comma = ", ";
802  const char *sep = paren;
803 
804  resetPQExpBuffer(sql);
805 
806  if (vacopts->analyze_only)
807  {
808  appendPQExpBufferStr(sql, "ANALYZE");
809 
810  /* parenthesized grammar of ANALYZE is supported since v11 */
811  if (serverVersion >= 110000)
812  {
813  if (vacopts->skip_locked)
814  {
815  /* SKIP_LOCKED is supported since v12 */
816  Assert(serverVersion >= 120000);
817  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
818  sep = comma;
819  }
820  if (vacopts->verbose)
821  {
822  appendPQExpBuffer(sql, "%sVERBOSE", sep);
823  sep = comma;
824  }
825  if (sep != paren)
826  appendPQExpBufferChar(sql, ')');
827  }
828  else
829  {
830  if (vacopts->verbose)
831  appendPQExpBufferStr(sql, " VERBOSE");
832  }
833  }
834  else
835  {
836  appendPQExpBufferStr(sql, "VACUUM");
837 
838  /* parenthesized grammar of VACUUM is supported since v9.0 */
839  if (serverVersion >= 90000)
840  {
841  if (vacopts->disable_page_skipping)
842  {
843  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
844  Assert(serverVersion >= 90600);
845  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
846  sep = comma;
847  }
848  if (vacopts->no_index_cleanup)
849  {
850  /* "INDEX_CLEANUP FALSE" has been supported since v12 */
851  Assert(serverVersion >= 120000);
852  Assert(!vacopts->force_index_cleanup);
853  appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
854  sep = comma;
855  }
856  if (vacopts->force_index_cleanup)
857  {
858  /* "INDEX_CLEANUP TRUE" has been supported since v12 */
859  Assert(serverVersion >= 120000);
860  Assert(!vacopts->no_index_cleanup);
861  appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
862  sep = comma;
863  }
864  if (!vacopts->do_truncate)
865  {
866  /* TRUNCATE is supported since v12 */
867  Assert(serverVersion >= 120000);
868  appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
869  sep = comma;
870  }
871  if (!vacopts->process_toast)
872  {
873  /* PROCESS_TOAST is supported since v14 */
874  Assert(serverVersion >= 140000);
875  appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
876  sep = comma;
877  }
878  if (vacopts->skip_locked)
879  {
880  /* SKIP_LOCKED is supported since v12 */
881  Assert(serverVersion >= 120000);
882  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
883  sep = comma;
884  }
885  if (vacopts->full)
886  {
887  appendPQExpBuffer(sql, "%sFULL", sep);
888  sep = comma;
889  }
890  if (vacopts->freeze)
891  {
892  appendPQExpBuffer(sql, "%sFREEZE", sep);
893  sep = comma;
894  }
895  if (vacopts->verbose)
896  {
897  appendPQExpBuffer(sql, "%sVERBOSE", sep);
898  sep = comma;
899  }
900  if (vacopts->and_analyze)
901  {
902  appendPQExpBuffer(sql, "%sANALYZE", sep);
903  sep = comma;
904  }
905  if (vacopts->parallel_workers >= 0)
906  {
907  /* PARALLEL is supported since v13 */
908  Assert(serverVersion >= 130000);
909  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
910  vacopts->parallel_workers);
911  sep = comma;
912  }
913  if (sep != paren)
914  appendPQExpBufferChar(sql, ')');
915  }
916  else
917  {
918  if (vacopts->full)
919  appendPQExpBufferStr(sql, " FULL");
920  if (vacopts->freeze)
921  appendPQExpBufferStr(sql, " FREEZE");
922  if (vacopts->verbose)
923  appendPQExpBufferStr(sql, " VERBOSE");
924  if (vacopts->and_analyze)
925  appendPQExpBufferStr(sql, " ANALYZE");
926  }
927  }
928 
929  appendPQExpBuffer(sql, " %s;", table);
930 }
Assert(fmt[strlen(fmt) - 1] !='\n')
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369

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().

◆ run_vacuum_command()

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

Definition at line 939 of file vacuumdb.c.

941 {
942  bool status;
943 
944  if (echo)
945  printf("%s\n", sql);
946 
947  status = PQsendQuery(conn, sql) == 1;
948 
949  if (!status)
950  {
951  if (table)
952  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
953  table, PQdb(conn), PQerrorMessage(conn));
954  else
955  pg_log_error("vacuuming of database \"%s\" failed: %s",
957  }
958 }
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6754
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6908
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1424
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:229
PGconn * conn
Definition: streamutil.c:54

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

Referenced by vacuum_one_database().

◆ 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 731 of file vacuumdb.c.

736 {
737  PGconn *conn;
738  PGresult *result;
739  int stage;
740  int i;
741 
742  conn = connectMaintenanceDatabase(cparams, progname, echo);
743  result = executeQuery(conn,
744  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
745  echo);
746  PQfinish(conn);
747 
748  if (analyze_in_stages)
749  {
750  /*
751  * When analyzing all databases in stages, we analyze them all in the
752  * fastest stage first, so that initial statistics become available
753  * for all of them as soon as possible.
754  *
755  * This means we establish several times as many connections, but
756  * that's a secondary consideration.
757  */
758  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
759  {
760  for (i = 0; i < PQntuples(result); i++)
761  {
762  cparams->override_dbname = PQgetvalue(result, i, 0);
763 
764  vacuum_one_database(cparams, vacopts,
765  stage,
766  NULL,
767  concurrentCons,
768  progname, echo, quiet);
769  }
770  }
771  }
772  else
773  {
774  for (i = 0; i < PQntuples(result); i++)
775  {
776  cparams->override_dbname = PQgetvalue(result, i, 0);
777 
778  vacuum_one_database(cparams, vacopts,
780  NULL,
781  concurrentCons,
782  progname, echo, quiet);
783  }
784  }
785 
786  PQclear(result);
787 }
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4261
void PQclear(PGresult *res)
Definition: fe-exec.c:718
int PQntuples(const PGresult *res)
Definition: fe-exec.c:3340
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3735
int i
Definition: isn.c:73
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1699

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

Referenced by main().

◆ 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 385 of file vacuumdb.c.

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

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(), exit(), fflush(), 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_fatal, pg_free(), PQclear(), PQclientEncoding(), PQdb(), PQfinish(), PQgetisnull(), PQgetvalue(), PQntuples(), PQserverVersion(), prepare_vacuum_command(), printf, vacuumingOptions::process_toast, progname, res, 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().