LCOV - code coverage report
Current view: top level - src/bin/scripts - vacuumdb.c (source / functions) Hit Total Coverage
Test: PostgreSQL 13devel Lines: 274 340 80.6 %
Date: 2019-09-22 08:06:49 Functions: 6 6 100.0 %
Legend: Lines: hit not hit

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

Generated by: LCOV version 1.13