LCOV - code coverage report
Current view: top level - src/bin/scripts - vacuumdb.c (source / functions) Hit Total Coverage
Test: PostgreSQL 14devel Lines: 330 413 79.9 %
Date: 2020-11-27 12:05:55 Functions: 6 6 100.0 %
Legend: Lines: hit not hit

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

Generated by: LCOV version 1.13