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/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 65 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 66 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 873 of file vacuumdb.c.

References _, and printf.

Referenced by main().

874 {
875  printf(_("%s cleans and analyzes a PostgreSQL database.\n\n"), progname);
876  printf(_("Usage:\n"));
877  printf(_(" %s [OPTION]... [DBNAME]\n"), progname);
878  printf(_("\nOptions:\n"));
879  printf(_(" -a, --all vacuum all databases\n"));
880  printf(_(" -d, --dbname=DBNAME database to vacuum\n"));
881  printf(_(" --disable-page-skipping disable all page-skipping behavior\n"));
882  printf(_(" -e, --echo show the commands being sent to the server\n"));
883  printf(_(" -f, --full do full vacuuming\n"));
884  printf(_(" -F, --freeze freeze row transaction information\n"));
885  printf(_(" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
886  printf(_(" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
887  printf(_(" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
888  printf(_(" -q, --quiet don't write any messages\n"));
889  printf(_(" --skip-locked skip relations that cannot be immediately locked\n"));
890  printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
891  printf(_(" -v, --verbose write a lot of output\n"));
892  printf(_(" -V, --version output version information, then exit\n"));
893  printf(_(" -z, --analyze update optimizer statistics\n"));
894  printf(_(" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
895  printf(_(" --analyze-in-stages only update optimizer statistics, in multiple\n"
896  " stages for faster results; no vacuum\n"));
897  printf(_(" -?, --help show this help, then exit\n"));
898  printf(_("\nConnection options:\n"));
899  printf(_(" -h, --host=HOSTNAME database server host or socket directory\n"));
900  printf(_(" -p, --port=PORT database server port\n"));
901  printf(_(" -U, --username=USERNAME user name to connect as\n"));
902  printf(_(" -w, --no-password never prompt for password\n"));
903  printf(_(" -W, --password force password prompt\n"));
904  printf(_(" --maintenance-db=DBNAME alternate maintenance database\n"));
905  printf(_("\nRead the description of the SQL command VACUUM for details.\n"));
906  printf(_("\nReport bugs to <pgsql-bugs@lists.postgresql.org>.\n"));
907 }
#define printf(...)
Definition: port.h:198
const char * progname
Definition: pg_standby.c:36
#define _(x)
Definition: elog.c:87

◆ main()

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

Definition at line 70 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, 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.

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

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

Referenced by vacuum_one_database().

749 {
750  const char *paren = " (";
751  const char *comma = ", ";
752  const char *sep = paren;
753 
754  resetPQExpBuffer(sql);
755 
756  if (vacopts->analyze_only)
757  {
758  appendPQExpBufferStr(sql, "ANALYZE");
759 
760  /* parenthesized grammar of ANALYZE is supported since v11 */
761  if (serverVersion >= 110000)
762  {
763  if (vacopts->skip_locked)
764  {
765  /* SKIP_LOCKED is supported since v12 */
766  Assert(serverVersion >= 120000);
767  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
768  sep = comma;
769  }
770  if (vacopts->verbose)
771  {
772  appendPQExpBuffer(sql, "%sVERBOSE", sep);
773  sep = comma;
774  }
775  if (sep != paren)
776  appendPQExpBufferChar(sql, ')');
777  }
778  else
779  {
780  if (vacopts->verbose)
781  appendPQExpBufferStr(sql, " VERBOSE");
782  }
783  }
784  else
785  {
786  appendPQExpBufferStr(sql, "VACUUM");
787 
788  /* parenthesized grammar of VACUUM is supported since v9.0 */
789  if (serverVersion >= 90000)
790  {
791  if (vacopts->disable_page_skipping)
792  {
793  /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
794  Assert(serverVersion >= 90600);
795  appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
796  sep = comma;
797  }
798  if (vacopts->skip_locked)
799  {
800  /* SKIP_LOCKED is supported since v12 */
801  Assert(serverVersion >= 120000);
802  appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
803  sep = comma;
804  }
805  if (vacopts->full)
806  {
807  appendPQExpBuffer(sql, "%sFULL", sep);
808  sep = comma;
809  }
810  if (vacopts->freeze)
811  {
812  appendPQExpBuffer(sql, "%sFREEZE", sep);
813  sep = comma;
814  }
815  if (vacopts->verbose)
816  {
817  appendPQExpBuffer(sql, "%sVERBOSE", sep);
818  sep = comma;
819  }
820  if (vacopts->and_analyze)
821  {
822  appendPQExpBuffer(sql, "%sANALYZE", sep);
823  sep = comma;
824  }
825  if (sep != paren)
826  appendPQExpBufferChar(sql, ')');
827  }
828  else
829  {
830  if (vacopts->full)
831  appendPQExpBufferStr(sql, " FULL");
832  if (vacopts->freeze)
833  appendPQExpBufferStr(sql, " FREEZE");
834  if (vacopts->verbose)
835  appendPQExpBufferStr(sql, " VERBOSE");
836  if (vacopts->and_analyze)
837  appendPQExpBufferStr(sql, " ANALYZE");
838  }
839  }
840 
841  appendPQExpBuffer(sql, " %s;", table);
842 }
bool skip_locked
Definition: vacuumdb.c:34
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:369
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:267
bool and_analyze
Definition: vacuumdb.c:30
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:380
#define Assert(condition)
Definition: c.h:733
bool disable_page_skipping
Definition: vacuumdb.c:33
bool analyze_only
Definition: vacuumdb.c:28
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 851 of file vacuumdb.c.

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

Referenced by vacuum_one_database().

853 {
854  bool status;
855 
856  if (echo)
857  printf("%s\n", sql);
858 
859  status = PQsendQuery(conn, sql) == 1;
860 
861  if (!status)
862  {
863  if (table)
864  pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
865  table, PQdb(conn), PQerrorMessage(conn));
866  else
867  pg_log_error("vacuuming of database \"%s\" failed: %s",
868  PQdb(conn), PQerrorMessage(conn));
869  }
870 }
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:6617
#define pg_log_error(...)
Definition: logging.h:79
#define printf(...)
Definition: port.h:198
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1234
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:6467
static void static void status(const char *fmt,...) pg_attribute_printf(1
Definition: pg_regress.c:226

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

676 {
677  PGconn *conn;
678  PGresult *result;
680  int stage;
681  int i;
682 
683  conn = connectMaintenanceDatabase(maintenance_db, host, port, username,
684  prompt_password, progname, echo);
685  result = executeQuery(conn,
686  "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
687  echo);
688  PQfinish(conn);
689 
690  initPQExpBuffer(&connstr);
691  if (analyze_in_stages)
692  {
693  /*
694  * When analyzing all databases in stages, we analyze them all in the
695  * fastest stage first, so that initial statistics become available
696  * for all of them as soon as possible.
697  *
698  * This means we establish several times as many connections, but
699  * that's a secondary consideration.
700  */
701  for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
702  {
703  for (i = 0; i < PQntuples(result); i++)
704  {
705  resetPQExpBuffer(&connstr);
706  appendPQExpBufferStr(&connstr, "dbname=");
707  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
708 
709  vacuum_one_database(connstr.data, vacopts,
710  stage,
711  NULL,
712  host, port, username, prompt_password,
713  concurrentCons,
714  progname, echo, quiet);
715  }
716  }
717  }
718  else
719  {
720  for (i = 0; i < PQntuples(result); i++)
721  {
722  resetPQExpBuffer(&connstr);
723  appendPQExpBufferStr(&connstr, "dbname=");
724  appendConnStrVal(&connstr, PQgetvalue(result, i, 0));
725 
726  vacuum_one_database(connstr.data, vacopts,
728  NULL,
729  host, port, username, prompt_password,
730  concurrentCons,
731  progname, echo, quiet);
732  }
733  }
734  termPQExpBuffer(&connstr);
735 
736  PQclear(result);
737 }
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:160
static PGresult * executeQuery(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1878
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:4119
#define ANALYZE_NO_STAGE
Definition: vacuumdb.c:65
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:338
PGconn * conn
Definition: streamutil.c:54
static int port
Definition: pg_regress.c:91
static char * username
Definition: initdb.c:133
void PQclear(PGresult *res)
Definition: fe-exec.c:694
#define ANALYZE_NUM_STAGES
Definition: vacuumdb.c:66
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 338 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, 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().

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