17 #include "catalog/pg_class_d.h"
69 const char *
progname,
bool echo,
bool quiet);
73 bool analyze_in_stages,
75 const char *
progname,
bool echo,
bool quiet);
88 #define ANALYZE_NO_STAGE -1
89 #define ANALYZE_NUM_STAGES 3
93 main(
int argc,
char *argv[])
95 static struct option long_options[] = {
132 const char *
dbname = NULL;
133 const char *maintenance_db = NULL;
142 bool analyze_in_stages =
false;
144 int concurrentCons = 1;
148 memset(&vacopts, 0,
sizeof(vacopts));
162 while ((
c =
getopt_long(argc, argv,
"ad:efFh:j:n:N:p:P:qt:U:vwWzZ", long_options, &optindex)) != -1)
289 pg_log_error(
"too many command-line arguments (first is \"%s\")",
304 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
307 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
310 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
311 "disable-page-skipping");
313 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
316 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
317 "force-index-cleanup");
319 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
322 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
325 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
334 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
337 pg_fatal(
"cannot use the \"%s\" option when performing full vacuum",
343 pg_fatal(
"cannot use the \"%s\" option with the \"%s\" option",
344 "no-index-cleanup",
"force-index-cleanup");
356 if (tbl_count && (concurrentCons > tbl_count))
357 concurrentCons = tbl_count;
361 cparams.
dbname = maintenance_db;
372 if (getenv(
"PGDATABASE"))
373 dbname = getenv(
"PGDATABASE");
374 else if (getenv(
"PGUSER"))
375 dbname = getenv(
"PGUSER");
382 if (analyze_in_stages)
414 pg_fatal(
"cannot vacuum all databases and a specific one at the same time");
418 pg_fatal(
"cannot vacuum specific table(s) in all databases");
422 pg_fatal(
"cannot vacuum specific schema(s) in all databases");
426 pg_fatal(
"cannot exclude specific schema(s) in all databases");
430 pg_fatal(
"cannot vacuum all tables in schema(s) and specific table(s) at the same time");
434 pg_fatal(
"cannot vacuum specific table(s) and exclude schema(s) at the same time");
438 pg_fatal(
"cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
460 const char *
progname,
bool echo,
bool quiet)
473 bool objects_listed =
false;
474 bool has_where =
false;
476 const char *stage_commands[] = {
477 "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
478 "SET default_statistics_target=10; RESET vacuum_cost_delay;",
479 "RESET default_statistics_target;"
481 const char *stage_messages[] = {
482 gettext_noop(
"Generating minimal optimizer statistics (1 target)"),
483 gettext_noop(
"Generating medium optimizer statistics (10 targets)"),
484 gettext_noop(
"Generating default (full) optimizer statistics")
495 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
496 "disable-page-skipping",
"9.6");
502 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
503 "no-index-cleanup",
"12");
509 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
510 "force-index-cleanup",
"12");
516 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
517 "no-truncate",
"12");
523 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
524 "no-process-main",
"16");
530 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
531 "no-process-toast",
"14");
537 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
538 "skip-locked",
"12");
542 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
543 "--min-xid-age",
"9.6");
546 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
547 "--min-mxid-age",
"9.6");
550 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
559 printf(
_(
"%s: processing database \"%s\": %s\n"),
562 printf(
_(
"%s: vacuuming database \"%s\"\n"),
582 for (cell = objects ? objects->
head : NULL; cell; cell = cell->
next)
584 char *just_table = NULL;
585 const char *just_columns = NULL;
590 "WITH listed_objects (object_oid, column_list) "
592 objects_listed =
true;
612 &just_table, &just_columns);
618 if (just_columns && just_columns[0] !=
'\0')
638 " FROM pg_catalog.pg_class c\n"
639 " JOIN pg_catalog.pg_namespace ns"
640 " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
641 " LEFT JOIN pg_catalog.pg_class t"
642 " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
648 " ON listed_objects.object_oid ");
685 " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
686 " pg_catalog.age(t.relfrozenxid)) "
687 " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
688 " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
689 " '0'::pg_catalog.xid\n",
690 has_where ?
"AND" :
"WHERE", vacopts->
min_xid_age);
697 " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
698 " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
699 " '%d'::pg_catalog.int4\n"
700 " AND c.relminmxid OPERATOR(pg_catalog.!=)"
701 " '0'::pg_catalog.xid\n",
732 for (
i = 0;
i < ntups;
i++)
751 if (concurrentCons > ntups)
752 concurrentCons = ntups;
753 if (concurrentCons <= 0)
765 initcmd = stage_commands[stage];
779 cell = dbtables.
head;
782 const char *tabname = cell->
val;
810 }
while (cell != NULL);
821 const char *cmd =
"VACUUM (ONLY_DATABASE_STATS);";
857 bool analyze_in_stages,
859 const char *
progname,
bool echo,
bool quiet)
868 "SELECT datname FROM pg_database WHERE datallowconn ORDER BY 1;",
872 if (analyze_in_stages)
924 const char *paren =
" (";
925 const char *
comma =
", ";
926 const char *sep = paren;
935 if (serverVersion >= 110000)
940 Assert(serverVersion >= 120000);
963 if (serverVersion >= 90000)
968 Assert(serverVersion >= 90600);
975 Assert(serverVersion >= 120000);
983 Assert(serverVersion >= 120000);
991 Assert(serverVersion >= 120000);
998 Assert(serverVersion >= 160000);
1005 Assert(serverVersion >= 140000);
1012 Assert(serverVersion >= 160000);
1019 Assert(serverVersion >= 120000);
1046 Assert(serverVersion >= 130000);
1090 pg_log_error(
"vacuuming of table \"%s\" in database \"%s\" failed: %s",
1093 pg_log_error(
"vacuuming of database \"%s\" failed: %s",
1101 printf(
_(
"%s cleans and analyzes a PostgreSQL database.\n\n"),
progname);
1105 printf(
_(
" -a, --all vacuum all databases\n"));
1106 printf(
_(
" -d, --dbname=DBNAME database to vacuum\n"));
1107 printf(
_(
" --disable-page-skipping disable all page-skipping behavior\n"));
1108 printf(
_(
" -e, --echo show the commands being sent to the server\n"));
1109 printf(
_(
" -f, --full do full vacuuming\n"));
1110 printf(
_(
" -F, --freeze freeze row transaction information\n"));
1111 printf(
_(
" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1112 printf(
_(
" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1113 printf(
_(
" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1114 printf(
_(
" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1115 printf(
_(
" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1116 printf(
_(
" --no-process-main skip the main relation\n"));
1117 printf(
_(
" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1118 printf(
_(
" --no-truncate don't truncate empty pages at the end of the table\n"));
1119 printf(
_(
" -n, --schema=PATTERN vacuum tables in the specified schema(s) only\n"));
1120 printf(
_(
" -N, --exclude-schema=PATTERN do not vacuum tables in the specified schema(s)\n"));
1121 printf(
_(
" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1122 printf(
_(
" -q, --quiet don't write any messages\n"));
1123 printf(
_(
" --skip-locked skip relations that cannot be immediately locked\n"));
1124 printf(
_(
" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1125 printf(
_(
" -v, --verbose write a lot of output\n"));
1126 printf(
_(
" -V, --version output version information, then exit\n"));
1127 printf(
_(
" -z, --analyze update optimizer statistics\n"));
1128 printf(
_(
" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1129 printf(
_(
" --analyze-in-stages only update optimizer statistics, in multiple\n"
1130 " stages for faster results; no vacuum\n"));
1131 printf(
_(
" -?, --help show this help, then exit\n"));
1132 printf(
_(
"\nConnection options:\n"));
1133 printf(
_(
" -h, --host=HOSTNAME database server host or socket directory\n"));
1134 printf(
_(
" -p, --port=PORT database server port\n"));
1135 printf(
_(
" -U, --username=USERNAME user name to connect as\n"));
1136 printf(
_(
" -w, --no-password never prompt for password\n"));
1137 printf(
_(
" -W, --password force password prompt\n"));
1138 printf(
_(
" --maintenance-db=DBNAME alternate maintenance database\n"));
1139 printf(
_(
"\nRead the description of the SQL command VACUUM for details.\n"));
1140 printf(
_(
"\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1141 printf(
_(
"%s home page: <%s>\n"), PACKAGE_NAME, PACKAGE_URL);
static void setup_cancel_handler(void)
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
#define PG_TEXTDOMAIN(domain)
volatile sig_atomic_t CancelRequested
void set_pglocale_pgservice(const char *argv0, const char *app)
#define ALWAYS_SECURE_SEARCH_PATH_SQL
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
static void PGresult * res
int PQserverVersion(const PGconn *conn)
char * PQdb(const PGconn *conn)
char * PQerrorMessage(const PGconn *conn)
int PQclientEncoding(const PGconn *conn)
void PQfinish(PGconn *conn)
int PQntuples(const PGresult *res)
char * PQgetvalue(const PGresult *res, int tup_num, int field_num)
int PQgetisnull(const PGresult *res, int tup_num, int field_num)
int PQsendQuery(PGconn *conn, const char *query)
char * pg_strdup(const char *in)
int getopt_long(int argc, char *const argv[], const char *optstring, const struct option *longopts, int *longindex)
#define required_argument
static void const char fflush(stdout)
Assert(fmt[strlen(fmt) - 1] !='\n')
void pg_logging_init(const char *argv0)
#define pg_log_error(...)
#define pg_log_error_hint(...)
bool option_parse_int(const char *optarg, const char *optname, int min_range, int max_range, int *result)
void handle_help_version_opts(int argc, char *argv[], const char *fixed_progname, help_handler hlp)
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)
static PGconn * connectDatabase(const char *dbname, const char *connection_string, const char *pghost, const char *pgport, const char *pguser, trivalue prompt_password, bool fail_on_error)
static void executeCommand(PGconn *conn, const char *query)
static PGresult * executeQuery(PGconn *conn, const char *query)
PGDLLIMPORT char * optarg
static void static void status(const char *fmt,...) pg_attribute_printf(1
const char * get_progname(const char *argv0)
void initPQExpBuffer(PQExpBuffer str)
void resetPQExpBuffer(PQExpBuffer str)
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
void appendPQExpBufferChar(PQExpBuffer str, char ch)
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
void termPQExpBuffer(PQExpBuffer str)
void simple_string_list_append(SimpleStringList *list, const char *val)
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
const char * fmtQualifiedId(const char *schema, const char *id)
char val[FLEXIBLE_ARRAY_MEMBER]
struct SimpleStringListCell * next
SimpleStringListCell * head
enum trivalue prompt_password
bool disable_page_skipping
const char * get_user_name_or_exit(const char *progname)
int main(int argc, char *argv[])
void check_objfilter(void)
static void help(const char *progname)
struct vacuumingOptions vacuumingOptions
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
#define ANALYZE_NUM_STAGES
@ OBJFILTER_SCHEMA_EXCLUDE
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 vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet)