PostgreSQL Source Code  git master
vacuumdb.c File Reference
#include "postgres_fe.h"
#include "catalog/pg_class_d.h"
#include "common.h"
#include "common/logging.h"
#include "fe_utils/cancel.h"
#include "fe_utils/connect.h"
#include "fe_utils/simple_list.h"
#include "fe_utils/string_utils.h"
#include "scripts_parallel.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 (const char *dbname, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet)
 
static void vacuum_all_databases (vacuumingOptions *vacopts, bool analyze_in_stages, const char *maintenance_db, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet)
 
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 68 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 69 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 918 of file vacuumdb.c.

References _, and printf.

Referenced by main().

919 {
920  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
921  printf(_("Usage:\n"));
922  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
923  printf(_("\nOptions:\n"));
924  printf(_(" -a, --all vacuum all databases\n"));
925  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
926  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
927  printf(_(" -e, --echo show the commands being sent to the server\n"));
928  printf(_(" -f, --full do full vacuuming\n"));
929  printf(_(" -F, --freeze freeze row transaction information\n"));
930  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
931  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
932  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
933  printf(_(" -P, --parallel=PARALLEL_DEGREE use this many background workers for vacuum, if available\n"));
934  printf(_(" -q, --quiet don't write any messages\n"));
935  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
936  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
937  printf(_(" -v, --verbose write a lot of output\n"));
938  printf(_(" -V, --version output version information, then exit\n"));
939  printf(_(" -z, --analyze update optimizer statistics\n"));
940  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
941  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
942  " stages for faster results; no vacuum\n"));
943  printf(_(" -?, --help show this help, then exit\n"));
944  printf(_("\nConnection options:\n"));
945  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
946  printf(_(" -p, --port=PORT database server port\n"));
947  printf(_(" -U, --username=USERNAME user name to connect as\n"));
948  printf(_(" -w, --no-password never prompt for password\n"));
949  printf(_(" -W, --password force password prompt\n"));
950  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
951  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
952  printf(_("\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
953  printf(_("%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
954 }
#define printf(...)
Definition: port.h:199
const char * progname
Definition: pg_standby.c:36
#define _(x)
Definition: elog.c:88

◆ main()

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

Definition at line 73 of file vacuumdb.c.

References _, ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, vacuumingOptions::analyze_only, vacuumingOptions::and_analyze, dbname, vacuumingOptions::disable_page_skipping, 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, vacuumingOptions::parallel_workers, pg_log_error, pg_logging_init(), pg_strdup(), PG_TEXTDOMAIN, port, progname, 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.

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

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

Referenced by vacuum_one_database().

786 {
787  const char *paren = " (";
788  const char *comma = ", ";
789  const char *sep = paren;
790 
791  resetPQExpBuffer(sql);
792 
793  if (vacopts->analyze_only)
794  {
795  appendPQExpBufferStr(sql, "ANALYZE");
796 
797  /* parenthesized grammar of ANALYZE is supported since v11 */
798  if (serverVersion >= 110000)
799  {
800  if (vacopts->skip_locked)
801  {
802  /* SKIP_LOCKED is supported since v12 */
803  Assert(serverVersion >= 120000);
804  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
805  sep = comma;
806  }
807  if (vacopts->verbose)
808  {
809  appendPQExpBuffer(sql, "%sVERBOSE", sep);
810  sep = comma;
811  }
812  if (sep != paren)
813  appendPQExpBufferChar(sql, ')');
814  }
815  else
816  {
817  if (vacopts->verbose)
818  appendPQExpBufferStr(sql, " VERBOSE");
819  }
820  }
821  else
822  {
823  appendPQExpBufferStr(sql, "VACUUM");
824 
825  /* parenthesized grammar of VACUUM is supported since v9.0 */
826  if (serverVersion >= 90000)
827  {
828  if (vacopts->disable_page_skipping)
829  {
830  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
831  Assert(serverVersion >= 90600);
832  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
833  sep = comma;
834  }
835  if (vacopts->skip_locked)
836  {
837  /* SKIP_LOCKED is supported since v12 */
838  Assert(serverVersion >= 120000);
839  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
840  sep = comma;
841  }
842  if (vacopts->full)
843  {
844  appendPQExpBuffer(sql, "%sFULL", sep);
845  sep = comma;
846  }
847  if (vacopts->freeze)
848  {
849  appendPQExpBuffer(sql, "%sFREEZE", sep);
850  sep = comma;
851  }
852  if (vacopts->verbose)
853  {
854  appendPQExpBuffer(sql, "%sVERBOSE", sep);
855  sep = comma;
856  }
857  if (vacopts->and_analyze)
858  {
859  appendPQExpBuffer(sql, "%sANALYZE", sep);
860  sep = comma;
861  }
862  if (vacopts->parallel_workers >= 0)
863  {
864  /* PARALLEL is supported since v13 */
865  Assert(serverVersion >= 130000);
866  appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
867  vacopts->parallel_workers);
868  sep = comma;
869  }
870  if (sep != paren)
871  appendPQExpBufferChar(sql, ')');
872  }
873  else
874  {
875  if (vacopts->full)
876  appendPQExpBufferStr(sql, " FULL");
877  if (vacopts->freeze)
878  appendPQExpBufferStr(sql, " FREEZE");
879  if (vacopts->verbose)
880  appendPQExpBufferStr(sql, " VERBOSE");
881  if (vacopts->and_analyze)
882  appendPQExpBufferStr(sql, " ANALYZE");
883  }
884  }
885 
886  appendPQExpBuffer(sql, " %s;", table);
887 }
bool skip_locked
Definition: vacuumdb.c:35
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
int parallel_workers
Definition: vacuumdb.c:38
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
bool and_analyze
Definition: vacuumdb.c:31
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
#define Assert(condition)
Definition: c.h:738
bool disable_page_skipping
Definition: vacuumdb.c:34
bool analyze_only
Definition: vacuumdb.c:29
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 896 of file vacuumdb.c.

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

Referenced by vacuum_one_database().

898 {
899  bool status;
900 
901  if (echo)
902  printf("%s\n", sql);
903 
904  status = PQsendQuery(conn, sql) == 1;
905 
906  if (!status)
907  {
908  if (table)
909  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
910  table, PQdb(conn), PQerrorMessage(conn));
911  else
912  pg_log_error("vacuuming of database \"%s\" failed: %s",
913  PQdb(conn), PQerrorMessage(conn));
914  }
915 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6687
#define pg_log_error(...)
Definition: logging.h:79
#define printf(...)
Definition: port.h:199
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1234
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6537
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:225

◆ vacuum_all_databases()

static void vacuum_all_databases ( vacuumingOptions vacopts,
bool  analyze_in_stages,
const char *  maintenance_db,
const char *  host,
const char *  port,
const char *  username,
enum trivalue  prompt_password,
int  concurrentCons,
const char *  progname,
bool  echo,
bool  quiet 
)
static

Definition at line 706 of file vacuumdb.c.

References ANALYZE_NO_STAGE, ANALYZE_NUM_STAGES, appendConnStrVal(), appendPQExpBufferStr(), conn, connectMaintenanceDatabase(), connstr, PQExpBufferData::data, executeQuery(), i, initPQExpBuffer(), PQclear(), PQfinish(), PQgetvalue(), PQntuples(), resetPQExpBuffer(), termPQExpBuffer(), and vacuum_one_database().

Referenced by main().

713 {
714  PGconn *conn;
715  PGresult *result;
717  int stage;
718  int i;
719 
720  conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
721  prompt_password, progname, echo);
722  result = executeQuery(conn,
723  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
724  echo);
725  PQfinish(conn);
726 
727  initPQExpBuffer(&connstr);
728  if (analyze_in_stages)
729  {
730  /*
731  * When analyzing all databases in stages, we analyze them all in the
732  * fastest stage first, so that initial statistics become available
733  * for all of them as soon as possible.
734  *
735  * This means we establish several times as many connections, but
736  * that's a secondary consideration.
737  */
738  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
739  {
740  for (i = 0; i < PQntuples(result); i++)
741  {
742  resetPQExpBuffer(&connstr);
743  appendPQExpBufferStr(&connstr, "dbname=");
744  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
745 
746  vacuum_one_database(connstr.data, vacopts,
747  stage,
748  NULL,
749  host, port, username, prompt_password,
750  concurrentCons,
751  progname, echo, quiet);
752  }
753  }
754  }
755  else
756  {
757  for (i = 0; i < PQntuples(result); i++)
758  {
759  resetPQExpBuffer(&connstr);
760  appendPQExpBufferStr(&connstr, "dbname=");
761  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
762 
763  vacuum_one_database(connstr.data, vacopts,
765  NULL,
766  host, port, username, prompt_password,
767  concurrentCons,
768  progname, echo, quiet);
769  }
770  }
771  termPQExpBuffer(&connstr);
772 
773  PQclear(result);
774 }
PGconn * connectMaintenanceDatabase(const char *maintenance_db, const char *pghost, const char *pgport, const char *pguser, enum trivalue prompt_password, const char *progname, bool echo)
Definition: common.c:153
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1879
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
Definition: fe-exec.c:3163
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:131
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
void appendConnStrVal(PQExpBuffer buf, const char *str)
Definition: string_utils.c:545
void PQfinish(PGconn *conn)
Definition: fe-connect.c:4185
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:68
const char * progname
Definition: pg_standby.c:36
int PQntuples(const PGresult *res)
Definition: fe-exec.c:2769
static void vacuum_one_database(const char *dbname, vacuumingOptions *vacopts, int stage, SimpleStringList *tables, const char *host, const char *port, const char *username, enum trivalue prompt_password, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuumdb.c:368
PGconn * conn
Definition: streamutil.c:54
static int port
Definition: pg_regress.c:90
static char * username
Definition: initdb.c:133
void PQclear(PGresult *res)
Definition: fe-exec.c:694
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:69
int i
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:148
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:92
static char * connstr
Definition: pg_dumpall.c:61

◆ vacuum_one_database()

static void vacuum_one_database ( const char *  dbname,
vacuumingOptions vacopts,
int  stage,
SimpleStringList tables,
const char *  host,
const char *  port,
const char *  username,
enum trivalue  prompt_password,
int  concurrentCons,
const char *  progname,
bool  echo,
bool  quiet 
)
static

Definition at line 368 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, executeCommand(), executeQuery(), fmtQualifiedId(), gettext_noop, SimpleStringList::head, i, initPQExpBuffer(), vacuumingOptions::min_mxid_age, vacuumingOptions::min_xid_age, SimpleStringListCell::next, vacuumingOptions::parallel_workers, ParallelSlotsGetIdle(), ParallelSlotsSetup(), ParallelSlotsTerminate(), ParallelSlotsWaitCompletion(), pg_free(), pg_log_error, PQclear(), PQclientEncoding(), PQdb(), PQfinish(), PQgetisnull(), PQgetvalue(), PQntuples(), PQserverVersion(), prepare_vacuum_command(), printf, resetPQExpBuffer(), run_vacuum_command(), simple_string_list_append(), vacuumingOptions::skip_locked, splitTableColumnsSpec(), generate_unaccent_rules::stdout, termPQExpBuffer(), and SimpleStringListCell::val.

Referenced by main(), and vacuum_all_databases().

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