17 #include "catalog/pg_class_d.h"
70 const char *
progname,
bool echo,
bool quiet);
74 bool analyze_in_stages,
76 const char *
progname,
bool echo,
bool quiet);
91 #define ANALYZE_NO_STAGE -1
92 #define ANALYZE_NUM_STAGES 3
96 main(
int argc,
char *argv[])
98 static struct option long_options[] = {
136 const char *
dbname = NULL;
137 const char *maintenance_db = NULL;
146 bool analyze_in_stages =
false;
148 int concurrentCons = 1;
152 memset(&vacopts, 0,
sizeof(vacopts));
167 while ((
c =
getopt_long(argc, argv,
"ad:efFh:j:n:N:p:P:qt:U:vwWzZ", long_options, &optindex)) != -1)
297 pg_log_error(
"too many command-line arguments (first is \"%s\")",
312 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
315 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
318 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
319 "disable-page-skipping");
321 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
324 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
325 "force-index-cleanup");
327 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
330 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
333 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
342 pg_fatal(
"cannot use the \"%s\" option when performing only analyze",
345 pg_fatal(
"cannot use the \"%s\" option when performing full vacuum",
351 pg_fatal(
"cannot use the \"%s\" option with the \"%s\" option",
352 "no-index-cleanup",
"force-index-cleanup");
359 pg_fatal(
"cannot use the \"%s\" option with the \"%s\" option",
360 "buffer-usage-limit",
"full");
372 if (tbl_count && (concurrentCons > tbl_count))
373 concurrentCons = tbl_count;
377 cparams.
dbname = maintenance_db;
388 if (getenv(
"PGDATABASE"))
389 dbname = getenv(
"PGDATABASE");
390 else if (getenv(
"PGUSER"))
391 dbname = getenv(
"PGUSER");
398 if (analyze_in_stages)
430 pg_fatal(
"cannot vacuum all databases and a specific one at the same time");
434 pg_fatal(
"cannot vacuum specific table(s) in all databases");
438 pg_fatal(
"cannot vacuum specific schema(s) in all databases");
442 pg_fatal(
"cannot exclude specific schema(s) in all databases");
446 pg_fatal(
"cannot vacuum all tables in schema(s) and specific table(s) at the same time");
450 pg_fatal(
"cannot vacuum specific table(s) and exclude schema(s) at the same time");
454 pg_fatal(
"cannot vacuum all tables in schema(s) and exclude schema(s) at the same time");
490 const char *
progname,
bool echo,
bool quiet)
503 bool objects_listed =
false;
504 bool has_where =
false;
506 const char *stage_commands[] = {
507 "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
508 "SET default_statistics_target=10; RESET vacuum_cost_delay;",
509 "RESET default_statistics_target;"
511 const char *stage_messages[] = {
512 gettext_noop(
"Generating minimal optimizer statistics (1 target)"),
513 gettext_noop(
"Generating medium optimizer statistics (10 targets)"),
514 gettext_noop(
"Generating default (full) optimizer statistics")
525 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
526 "disable-page-skipping",
"9.6");
532 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
533 "no-index-cleanup",
"12");
539 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
540 "force-index-cleanup",
"12");
546 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
547 "no-truncate",
"12");
553 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
554 "no-process-main",
"16");
560 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
561 "no-process-toast",
"14");
567 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
568 "skip-locked",
"12");
572 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
573 "--min-xid-age",
"9.6");
576 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
577 "--min-mxid-age",
"9.6");
580 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
584 pg_fatal(
"cannot use the \"%s\" option on server versions older than PostgreSQL %s",
585 "--buffer-usage-limit",
"16");
593 printf(
_(
"%s: processing database \"%s\": %s\n"),
596 printf(
_(
"%s: vacuuming database \"%s\"\n"),
616 for (cell = objects ? objects->
head : NULL; cell; cell = cell->
next)
618 char *just_table = NULL;
619 const char *just_columns = NULL;
624 "WITH listed_objects (object_oid, column_list) "
626 objects_listed =
true;
646 &just_table, &just_columns);
652 if (just_columns && just_columns[0] !=
'\0')
672 " FROM pg_catalog.pg_class c\n"
673 " JOIN pg_catalog.pg_namespace ns"
674 " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
675 " LEFT JOIN pg_catalog.pg_class t"
676 " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
682 " ON listed_objects.object_oid"
683 " OPERATOR(pg_catalog.=) ");
692 " WHERE listed_objects.object_oid IS NULL\n");
695 " WHERE listed_objects.object_oid IS NOT NULL\n");
708 " %s c.relkind OPERATOR(pg_catalog.=) ANY (array["
711 has_where ?
"AND" :
"WHERE");
725 " %s GREATEST(pg_catalog.age(c.relfrozenxid),"
726 " pg_catalog.age(t.relfrozenxid)) "
727 " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
728 " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
729 " '0'::pg_catalog.xid\n",
730 has_where ?
"AND" :
"WHERE", vacopts->
min_xid_age);
737 " %s GREATEST(pg_catalog.mxid_age(c.relminmxid),"
738 " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
739 " '%d'::pg_catalog.int4\n"
740 " AND c.relminmxid OPERATOR(pg_catalog.!=)"
741 " '0'::pg_catalog.xid\n",
772 for (
i = 0;
i < ntups;
i++)
791 if (concurrentCons > ntups)
792 concurrentCons = ntups;
793 if (concurrentCons <= 0)
805 initcmd = stage_commands[stage];
819 cell = dbtables.
head;
822 const char *tabname = cell->
val;
850 }
while (cell != NULL);
861 const char *cmd =
"VACUUM (ONLY_DATABASE_STATS);";
897 bool analyze_in_stages,
899 const char *
progname,
bool echo,
bool quiet)
908 "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
912 if (analyze_in_stages)
964 const char *paren =
" (";
965 const char *
comma =
", ";
966 const char *sep = paren;
975 if (serverVersion >= 110000)
980 Assert(serverVersion >= 120000);
991 Assert(serverVersion >= 160000);
1010 if (serverVersion >= 90000)
1015 Assert(serverVersion >= 90600);
1022 Assert(serverVersion >= 120000);
1030 Assert(serverVersion >= 120000);
1038 Assert(serverVersion >= 120000);
1045 Assert(serverVersion >= 160000);
1052 Assert(serverVersion >= 140000);
1059 Assert(serverVersion >= 160000);
1066 Assert(serverVersion >= 120000);
1093 Assert(serverVersion >= 130000);
1100 Assert(serverVersion >= 160000);
1144 pg_log_error(
"vacuuming of table \"%s\" in database \"%s\" failed: %s",
1147 pg_log_error(
"vacuuming of database \"%s\" failed: %s",
1155 printf(
_(
"%s cleans and analyzes a PostgreSQL database.\n\n"),
progname);
1159 printf(
_(
" -a, --all vacuum all databases\n"));
1160 printf(
_(
" --buffer-usage-limit=SIZE size of ring buffer used for vacuum\n"));
1161 printf(
_(
" -d, --dbname=DBNAME database to vacuum\n"));
1162 printf(
_(
" --disable-page-skipping disable all page-skipping behavior\n"));
1163 printf(
_(
" -e, --echo show the commands being sent to the server\n"));
1164 printf(
_(
" -f, --full do full vacuuming\n"));
1165 printf(
_(
" -F, --freeze freeze row transaction information\n"));
1166 printf(
_(
" --force-index-cleanup always remove index entries that point to dead tuples\n"));
1167 printf(
_(
" -j, --jobs=NUM use this many concurrent connections to vacuum\n"));
1168 printf(
_(
" --min-mxid-age=MXID_AGE minimum multixact ID age of tables to vacuum\n"));
1169 printf(
_(
" --min-xid-age=XID_AGE minimum transaction ID age of tables to vacuum\n"));
1170 printf(
_(
" --no-index-cleanup don't remove index entries that point to dead tuples\n"));
1171 printf(
_(
" --no-process-main skip the main relation\n"));
1172 printf(
_(
" --no-process-toast skip the TOAST table associated with the table to vacuum\n"));
1173 printf(
_(
" --no-truncate don't truncate empty pages at the end of the table\n"));
1174 printf(
_(
" -n, --schema=SCHEMA vacuum tables in the specified schema(s) only\n"));
1175 printf(
_(
" -N, --exclude-schema=SCHEMA do not vacuum tables in the specified schema(s)\n"));
1176 printf(
_(
" -P, --parallel=PARALLEL_WORKERS use this many background workers for vacuum, if available\n"));
1177 printf(
_(
" -q, --quiet don't write any messages\n"));
1178 printf(
_(
" --skip-locked skip relations that cannot be immediately locked\n"));
1179 printf(
_(
" -t, --table='TABLE[(COLUMNS)]' vacuum specific table(s) only\n"));
1180 printf(
_(
" -v, --verbose write a lot of output\n"));
1181 printf(
_(
" -V, --version output version information, then exit\n"));
1182 printf(
_(
" -z, --analyze update optimizer statistics\n"));
1183 printf(
_(
" -Z, --analyze-only only update optimizer statistics; no vacuum\n"));
1184 printf(
_(
" --analyze-in-stages only update optimizer statistics, in multiple\n"
1185 " stages for faster results; no vacuum\n"));
1186 printf(
_(
" -?, --help show this help, then exit\n"));
1187 printf(
_(
"\nConnection options:\n"));
1188 printf(
_(
" -h, --host=HOSTNAME database server host or socket directory\n"));
1189 printf(
_(
" -p, --port=PORT database server port\n"));
1190 printf(
_(
" -U, --username=USERNAME user name to connect as\n"));
1191 printf(
_(
" -w, --no-password never prompt for password\n"));
1192 printf(
_(
" -W, --password force password prompt\n"));
1193 printf(
_(
" --maintenance-db=DBNAME alternate maintenance database\n"));
1194 printf(
_(
"\nRead the description of the SQL command VACUUM for details.\n"));
1195 printf(
_(
"\nReport bugs to <%s>.\n"), PACKAGE_BUGREPORT);
1196 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
const char * get_progname(const char *argv0)
char * escape_single_quotes_ascii(const char *src)
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
char * buffer_usage_limit
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 char * escape_quotes(const char *src)
static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet)