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

Generated by: LCOV version 1.13