PostgreSQL Source Code: src/bin/scripts/vacuuming.c Source File

PostgreSQL Source Code git master
vacuuming.c
Go to the documentation of this file.
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
27static 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);
34static int vacuum_all_databases(ConnParams *cparams,
35 vacuumingOptions *vacopts,
36 SimpleStringList *objects,
37 int concurrentCons,
38 const char *progname, bool echo, bool quiet);
39static SimpleStringList *retrieve_objects(PGconn *conn,
40 vacuumingOptions *vacopts,
41 SimpleStringList *objects,
42 bool echo);
43static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
44 vacuumingOptions *vacopts, const char *table);
45static 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 */
53int
54 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 setup_cancel_handler(NULL);
61
62 /* Avoid opening extra connections. */
63 if (tbl_count > 0 && (concurrentCons > tbl_count))
64 concurrentCons = tbl_count;
65
66 if (vacopts->objfilter & OBJFILTER_ALL_DBS)
67 {
68 cparams->dbname = maintenance_db;
69
70 return vacuum_all_databases(cparams, vacopts,
71 objects,
72 concurrentCons,
73 progname, echo, quiet);
74 }
75 else
76 {
77 if (dbname == NULL)
78 {
79 if (getenv("PGDATABASE"))
80 dbname = getenv("PGDATABASE");
81 else if (getenv("PGUSER"))
82 dbname = getenv("PGUSER");
83 else
84 dbname = get_user_name_or_exit(progname);
85 }
86
87 cparams->dbname = dbname;
88
89 if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
90 {
91 SimpleStringList *found_objs = NULL;
92
93 for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
94 {
95 int ret;
96
97 ret = vacuum_one_database(cparams, vacopts,
98 stage,
99 objects,
100 vacopts->missing_stats_only ? &found_objs : NULL,
101 concurrentCons,
102 progname, echo, quiet);
103 if (ret != 0)
104 return ret;
105 }
106
107 return EXIT_SUCCESS;
108 }
109 else
110 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 */
158static int
159 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 int ntups = 0;
172 const char *initcmd;
173 SimpleStringList *retobjs = NULL;
174 int ret = EXIT_SUCCESS;
175 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 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 conn = connectDatabase(cparams, progname, echo, false, true);
190
191 if (vacopts->disable_page_skipping && PQserverVersion(conn) < 90600)
192 {
193 PQfinish(conn);
194 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
195 "disable-page-skipping", "9.6");
196 }
197
198 if (vacopts->no_index_cleanup && PQserverVersion(conn) < 120000)
199 {
200 PQfinish(conn);
201 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
202 "no-index-cleanup", "12");
203 }
204
205 if (vacopts->force_index_cleanup && PQserverVersion(conn) < 120000)
206 {
207 PQfinish(conn);
208 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
209 "force-index-cleanup", "12");
210 }
211
212 if (!vacopts->do_truncate && PQserverVersion(conn) < 120000)
213 {
214 PQfinish(conn);
215 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
216 "no-truncate", "12");
217 }
218
219 if (!vacopts->process_main && PQserverVersion(conn) < 160000)
220 {
221 PQfinish(conn);
222 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
223 "no-process-main", "16");
224 }
225
226 if (!vacopts->process_toast && PQserverVersion(conn) < 140000)
227 {
228 PQfinish(conn);
229 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
230 "no-process-toast", "14");
231 }
232
233 if (vacopts->skip_locked && PQserverVersion(conn) < 120000)
234 {
235 PQfinish(conn);
236 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
237 "skip-locked", "12");
238 }
239
240 if (vacopts->min_xid_age != 0 && PQserverVersion(conn) < 90600)
241 {
242 PQfinish(conn);
243 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
244 "--min-xid-age", "9.6");
245 }
246
247 if (vacopts->min_mxid_age != 0 && PQserverVersion(conn) < 90600)
248 {
249 PQfinish(conn);
250 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
251 "--min-mxid-age", "9.6");
252 }
253
254 if (vacopts->parallel_workers >= 0 && PQserverVersion(conn) < 130000)
255 {
256 PQfinish(conn);
257 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
258 "--parallel", "13");
259 }
260
261 if (vacopts->buffer_usage_limit && PQserverVersion(conn) < 160000)
262 {
263 PQfinish(conn);
264 pg_fatal("cannot use the \"%s\" option on server versions older than PostgreSQL %s",
265 "--buffer-usage-limit", "16");
266 }
267
268 if (vacopts->missing_stats_only && PQserverVersion(conn) < 150000)
269 {
270 PQfinish(conn);
271 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 vacopts->skip_database_stats = (PQserverVersion(conn) >= 160000);
277
278 if (!quiet)
279 {
280 if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
281 printf(_("%s: processing database \"%s\": %s\n"),
282 progname, PQdb(conn), _(stage_messages[stage]));
283 else
284 printf(_("%s: vacuuming database \"%s\"\n"),
285 progname, PQdb(conn));
286 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 if (found_objs && *found_objs)
295 retobjs = *found_objs;
296 else
297 {
298 retobjs = retrieve_objects(conn, vacopts, objects, echo);
299 if (found_objs)
300 *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 for (cell = retobjs ? retobjs->head : NULL; cell; cell = cell->next)
308 ntups++;
309
310 if (ntups == 0)
311 {
312 PQfinish(conn);
313 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 if (concurrentCons > ntups)
321 concurrentCons = ntups;
322 if (concurrentCons <= 0)
323 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 if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
331 {
332 initcmd = stage_commands[stage];
333 executeCommand(conn, initcmd, echo);
334 }
335 else
336 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 sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd);
344 ParallelSlotsAdoptConn(sa, conn);
345
346 initPQExpBuffer(&sql);
347
348 cell = retobjs->head;
349 do
350 {
351 const char *tabname = cell->val;
352 ParallelSlot *free_slot;
353
354 if (CancelRequested)
355 {
356 ret = EXIT_FAILURE;
357 goto finish;
358 }
359
360 free_slot = ParallelSlotsGetIdle(sa, NULL);
361 if (!free_slot)
362 {
363 ret = EXIT_FAILURE;
364 goto finish;
365 }
366
367 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 ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
375 run_vacuum_command(free_slot->connection, sql.data,
376 echo, tabname);
377
378 cell = cell->next;
379 } while (cell != NULL);
380
381 if (!ParallelSlotsWaitCompletion(sa))
382 {
383 ret = EXIT_FAILURE;
384 goto finish;
385 }
386
387 /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */
388 if (vacopts->mode == MODE_VACUUM && vacopts->skip_database_stats)
389 {
390 const char *cmd = "VACUUM (ONLY_DATABASE_STATS);";
391 ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL);
392
393 if (!free_slot)
394 {
395 ret = EXIT_FAILURE;
396 goto finish;
397 }
398
399 ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL);
400 run_vacuum_command(free_slot->connection, cmd, echo, NULL);
401
402 if (!ParallelSlotsWaitCompletion(sa))
403 ret = EXIT_FAILURE; /* error already reported by handler */
404 }
405
406finish:
407 ParallelSlotsTerminate(sa);
408 pg_free(sa);
409 termPQExpBuffer(&sql);
410
411 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 */
421static int
422 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 conn = connectMaintenanceDatabase(cparams, progname, echo);
432 result = executeQuery(conn,
433 "SELECT datname FROM pg_database WHERE datallowconn AND datconnlimit <> -2 ORDER BY 1;",
434 echo);
435 PQfinish(conn);
436
437 if (vacopts->mode == MODE_ANALYZE_IN_STAGES)
438 {
439 SimpleStringList **found_objs = NULL;
440
441 if (vacopts->missing_stats_only)
442 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 for (int stage = 0; stage < ANALYZE_NUM_STAGES; stage++)
453 {
454 for (int i = 0; i < PQntuples(result); i++)
455 {
456 int ret;
457
458 cparams->override_dbname = PQgetvalue(result, i, 0);
459 ret = vacuum_one_database(cparams, vacopts, stage,
460 objects,
461 vacopts->missing_stats_only ? &found_objs[i] : NULL,
462 concurrentCons,
463 progname, echo, quiet);
464 if (ret != EXIT_SUCCESS)
465 return ret;
466 }
467 }
468 }
469 else
470 {
471 for (int i = 0; i < PQntuples(result); i++)
472 {
473 int ret;
474
475 cparams->override_dbname = PQgetvalue(result, i, 0);
476 ret = vacuum_one_database(cparams, vacopts,
477 ANALYZE_NO_STAGE,
478 objects,
479 NULL,
480 concurrentCons,
481 progname, echo, quiet);
482 if (ret != EXIT_SUCCESS)
483 return ret;
484 }
485 }
486
487 PQclear(result);
488
489 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 */
504static SimpleStringList *
505 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 SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList));
513 bool objects_listed = false;
514
515 initPQExpBuffer(&catalog_query);
516 for (cell = objects ? objects->head : NULL; cell; cell = cell->next)
517 {
518 char *just_table = NULL;
519 const char *just_columns = NULL;
520
521 if (!objects_listed)
522 {
523 appendPQExpBufferStr(&catalog_query,
524 "WITH listed_objects (object_oid, column_list) AS (\n"
525 " VALUES (");
526 objects_listed = true;
527 }
528 else
529 appendPQExpBufferStr(&catalog_query, ",\n (");
530
531 if (vacopts->objfilter & (OBJFILTER_SCHEMA | OBJFILTER_SCHEMA_EXCLUDE))
532 {
533 appendStringLiteralConn(&catalog_query, cell->val, conn);
534 appendPQExpBufferStr(&catalog_query, "::pg_catalog.regnamespace, ");
535 }
536
537 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 splitTableColumnsSpec(cell->val, PQclientEncoding(conn),
546 &just_table, &just_columns);
547
548 appendStringLiteralConn(&catalog_query, just_table, conn);
549 appendPQExpBufferStr(&catalog_query, "::pg_catalog.regclass, ");
550 }
551
552 if (just_columns && just_columns[0] != '0円')
553 appendStringLiteralConn(&catalog_query, just_columns, conn);
554 else
555 appendPQExpBufferStr(&catalog_query, "NULL");
556
557 appendPQExpBufferStr(&catalog_query, "::pg_catalog.text)");
558
559 pg_free(just_table);
560 }
561
562 /* Finish formatting the CTE */
563 if (objects_listed)
564 appendPQExpBufferStr(&catalog_query, "\n)\n");
565
566 appendPQExpBufferStr(&catalog_query, "SELECT c.relname, ns.nspname");
567
568 if (objects_listed)
569 appendPQExpBufferStr(&catalog_query, ", listed_objects.column_list");
570
571 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 if (objects_listed)
586 {
587 appendPQExpBufferStr(&catalog_query, " LEFT JOIN listed_objects"
588 " ON listed_objects.object_oid"
589 " OPERATOR(pg_catalog.=) ");
590
591 if (vacopts->objfilter & OBJFILTER_TABLE)
592 appendPQExpBufferStr(&catalog_query, "c.oid\n");
593 else
594 appendPQExpBufferStr(&catalog_query, "ns.oid\n");
595 }
596
597 /*
598 * Exclude temporary tables, beginning the WHERE clause.
599 */
600 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 if (objects_listed)
609 {
610 if (vacopts->objfilter & OBJFILTER_SCHEMA_EXCLUDE)
611 appendPQExpBufferStr(&catalog_query,
612 " AND listed_objects.object_oid IS NULL\n");
613 else
614 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 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 if (vacopts->mode == MODE_ANALYZE)
635 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 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 if (vacopts->min_xid_age != 0)
655 {
656 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 if (vacopts->min_mxid_age != 0)
666 {
667 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 if (vacopts->missing_stats_only)
677 {
678 appendPQExpBufferStr(&catalog_query, " AND (\n");
679
680 /* regular stats */
681 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 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 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 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 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 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 appendPQExpBufferStr(&catalog_query, " ORDER BY c.relpages DESC;");
758 executeCommand(conn, "RESET search_path;", echo);
759 res = executeQuery(conn, catalog_query.data, echo);
760 termPQExpBuffer(&catalog_query);
761 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 initPQExpBuffer(&buf);
768 for (int i = 0; i < PQntuples(res); i++)
769 {
770 appendPQExpBufferStr(&buf,
771 fmtQualifiedIdEnc(PQgetvalue(res, i, 1),
772 PQgetvalue(res, i, 0),
773 PQclientEncoding(conn)));
774
775 if (objects_listed && !PQgetisnull(res, i, 2))
776 appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2));
777
778 simple_string_list_append(found_objs, buf.data);
779 resetPQExpBuffer(&buf);
780 }
781 termPQExpBuffer(&buf);
782 PQclear(res);
783
784 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 */
794static void
795 prepare_vacuum_command(PGconn *conn, PQExpBuffer sql,
796 vacuumingOptions *vacopts, const char *table)
797{
798 int serverVersion = PQserverVersion(conn);
799 const char *paren = " (";
800 const char *comma = ", ";
801 const char *sep = paren;
802
803 resetPQExpBuffer(sql);
804
805 if (vacopts->mode == MODE_ANALYZE ||
806 vacopts->mode == MODE_ANALYZE_IN_STAGES)
807 {
808 appendPQExpBufferStr(sql, "ANALYZE");
809
810 /* parenthesized grammar of ANALYZE is supported since v11 */
811 if (serverVersion >= 110000)
812 {
813 if (vacopts->skip_locked)
814 {
815 /* SKIP_LOCKED is supported since v12 */
816 Assert(serverVersion >= 120000);
817 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
818 sep = comma;
819 }
820 if (vacopts->verbose)
821 {
822 appendPQExpBuffer(sql, "%sVERBOSE", sep);
823 sep = comma;
824 }
825 if (vacopts->buffer_usage_limit)
826 {
827 Assert(serverVersion >= 160000);
828 appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
829 vacopts->buffer_usage_limit);
830 sep = comma;
831 }
832 if (sep != paren)
833 appendPQExpBufferChar(sql, ')');
834 }
835 else
836 {
837 if (vacopts->verbose)
838 appendPQExpBufferStr(sql, " VERBOSE");
839 }
840 }
841 else
842 {
843 appendPQExpBufferStr(sql, "VACUUM");
844
845 /* parenthesized grammar of VACUUM is supported since v9.0 */
846 if (serverVersion >= 90000)
847 {
848 if (vacopts->disable_page_skipping)
849 {
850 /* DISABLE_PAGE_SKIPPING is supported since v9.6 */
851 Assert(serverVersion >= 90600);
852 appendPQExpBuffer(sql, "%sDISABLE_PAGE_SKIPPING", sep);
853 sep = comma;
854 }
855 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 appendPQExpBuffer(sql, "%sINDEX_CLEANUP FALSE", sep);
861 sep = comma;
862 }
863 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 appendPQExpBuffer(sql, "%sINDEX_CLEANUP TRUE", sep);
869 sep = comma;
870 }
871 if (!vacopts->do_truncate)
872 {
873 /* TRUNCATE is supported since v12 */
874 Assert(serverVersion >= 120000);
875 appendPQExpBuffer(sql, "%sTRUNCATE FALSE", sep);
876 sep = comma;
877 }
878 if (!vacopts->process_main)
879 {
880 /* PROCESS_MAIN is supported since v16 */
881 Assert(serverVersion >= 160000);
882 appendPQExpBuffer(sql, "%sPROCESS_MAIN FALSE", sep);
883 sep = comma;
884 }
885 if (!vacopts->process_toast)
886 {
887 /* PROCESS_TOAST is supported since v14 */
888 Assert(serverVersion >= 140000);
889 appendPQExpBuffer(sql, "%sPROCESS_TOAST FALSE", sep);
890 sep = comma;
891 }
892 if (vacopts->skip_database_stats)
893 {
894 /* SKIP_DATABASE_STATS is supported since v16 */
895 Assert(serverVersion >= 160000);
896 appendPQExpBuffer(sql, "%sSKIP_DATABASE_STATS", sep);
897 sep = comma;
898 }
899 if (vacopts->skip_locked)
900 {
901 /* SKIP_LOCKED is supported since v12 */
902 Assert(serverVersion >= 120000);
903 appendPQExpBuffer(sql, "%sSKIP_LOCKED", sep);
904 sep = comma;
905 }
906 if (vacopts->full)
907 {
908 appendPQExpBuffer(sql, "%sFULL", sep);
909 sep = comma;
910 }
911 if (vacopts->freeze)
912 {
913 appendPQExpBuffer(sql, "%sFREEZE", sep);
914 sep = comma;
915 }
916 if (vacopts->verbose)
917 {
918 appendPQExpBuffer(sql, "%sVERBOSE", sep);
919 sep = comma;
920 }
921 if (vacopts->and_analyze)
922 {
923 appendPQExpBuffer(sql, "%sANALYZE", sep);
924 sep = comma;
925 }
926 if (vacopts->parallel_workers >= 0)
927 {
928 /* PARALLEL is supported since v13 */
929 Assert(serverVersion >= 130000);
930 appendPQExpBuffer(sql, "%sPARALLEL %d", sep,
931 vacopts->parallel_workers);
932 sep = comma;
933 }
934 if (vacopts->buffer_usage_limit)
935 {
936 Assert(serverVersion >= 160000);
937 appendPQExpBuffer(sql, "%sBUFFER_USAGE_LIMIT '%s'", sep,
938 vacopts->buffer_usage_limit);
939 sep = comma;
940 }
941 if (sep != paren)
942 appendPQExpBufferChar(sql, ')');
943 }
944 else
945 {
946 if (vacopts->full)
947 appendPQExpBufferStr(sql, " FULL");
948 if (vacopts->freeze)
949 appendPQExpBufferStr(sql, " FREEZE");
950 if (vacopts->verbose)
951 appendPQExpBufferStr(sql, " VERBOSE");
952 if (vacopts->and_analyze)
953 appendPQExpBufferStr(sql, " ANALYZE");
954 }
955 }
956
957 appendPQExpBuffer(sql, " %s;", table);
958}
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 */
966static void
967 run_vacuum_command(PGconn *conn, const char *sql, bool echo,
968 const char *table)
969{
970 bool status;
971
972 if (echo)
973 printf("%s\n", sql);
974
975 status = PQsendQuery(conn, sql) == 1;
976
977 if (!status)
978 {
979 if (table)
980 {
981 pg_log_error("vacuuming of table \"%s\" in database \"%s\" failed: %s",
982 table, PQdb(conn), PQerrorMessage(conn));
983 }
984 else
985 {
986 pg_log_error("vacuuming of database \"%s\" failed: %s",
987 PQdb(conn), PQerrorMessage(conn));
988 }
989 }
990}
991
992/*
993 * Returns a newly malloc'd version of 'src' with escaped single quotes and
994 * backslashes.
995 */
996char *
997 escape_quotes(const char *src)
998{
999 char *result = escape_single_quotes_ascii(src);
1000
1001 if (!result)
1002 pg_fatal("out of memory");
1003 return result;
1004}
void splitTableColumnsSpec(const char *spec, int encoding, char **table, const char **columns)
Definition: common.c:33
#define gettext_noop(x)
Definition: c.h:1195
#define CppAsString2(x)
Definition: c.h:418
volatile sig_atomic_t CancelRequested
Definition: cancel.c:59
void setup_cancel_handler(void(*query_cancel_callback)(void))
Definition: cancel.c:183
#define ALWAYS_SECURE_SEARCH_PATH_SQL
Definition: connect.h:25
PGconn * connectMaintenanceDatabase(ConnParams *cparams, const char *progname, bool echo)
Definition: connect_utils.c:134
PGconn * connectDatabase(const ConnParams *cparams, const char *progname, bool echo, bool fail_ok, bool allow_password_reuse)
Definition: connect_utils.c:32
PGresult * executeQuery(PGconn *conn, const char *query)
Definition: connectdb.c:278
_
#define _(x)
Definition: elog.c:91
int PQserverVersion(const PGconn *conn)
Definition: fe-connect.c:7669
char * PQdb(const PGconn *conn)
Definition: fe-connect.c:7513
int PQclientEncoding(const PGconn *conn)
Definition: fe-connect.c:7769
void PQfinish(PGconn *conn)
Definition: fe-connect.c:5305
char * PQerrorMessage(const PGconn *conn)
Definition: fe-connect.c:7679
int PQsendQuery(PGconn *conn, const char *query)
Definition: fe-exec.c:1427
void pg_free(void *ptr)
Definition: fe_memutils.c:105
Assert(PointerIsAligned(start, uint64))
#define comma
Definition: indent_codes.h:48
i
int i
Definition: isn.c:77
#define PQgetvalue
Definition: libpq-be-fe.h:253
#define PQclear
Definition: libpq-be-fe.h:245
#define PQgetisnull
Definition: libpq-be-fe.h:255
#define PQntuples
Definition: libpq-be-fe.h:251
#define pg_log_error(...)
Definition: logging.h:106
const char * progname
Definition: main.c:44
void * palloc0(Size size)
Definition: mcxt.c:1395
stdout
ParallelSlotArray * ParallelSlotsSetup(int numslots, ConnParams *cparams, const char *progname, bool echo, const char *initcmd)
Definition: parallel_slot.c:428
bool ParallelSlotsWaitCompletion(ParallelSlotArray *sa)
Definition: parallel_slot.c:501
bool TableCommandResultHandler(PGresult *res, PGconn *conn, void *context)
Definition: parallel_slot.c:540
ParallelSlot * ParallelSlotsGetIdle(ParallelSlotArray *sa, const char *dbname)
Definition: parallel_slot.c:371
void ParallelSlotsTerminate(ParallelSlotArray *sa)
Definition: parallel_slot.c:479
void ParallelSlotsAdoptConn(ParallelSlotArray *sa, PGconn *conn)
Definition: parallel_slot.c:460
static void ParallelSlotSetHandler(ParallelSlot *slot, ParallelSlotResultHandler handler, void *context)
Definition: parallel_slot.h:47
#define pg_fatal(...)
static void executeCommand(PGconn *conn, const char *query)
Definition: pg_dumpall.c:1780
static const struct lconv_member_info table[]
static char * buf
Definition: pg_test_fsync.c:72
char * escape_single_quotes_ascii(const char *src)
Definition: quotes.c:33
#define printf(...)
Definition: port.h:245
void initPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:90
void resetPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:146
void appendPQExpBuffer(PQExpBuffer str, const char *fmt,...)
Definition: pqexpbuffer.c:265
void appendPQExpBufferChar(PQExpBuffer str, char ch)
Definition: pqexpbuffer.c:378
void appendPQExpBufferStr(PQExpBuffer str, const char *data)
Definition: pqexpbuffer.c:367
void termPQExpBuffer(PQExpBuffer str)
Definition: pqexpbuffer.c:129
#define EXIT_SUCCESS
Definition: settings.h:193
#define EXIT_FAILURE
Definition: settings.h:197
void simple_string_list_append(SimpleStringList *list, const char *val)
Definition: simple_list.c:63
char * dbname
Definition: streamutil.c:49
PGconn * conn
Definition: streamutil.c:52
const char * fmtQualifiedIdEnc(const char *schema, const char *id, int encoding)
Definition: string_utils.c:263
void appendStringLiteralConn(PQExpBuffer buf, const char *str, PGconn *conn)
Definition: string_utils.c:446
char * data
Definition: pqexpbuffer.h:46
PGconn * connection
Definition: parallel_slot.h:23
char val[FLEXIBLE_ARRAY_MEMBER]
Definition: simple_list.h:37
struct SimpleStringListCell * next
Definition: simple_list.h:34
SimpleStringListCell * head
Definition: simple_list.h:42
Definition: pg_backup.h:84
char * override_dbname
Definition: pg_backup.h:93
char * dbname
Definition: pg_backup.h:86
Definition: libpq-int.h:372
Definition: libpq-int.h:165
Definition: preproc-init.c:83
bool force_index_cleanup
Definition: vacuuming.h:47
RunMode mode
Definition: vacuuming.h:34
bool no_index_cleanup
Definition: vacuuming.h:46
bits32 objfilter
Definition: vacuuming.h:35
int min_xid_age
Definition: vacuuming.h:42
int parallel_workers
Definition: vacuuming.h:44
bool disable_page_skipping
Definition: vacuuming.h:40
int min_mxid_age
Definition: vacuuming.h:43
bool skip_locked
Definition: vacuuming.h:41
bool process_toast
Definition: vacuuming.h:50
char * buffer_usage_limit
Definition: vacuuming.h:52
bool do_truncate
Definition: vacuuming.h:48
bool process_main
Definition: vacuuming.h:49
bool verbose
Definition: vacuuming.h:36
bool missing_stats_only
Definition: vacuuming.h:53
bool skip_database_stats
Definition: vacuuming.h:51
bool and_analyze
Definition: vacuuming.h:37
const char * get_user_name_or_exit(const char *progname)
Definition: username.c:74
static SimpleStringList * retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, SimpleStringList *objects, bool echo)
Definition: vacuuming.c:505
static void prepare_vacuum_command(PGconn *conn, PQExpBuffer sql, vacuumingOptions *vacopts, const char *table)
Definition: vacuuming.c:795
static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table)
Definition: vacuuming.c:967
static int vacuum_all_databases(ConnParams *cparams, vacuumingOptions *vacopts, SimpleStringList *objects, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuuming.c:422
int vacuuming_main(ConnParams *cparams, const char *dbname, const char *maintenance_db, vacuumingOptions *vacopts, SimpleStringList *objects, unsigned int tbl_count, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuuming.c:54
char * escape_quotes(const char *src)
Definition: vacuuming.c:997
static int vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet)
Definition: vacuuming.c:159
#define OBJFILTER_ALL_DBS
Definition: vacuuming.h:57
@ MODE_ANALYZE
Definition: vacuuming.h:23
@ MODE_VACUUM
Definition: vacuuming.h:22
@ MODE_ANALYZE_IN_STAGES
Definition: vacuuming.h:24
#define ANALYZE_NUM_STAGES
Definition: vacuuming.h:29
#define OBJFILTER_SCHEMA_EXCLUDE
Definition: vacuuming.h:61
#define OBJFILTER_TABLE
Definition: vacuuming.h:59
#define ANALYZE_NO_STAGE
Definition: vacuuming.h:28
#define OBJFILTER_SCHEMA
Definition: vacuuming.h:60

AltStyle によって変換されたページ (->オリジナル) /