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