Line data Source code
1 : /*-------------------------------------------------------------------------
2 : * vacuuming.c
3 : * Helper routines for vacuumdb
4 : *
5 : * Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group
6 : * Portions Copyright (c) 1994, Regents of the University of California
7 : *
8 : * src/bin/scripts/vacuuming.c
9 : *
10 : *-------------------------------------------------------------------------
11 : */
12 :
13 : #include "postgres_fe.h"
14 :
15 : #include "catalog/pg_attribute_d.h"
16 : #include "catalog/pg_class_d.h"
17 : #include "common/connect.h"
18 : #include "common/logging.h"
19 : #include "fe_utils/cancel.h"
20 : #include "fe_utils/option_utils.h"
21 : #include "fe_utils/parallel_slot.h"
22 : #include "fe_utils/query_utils.h"
23 : #include "fe_utils/string_utils.h"
24 : #include "vacuuming.h"
25 :
26 :
27 : static int vacuum_one_database(ConnParams *cparams,
28 : vacuumingOptions *vacopts,
29 : int stage,
30 : SimpleStringList *objects,
31 : SimpleStringList **found_objs,
32 : int concurrentCons,
33 : const char *progname, bool echo, bool quiet);
34 : static int vacuum_all_databases(ConnParams *cparams,
35 : vacuumingOptions *vacopts,
36 : SimpleStringList *objects,
37 : int concurrentCons,
38 : const char *progname, bool echo, bool quiet);
39 : static SimpleStringList *retrieve_objects(PGconn *conn,
40 : vacuumingOptions *vacopts,
41 : SimpleStringList *objects,
42 : bool echo);
43 : static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
44 : vacuumingOptions *vacopts, const char *table);
45 : static void run_vacuum_command(PGconn *conn, const char *sql, bool echo,
46 : const char *table);
47 :
48 : /*
49 : * Executes vacuum/analyze as indicated. Returns 0 if the plan is carried
50 : * to completion, or -1 in case of certain errors (which should hopefully
51 : * been already reported.) Other errors are reported via pg_fatal().
52 : */
53 : int
54 130 : vacuuming_main(ConnParams *cparams, const char *dbname,
55 : const char *maintenance_db, vacuumingOptions *vacopts,
56 : SimpleStringList *objects,
57 : unsigned int tbl_count, int concurrentCons,
58 : const char *progname, bool echo, bool quiet)
59 : {
60 130 : setup_cancel_handler(NULL);
61 :
62 : /* Avoid opening extra connections. */
63 130 : if (tbl_count > 0 && (concurrentCons > tbl_count))
64 0 : concurrentCons = tbl_count;
65 :
66 130 : if (vacopts->objfilter & OBJFILTER_ALL_DBS)
67 : {
68 46 : cparams->dbname = maintenance_db;
69 :
70 46 : return vacuum_all_databases(cparams, vacopts,
71 : objects,
72 : concurrentCons,
73 : progname, echo, quiet);
74 : }
75 : else
76 : {
77 84 : if (dbname == NULL)
78 : {
79 0 : if (getenv("PGDATABASE"))
80 0 : dbname = getenv("PGDATABASE");
81 0 : else if (getenv("PGUSER"))
82 0 : dbname = getenv("PGUSER");
83 : else
84 0 : dbname = get_user_name_or_exit(progname);
85 : }
86 :
87 84 : cparams->dbname = dbname;
88 :
89 84 : if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
90 : {
91 10 : SimpleStringList *found_objs = NULL;
92 :
93 40 : for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
94 : {
95 : int ret;
96 :
97 30 : ret = vacuum_one_database(cparams, vacopts,
98 : stage,
99 : objects,
100 30 : vacopts->missing_stats_only ? &found_objs : NULL,
101 : concurrentCons,
102 : progname, echo, quiet);
103 30 : if (ret != 0)
104 0 : return ret;
105 : }
106 :
107 10 : return EXIT_SUCCESS;
108 : }
109 : else
110 74 : return vacuum_one_database(cparams, vacopts,
111 : ANALYZE_NO_STAGE,
112 : objects, NULL,
113 : concurrentCons,
114 : progname, echo, quiet);
115 : }
116 : }
117 :
118 : /*
119 : * vacuum_one_database
120 : *
121 : * Process tables in the given database.
122 : *
123 : * There are two ways to specify the list of objects to process:
124 : *
125 : * 1) The "found_objs" parameter is a double pointer to a fully qualified list
126 : * of objects to process, as returned by a previous call to
127 : * vacuum_one_database().
128 : *
129 : * a) If both "found_objs" (the double pointer) and "*found_objs" (the
130 : * once-dereferenced double pointer) are not NULL, this list takes
131 : * priority, and anything specified in "objects" is ignored.
132 : *
133 : * b) If "found_objs" (the double pointer) is not NULL but "*found_objs"
134 : * (the once-dereferenced double pointer) _is_ NULL, the "objects"
135 : * parameter takes priority, and the results of the catalog query
136 : * described in (2) are stored in "found_objs".
137 : *
138 : * c) If "found_objs" (the double pointer) is NULL, the "objects"
139 : * parameter again takes priority, and the results of the catalog query
140 : * are not saved.
141 : *
142 : * 2) The "objects" parameter is a user-specified list of objects to process.
143 : * When (1b) or (1c) applies, this function performs a catalog query to
144 : * retrieve a fully qualified list of objects to process, as described
145 : * below.
146 : *
147 : * a) If "objects" is not NULL, the catalog query gathers only the objects
148 : * listed in "objects".
149 : *
150 : * b) If "objects" is NULL, all tables in the database are gathered.
151 : *
152 : * Note that this function is only concerned with running exactly one stage
153 : * when in analyze-in-stages mode; caller must iterate on us if necessary.
154 : *
155 : * If concurrentCons is > 1, multiple connections are used to vacuum tables
156 : * in parallel.
157 : */
158 : static int
159 214 : vacuum_one_database(ConnParams *cparams,
160 : vacuumingOptions *vacopts,
161 : int stage,
162 : SimpleStringList *objects,
163 : SimpleStringList **found_objs,
164 : int concurrentCons,
165 : const char *progname, bool echo, bool quiet)
166 : {
167 : PQExpBufferData sql;
168 : PGconn *conn;
169 : SimpleStringListCell *cell;
170 : ParallelSlotArray *sa;
171 214 : int ntups = 0;
172 : const char *initcmd;
173 214 : SimpleStringList *retobjs = NULL;
174 214 : int ret = EXIT_SUCCESS;
175 214 : const char *stage_commands[] = {
176 : "SET default_statistics_target=1; SET vacuum_cost_delay=0;",
177 : "SET default_statistics_target=10; RESET vacuum_cost_delay;",
178 : "RESET default_statistics_target;"
179 : };
180 214 : const char *stage_messages[] = {
181 : gettext_noop("Generating minimal optimizer statistics (1 target)"),
182 : gettext_noop("Generating medium optimizer statistics (10 targets)"),
183 : gettext_noop("Generating default (full) optimizer statistics")
184 : };
185 :
186 : Assert(stage == ANALYZE_NO_STAGE ||
187 : (stage >= 0 && stage < ANALYZE_NUM_STAGES));
188 :
189 214 : conn = connectDatabase(cparams, progname, echo, false, true);
190 :
191 212 : if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
192 : {
193 0 : PQfinish(conn);
194 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
195 : "disable-page-skipping", "9.6");
196 : }
197 :
198 212 : if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
199 : {
200 0 : PQfinish(conn);
201 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
202 : "no-index-cleanup", "12");
203 : }
204 :
205 212 : if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
206 : {
207 0 : PQfinish(conn);
208 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
209 : "force-index-cleanup", "12");
210 : }
211 :
212 212 : if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
213 : {
214 0 : PQfinish(conn);
215 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
216 : "no-truncate", "12");
217 : }
218 :
219 212 : if (!vacopts->process_main && PQserverVersion(conn) < 160000)
220 : {
221 0 : PQfinish(conn);
222 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
223 : "no-process-main", "16");
224 : }
225 :
226 212 : if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
227 : {
228 0 : PQfinish(conn);
229 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
230 : "no-process-toast", "14");
231 : }
232 :
233 212 : if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
234 : {
235 0 : PQfinish(conn);
236 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
237 : "skip-locked", "12");
238 : }
239 :
240 212 : if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
241 : {
242 0 : PQfinish(conn);
243 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
244 : "--min-xid-age", "9.6");
245 : }
246 :
247 212 : if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
248 : {
249 0 : PQfinish(conn);
250 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
251 : "--min-mxid-age", "9.6");
252 : }
253 :
254 212 : if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
255 : {
256 0 : PQfinish(conn);
257 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
258 : "--parallel", "13");
259 : }
260 :
261 212 : if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
262 : {
263 0 : PQfinish(conn);
264 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
265 : "--buffer-usage-limit", "16");
266 : }
267 :
268 212 : if (vacopts->missing_stats_only && PQserverVersion(conn) < 150000)
269 : {
270 0 : PQfinish(conn);
271 0 : pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
272 : "--missing-stats-only", "15");
273 : }
274 :
275 : /* skip_database_stats is used automatically if server supports it */
276 212 : vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
277 :
278 212 : if (!quiet)
279 : {
280 212 : if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
281 42 : printf(_("%s: processing database \"%s\": %s\n"),
282 : progname, PQdb(conn), _(stage_messages[stage]));
283 : else
284 170 : printf(_("%s: vacuuming database \"%s\"\n"),
285 : progname, PQdb(conn));
286 212 : fflush(stdout);
287 : }
288 :
289 : /*
290 : * If the caller provided the results of a previous catalog query, just
291 : * use that. Otherwise, run the catalog query ourselves and set the
292 : * return variable if provided.
293 : */
294 212 : if (found_objs && *found_objs)
295 16 : retobjs = *found_objs;
296 : else
297 : {
298 196 : retobjs = retrieve_objects(conn, vacopts, objects, echo);
299 194 : if (found_objs)
300 8 : *found_objs = retobjs;
301 : }
302 :
303 : /*
304 : * Count the number of objects in the catalog query result. If there are
305 : * none, we are done.
306 : */
307 10112 : for (cell = retobjs ? retobjs->head : NULL; cell; cell = cell->next)
308 9902 : ntups++;
309 :
310 210 : if (ntups == 0)
311 : {
312 22 : PQfinish(conn);
313 22 : return EXIT_SUCCESS;
314 : }
315 :
316 : /*
317 : * Ensure concurrentCons is sane. If there are more connections than
318 : * vacuumable relations, we don't need to use them all.
319 : */
320 188 : if (concurrentCons > ntups)
321 0 : concurrentCons = ntups;
322 188 : if (concurrentCons <= 0)
323 0 : concurrentCons = 1;
324 :
325 : /*
326 : * All slots need to be prepared to run the appropriate analyze stage, if
327 : * caller requested that mode. We have to prepare the initial connection
328 : * ourselves before setting up the slots.
329 : */
330 188 : if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
331 : {
332 30 : initcmd = stage_commands[stage];
333 30 : executeCommand(conn, initcmd, echo);
334 : }
335 : else
336 158 : initcmd = NULL;
337 :
338 : /*
339 : * Setup the database connections. We reuse the connection we already have
340 : * for the first slot. If not in parallel mode, the first slot in the
341 : * array contains the connection.
342 : */
343 188 : sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
344 188 : ParallelSlotsAdoptConn(sa, conn);
345 :
346 188 : initPQExpBuffer(&sql);
347 :
348 188 : cell = retobjs->head;
349 : do
350 : {
351 9902 : const char *tabname = cell->val;
352 : ParallelSlot *free_slot;
353 :
354 9902 : if (CancelRequested)
355 : {
356 0 : ret = EXIT_FAILURE;
357 0 : goto finish;
358 : }
359 :
360 9902 : free_slot = ParallelSlotsGetIdle(sa, NULL);
361 9902 : if (!free_slot)
362 : {
363 0 : ret = EXIT_FAILURE;
364 0 : goto finish;
365 : }
366 :
367 9902 : prepare_vacuum_command(free_slot->connection, &sql,
368 : vacopts, tabname);
369 :
370 : /*
371 : * Execute the vacuum. All errors are handled in processQueryResult
372 : * through ParallelSlotsGetIdle.
373 : */
374 9902 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
375 9902 : run_vacuum_command(free_slot->connection, sql.data,
376 : echo, tabname);
377 :
378 9902 : cell = cell->next;
379 9902 : } while (cell != NULL);
380 :
381 188 : if (!ParallelSlotsWaitCompletion(sa))
382 : {
383 2 : ret = EXIT_FAILURE;
384 2 : goto finish;
385 : }
386 :
387 : /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
388 186 : if (vacopts->mode == MODE_VACUUM && vacopts->skip_database_stats)
389 : {
390 120 : const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
391 120 : ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
392 :
393 120 : if (!free_slot)
394 : {
395 0 : ret = EXIT_FAILURE;
396 0 : goto finish;
397 : }
398 :
399 120 : ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
400 120 : run_vacuum_command(free_slot->connection, cmd, echo, NULL);
401 :
402 120 : if (!ParallelSlotsWaitCompletion(sa))
403 0 : ret = EXIT_FAILURE; /* error already reported by handler */
404 : }
405 :
406 186 : finish:
407 188 : ParallelSlotsTerminate(sa);
408 188 : pg_free(sa);
409 188 : termPQExpBuffer(&sql);
410 :
411 188 : return ret;
412 : }
413 :
414 : /*
415 : * Vacuum/analyze all connectable databases.
416 : *
417 : * In analyze-in-stages mode, we process all databases in one stage before
418 : * moving on to the next stage. That ensure minimal stats are available
419 : * quickly everywhere before generating more detailed ones.
420 : */
421 : static int
422 46 : vacuum_all_databases(ConnParams *cparams,
423 : vacuumingOptions *vacopts,
424 : SimpleStringList *objects,
425 : int concurrentCons,
426 : const char *progname, bool echo, bool quiet)
427 : {
428 : PGconn *conn;
429 : PGresult *result;
430 :
431 46 : conn = connectMaintenanceDatabase(cparams, progname, echo);
432 46 : result = executeQuery(conn,
433 : "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
434 : echo);
435 46 : PQfinish(conn);
436 :
437 46 : if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
438 : {
439 2 : SimpleStringList **found_objs = NULL;
440 :
441 2 : if (vacopts->missing_stats_only)
442 0 : found_objs = palloc0(PQntuples(result) * sizeof(SimpleStringList *));
443 :
444 : /*
445 : * When analyzing all databases in stages, we analyze them all in the
446 : * fastest stage first, so that initial statistics become available
447 : * for all of them as soon as possible.
448 : *
449 : * This means we establish several times as many connections, but
450 : * that's a secondary consideration.
451 : */
452 8 : for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
453 : {
454 18 : for (int i = 0; i < PQntuples(result); i++)
455 : {
456 : int ret;
457 :
458 12 : cparams->override_dbname = PQgetvalue(result, i, 0);
459 12 : ret = vacuum_one_database(cparams, vacopts, stage,
460 : objects,
461 12 : vacopts->missing_stats_only ? &found_objs[i] : NULL,
462 : concurrentCons,
463 : progname, echo, quiet);
464 12 : if (ret != EXIT_SUCCESS)
465 0 : return ret;
466 : }
467 : }
468 : }
469 : else
470 : {
471 142 : for (int i = 0; i < PQntuples(result); i++)
472 : {
473 : int ret;
474 :
475 98 : cparams->override_dbname = PQgetvalue(result, i, 0);
476 98 : ret = vacuum_one_database(cparams, vacopts,
477 : ANALYZE_NO_STAGE,
478 : objects,
479 : NULL,
480 : concurrentCons,
481 : progname, echo, quiet);
482 98 : if (ret != EXIT_SUCCESS)
483 0 : return ret;
484 : }
485 : }
486 :
487 46 : PQclear(result);
488 :
489 46 : return EXIT_SUCCESS;
490 : }
491 :
492 : /*
493 : * Prepare the list of tables to process by querying the catalogs.
494 : *
495 : * Since we execute the constructed query with the default search_path (which
496 : * could be unsafe), everything in this query MUST be fully qualified.
497 : *
498 : * First, build a WITH clause for the catalog query if any tables were
499 : * specified, with a set of values made of relation names and their optional
500 : * set of columns. This is used to match any provided column lists with the
501 : * generated qualified identifiers and to filter for the tables provided via
502 : * --table. If a listed table does not exist, the catalog query will fail.
503 : */
504 : static SimpleStringList *
505 196 : retrieve_objects(PGconn *conn, vacuumingOptions *vacopts,
506 : SimpleStringList *objects, bool echo)
507 : {
508 : PQExpBufferData buf;
509 : PQExpBufferData catalog_query;
510 : PGresult *res;
511 : SimpleStringListCell *cell;
512 196 : SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList));
513 196 : bool objects_listed = false;
514 :
515 196 : initPQExpBuffer(&catalog_query);
516 258 : for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
517 : {
518 62 : char *just_table = NULL;
519 62 : const char *just_columns = NULL;
520 :
521 62 : if (!objects_listed)
522 : {
523 58 : appendPQExpBufferStr(&catalog_query,
524 : "WITH listed_objects (object_oid, column_list) AS (\n"
525 : " VALUES (");
526 58 : objects_listed = true;
527 : }
528 : else
529 4 : appendPQExpBufferStr(&catalog_query, ",\n (");
530 :
531 62 : if (vacopts->objfilter & (OBJFILTER_SCHEMA | OBJFILTER_SCHEMA_EXCLUDE))
532 : {
533 20 : appendStringLiteralConn(&catalog_query, cell->val, conn);
534 20 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
535 : }
536 :
537 62 : if (vacopts->objfilter & OBJFILTER_TABLE)
538 : {
539 : /*
540 : * Split relation and column names given by the user, this is used
541 : * to feed the CTE with values on which are performed pre-run
542 : * validity checks as well. For now these happen only on the
543 : * relation name.
544 : */
545 42 : splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
546 : &just_table, &just_columns);
547 :
548 42 : appendStringLiteralConn(&catalog_query, just_table, conn);
549 42 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
550 : }
551 :
552 62 : if (just_columns && just_columns[0] != '\0')
553 10 : appendStringLiteralConn(&catalog_query, just_columns, conn);
554 : else
555 52 : appendPQExpBufferStr(&catalog_query, "NULL");
556 :
557 62 : appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
558 :
559 62 : pg_free(just_table);
560 : }
561 :
562 : /* Finish formatting the CTE */
563 196 : if (objects_listed)
564 58 : appendPQExpBufferStr(&catalog_query, "\n)\n");
565 :
566 196 : appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
567 :
568 196 : if (objects_listed)
569 58 : appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
570 :
571 196 : appendPQExpBufferStr(&catalog_query,
572 : " FROM pg_catalog.pg_class c\n"
573 : " JOIN pg_catalog.pg_namespace ns"
574 : " ON c.relnamespace OPERATOR(pg_catalog.=) ns.oid\n"
575 : " CROSS JOIN LATERAL (SELECT c.relkind IN ("
576 : CppAsString2(RELKIND_PARTITIONED_TABLE) ", "
577 : CppAsString2(RELKIND_PARTITIONED_INDEX) ")) as p (inherited)\n"
578 : " LEFT JOIN pg_catalog.pg_class t"
579 : " ON c.reltoastrelid OPERATOR(pg_catalog.=) t.oid\n");
580 :
581 : /*
582 : * Used to match the tables or schemas listed by the user, completing the
583 : * JOIN clause.
584 : */
585 196 : if (objects_listed)
586 : {
587 58 : appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
588 : " ON listed_objects.object_oid"
589 : " OPERATOR(pg_catalog.=) ");
590 :
591 58 : if (vacopts->objfilter & OBJFILTER_TABLE)
592 42 : appendPQExpBufferStr(&catalog_query, "c.oid\n");
593 : else
594 16 : appendPQExpBufferStr(&catalog_query, "ns.oid\n");
595 : }
596 :
597 : /*
598 : * Exclude temporary tables, beginning the WHERE clause.
599 : */
600 196 : appendPQExpBufferStr(&catalog_query,
601 : " WHERE c.relpersistence OPERATOR(pg_catalog.!=) "
602 : CppAsString2(RELPERSISTENCE_TEMP) "\n");
603 :
604 : /*
605 : * Used to match the tables or schemas listed by the user, for the WHERE
606 : * clause.
607 : */
608 196 : if (objects_listed)
609 : {
610 58 : if (vacopts->objfilter & OBJFILTER_SCHEMA_EXCLUDE)
611 8 : appendPQExpBufferStr(&catalog_query,
612 : " AND listed_objects.object_oid IS NULL\n");
613 : else
614 50 : appendPQExpBufferStr(&catalog_query,
615 : " AND listed_objects.object_oid IS NOT NULL\n");
616 : }
617 :
618 : /*
619 : * If no tables were listed, filter for the relevant relation types. If
620 : * tables were given via --table, don't bother filtering by relation type.
621 : * Instead, let the server decide whether a given relation can be
622 : * processed in which case the user will know about it.
623 : */
624 196 : if ((vacopts->objfilter & OBJFILTER_TABLE) == 0)
625 : {
626 : /*
627 : * vacuumdb should generally follow the behavior of the underlying
628 : * VACUUM and ANALYZE commands. In MODE_ANALYZE mode, process regular
629 : * tables, materialized views, and partitioned tables, just like
630 : * ANALYZE (with no specific target tables) does. Otherwise, process
631 : * only regular tables and materialized views, since VACUUM skips
632 : * partitioned tables when no target tables are specified.
633 : */
634 154 : if (vacopts->mode == MODE_ANALYZE)
635 22 : appendPQExpBufferStr(&catalog_query,
636 : " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
637 : CppAsString2(RELKIND_RELATION) ", "
638 : CppAsString2(RELKIND_MATVIEW) ", "
639 : CppAsString2(RELKIND_PARTITIONED_TABLE) "])\n");
640 : else
641 132 : appendPQExpBufferStr(&catalog_query,
642 : " AND c.relkind OPERATOR(pg_catalog.=) ANY (array["
643 : CppAsString2(RELKIND_RELATION) ", "
644 : CppAsString2(RELKIND_MATVIEW) "])\n");
645 : }
646 :
647 : /*
648 : * For --min-xid-age and --min-mxid-age, the age of the relation is the
649 : * greatest of the ages of the main relation and its associated TOAST
650 : * table. The commands generated by vacuumdb will also process the TOAST
651 : * table for the relation if necessary, so it does not need to be
652 : * considered separately.
653 : */
654 196 : if (vacopts->min_xid_age != 0)
655 : {
656 2 : appendPQExpBuffer(&catalog_query,
657 : " AND GREATEST(pg_catalog.age(c.relfrozenxid),"
658 : " pg_catalog.age(t.relfrozenxid)) "
659 : " OPERATOR(pg_catalog.>=) '%d'::pg_catalog.int4\n"
660 : " AND c.relfrozenxid OPERATOR(pg_catalog.!=)"
661 : " '0'::pg_catalog.xid\n",
662 : vacopts->min_xid_age);
663 : }
664 :
665 196 : if (vacopts->min_mxid_age != 0)
666 : {
667 2 : appendPQExpBuffer(&catalog_query,
668 : " AND GREATEST(pg_catalog.mxid_age(c.relminmxid),"
669 : " pg_catalog.mxid_age(t.relminmxid)) OPERATOR(pg_catalog.>=)"
670 : " '%d'::pg_catalog.int4\n"
671 : " AND c.relminmxid OPERATOR(pg_catalog.!=)"
672 : " '0'::pg_catalog.xid\n",
673 : vacopts->min_mxid_age);
674 : }
675 :
676 196 : if (vacopts->missing_stats_only)
677 : {
678 20 : appendPQExpBufferStr(&catalog_query, " AND (\n");
679 :
680 : /* regular stats */
681 20 : appendPQExpBufferStr(&catalog_query,
682 : " EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
683 : " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
684 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
685 : " AND NOT a.attisdropped\n"
686 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
687 : " AND a.attgenerated OPERATOR(pg_catalog.<>) "
688 : CppAsString2(ATTRIBUTE_GENERATED_VIRTUAL) "\n"
689 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
690 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
691 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
692 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
693 :
694 : /* extended stats */
695 20 : appendPQExpBufferStr(&catalog_query,
696 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
697 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
698 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
699 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
700 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
701 : " AND d.stxdinherit OPERATOR(pg_catalog.=) p.inherited))\n");
702 :
703 : /* expression indexes */
704 20 : appendPQExpBufferStr(&catalog_query,
705 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
706 : " JOIN pg_catalog.pg_index i"
707 : " ON i.indexrelid OPERATOR(pg_catalog.=) a.attrelid\n"
708 : " WHERE i.indrelid OPERATOR(pg_catalog.=) c.oid\n"
709 : " AND i.indkey[a.attnum OPERATOR(pg_catalog.-) 1::pg_catalog.int2]"
710 : " OPERATOR(pg_catalog.=) 0::pg_catalog.int2\n"
711 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
712 : " AND NOT a.attisdropped\n"
713 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
714 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
715 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
716 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
717 : " AND s.stainherit OPERATOR(pg_catalog.=) p.inherited))\n");
718 :
719 : /* inheritance and regular stats */
720 20 : appendPQExpBufferStr(&catalog_query,
721 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_attribute a\n"
722 : " WHERE a.attrelid OPERATOR(pg_catalog.=) c.oid\n"
723 : " AND a.attnum OPERATOR(pg_catalog.>) 0::pg_catalog.int2\n"
724 : " AND NOT a.attisdropped\n"
725 : " AND a.attstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
726 : " AND a.attgenerated OPERATOR(pg_catalog.<>) "
727 : CppAsString2(ATTRIBUTE_GENERATED_VIRTUAL) "\n"
728 : " AND c.relhassubclass\n"
729 : " AND NOT p.inherited\n"
730 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
731 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
732 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic s\n"
733 : " WHERE s.starelid OPERATOR(pg_catalog.=) a.attrelid\n"
734 : " AND s.staattnum OPERATOR(pg_catalog.=) a.attnum\n"
735 : " AND s.stainherit))\n");
736 :
737 : /* inheritance and extended stats */
738 20 : appendPQExpBufferStr(&catalog_query,
739 : " OR EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext e\n"
740 : " WHERE e.stxrelid OPERATOR(pg_catalog.=) c.oid\n"
741 : " AND e.stxstattarget IS DISTINCT FROM 0::pg_catalog.int2\n"
742 : " AND c.relhassubclass\n"
743 : " AND NOT p.inherited\n"
744 : " AND EXISTS (SELECT NULL FROM pg_catalog.pg_inherits h\n"
745 : " WHERE h.inhparent OPERATOR(pg_catalog.=) c.oid)\n"
746 : " AND NOT EXISTS (SELECT NULL FROM pg_catalog.pg_statistic_ext_data d\n"
747 : " WHERE d.stxoid OPERATOR(pg_catalog.=) e.oid\n"
748 : " AND d.stxdinherit))\n");
749 :
750 20 : appendPQExpBufferStr(&catalog_query, " )\n");
751 : }
752 :
753 : /*
754 : * Execute the catalog query. We use the default search_path for this
755 : * query for consistency with table lookups done elsewhere by the user.
756 : */
757 196 : appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
758 196 : executeCommand(conn, "RESET search_path;", echo);
759 196 : res = executeQuery(conn, catalog_query.data, echo);
760 194 : termPQExpBuffer(&catalog_query);
761 194 : PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo));
762 :
763 : /*
764 : * Build qualified identifiers for each table, including the column list
765 : * if given.
766 : */
767 194 : initPQExpBuffer(&buf);
768 10088 : for (int i = 0; i < PQntuples(res); i++)
769 : {
770 19788 : appendPQExpBufferStr(&buf,
771 9894 : fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
772 9894 : PQgetvalue(res, i, 0),
773 : PQclientEncoding(conn)));
774 :
775 9894 : if (objects_listed && !PQgetisnull(res, i, 2))
776 10 : appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
777 :
778 9894 : simple_string_list_append(found_objs, buf.data);
779 9894 : resetPQExpBuffer(&buf);
780 : }
781 194 : termPQExpBuffer(&buf);
782 194 : PQclear(res);
783 :
784 194 : return found_objs;
785 : }
786 :
787 : /*
788 : * Construct a vacuum/analyze command to run based on the given
789 : * options, in the given string buffer, which may contain previous garbage.
790 : *
791 : * The table name used must be already properly quoted. The command generated
792 : * depends on the server version involved and it is semicolon-terminated.
793 : */
794 : static void
795 9902 : prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
796 : vacuumingOptions *vacopts, const char *table)
797 : {
798 9902 : int serverVersion = PQserverVersion(conn);
799 9902 : const char *paren = " (";
800 9902 : const char *comma = ", ";
801 9902 : const char *sep = paren;
802 :
803 9902 : resetPQExpBuffer(sql);
804 :
805 9902 : if (vacopts->mode == MODE_ANALYZE ||
806 8348 : vacopts->mode == MODE_ANALYZE_IN_STAGES)
807 : {
808 2790 : appendPQExpBufferStr(sql, "ANALYZE");
809 :
810 : /* parenthesized grammar of ANALYZE is supported since v11 */
811 2790 : if (serverVersion >= 110000)
812 : {
813 2790 : if (vacopts->skip_locked)
814 : {
815 : /* SKIP_LOCKED is supported since v12 */
816 : Assert(serverVersion >= 120000);
817 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
818 136 : sep = comma;
819 : }
820 2790 : if (vacopts->verbose)
821 : {
822 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
823 0 : sep = comma;
824 : }
825 2790 : if (vacopts->buffer_usage_limit)
826 : {
827 : Assert(serverVersion >= 160000);
828 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
829 : vacopts->buffer_usage_limit);
830 0 : sep = comma;
831 : }
832 2790 : if (sep != paren)
833 136 : appendPQExpBufferChar(sql, ')');
834 : }
835 : else
836 : {
837 0 : if (vacopts->verbose)
838 0 : appendPQExpBufferStr(sql, " VERBOSE");
839 : }
840 : }
841 : else
842 : {
843 7112 : appendPQExpBufferStr(sql, "VACUUM");
844 :
845 : /* parenthesized grammar of VACUUM is supported since v9.0 */
846 7112 : if (serverVersion >= 90000)
847 : {
848 7112 : if (vacopts->disable_page_skipping)
849 : {
850 : /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
851 : Assert(serverVersion >= 90600);
852 136 : appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
853 136 : sep = comma;
854 : }
855 7112 : if (vacopts->no_index_cleanup)
856 : {
857 : /* "INDEX_CLEANUP FALSE" has been supported since v12 */
858 : Assert(serverVersion >= 120000);
859 : Assert(!vacopts->force_index_cleanup);
860 136 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
861 136 : sep = comma;
862 : }
863 7112 : if (vacopts->force_index_cleanup)
864 : {
865 : /* "INDEX_CLEANUP TRUE" has been supported since v12 */
866 : Assert(serverVersion >= 120000);
867 : Assert(!vacopts->no_index_cleanup);
868 0 : appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
869 0 : sep = comma;
870 : }
871 7112 : if (!vacopts->do_truncate)
872 : {
873 : /* TRUNCATE is supported since v12 */
874 : Assert(serverVersion >= 120000);
875 136 : appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
876 136 : sep = comma;
877 : }
878 7112 : if (!vacopts->process_main)
879 : {
880 : /* PROCESS_MAIN is supported since v16 */
881 : Assert(serverVersion >= 160000);
882 136 : appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
883 136 : sep = comma;
884 : }
885 7112 : if (!vacopts->process_toast)
886 : {
887 : /* PROCESS_TOAST is supported since v14 */
888 : Assert(serverVersion >= 140000);
889 136 : appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
890 136 : sep = comma;
891 : }
892 7112 : if (vacopts->skip_database_stats)
893 : {
894 : /* SKIP_DATABASE_STATS is supported since v16 */
895 : Assert(serverVersion >= 160000);
896 7112 : appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
897 7112 : sep = comma;
898 : }
899 7112 : if (vacopts->skip_locked)
900 : {
901 : /* SKIP_LOCKED is supported since v12 */
902 : Assert(serverVersion >= 120000);
903 136 : appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
904 136 : sep = comma;
905 : }
906 7112 : if (vacopts->full)
907 : {
908 136 : appendPQExpBuffer(sql, "%sFULL", sep);
909 136 : sep = comma;
910 : }
911 7112 : if (vacopts->freeze)
912 : {
913 2312 : appendPQExpBuffer(sql, "%sFREEZE", sep);
914 2312 : sep = comma;
915 : }
916 7112 : if (vacopts->verbose)
917 : {
918 0 : appendPQExpBuffer(sql, "%sVERBOSE", sep);
919 0 : sep = comma;
920 : }
921 7112 : if (vacopts->and_analyze)
922 : {
923 2318 : appendPQExpBuffer(sql, "%sANALYZE", sep);
924 2318 : sep = comma;
925 : }
926 7112 : if (vacopts->parallel_workers >= 0)
927 : {
928 : /* PARALLEL is supported since v13 */
929 : Assert(serverVersion >= 130000);
930 272 : appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
931 : vacopts->parallel_workers);
932 272 : sep = comma;
933 : }
934 7112 : if (vacopts->buffer_usage_limit)
935 : {
936 : Assert(serverVersion >= 160000);
937 0 : appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
938 : vacopts->buffer_usage_limit);
939 0 : sep = comma;
940 : }
941 7112 : if (sep != paren)
942 7112 : appendPQExpBufferChar(sql, ')');
943 : }
944 : else
945 : {
946 0 : if (vacopts->full)
947 0 : appendPQExpBufferStr(sql, " FULL");
948 0 : if (vacopts->freeze)
949 0 : appendPQExpBufferStr(sql, " FREEZE");
950 0 : if (vacopts->verbose)
951 0 : appendPQExpBufferStr(sql, " VERBOSE");
952 0 : if (vacopts->and_analyze)
953 0 : appendPQExpBufferStr(sql, " ANALYZE");
954 : }
955 : }
956 :
957 9902 : appendPQExpBuffer(sql, " %s;", table);
958 9902 : }
959 :
960 : /*
961 : * Send a vacuum/analyze command to the server, returning after sending the
962 : * command.
963 : *
964 : * Any errors during command execution are reported to stderr.
965 : */
966 : static void
967 10022 : run_vacuum_command(PGconn *conn, const char *sql, bool echo,
968 : const char *table)
969 : {
970 : bool status;
971 :
972 10022 : if (echo)
973 952 : printf("%s\n", sql);
974 :
975 10022 : status = PQsendQuery(conn, sql) == 1;
976 :
977 10022 : if (!status)
978 : {
979 0 : if (table)
980 : {
981 0 : pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
982 : table, PQdb(conn), PQerrorMessage(conn));
983 : }
984 : else
985 : {
986 0 : pg_log_error("vacuuming of database \"%s\" failed: %s",
987 : PQdb(conn), PQerrorMessage(conn));
988 : }
989 : }
990 10022 : }
991 :
992 : /*
993 : * Returns a newly malloc'd version of 'src' with escaped single quotes and
994 : * backslashes.
995 : */
996 : char *
997 0 : escape_quotes(const char *src)
998 : {
999 0 : char *result = escape_single_quotes_ascii(src);
1000 :
1001 0 : if (!result)
1002 0 : pg_fatal("out of memory");
1003 0 : return result;
1004 : }
|
