From 975dd88313e88ae8d579b1e0cd68d7944cb6a391 Mon Sep 17 00:00:00 2001 From: Corey Huinker Date: Wed, 6 Nov 2024 21:30:49 -0500 Subject: [PATCH v33 08/11] preserve catalog lists across staged runs --- src/bin/scripts/vacuumdb.c | 113 ++++++++++++++++++++++++++----------- 1 file changed, 80 insertions(+), 33 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 36f4796db0..b13f3c4224 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -68,6 +68,9 @@ static void vacuum_one_database(ConnParams *cparams, int stage, SimpleStringList *objects, int concurrentCons, + PGconn *conn, + SimpleStringList *dbtables, + int ntups, const char *progname, bool echo, bool quiet); static void vacuum_all_databases(ConnParams *cparams, @@ -83,6 +86,14 @@ static void prepare_vacuum_command(PQExpBuffer sql, int serverVersion, static void run_vacuum_command(PGconn *conn, const char *sql, bool echo, const char *table); +static void check_conn_options(PGconn *conn, vacuumingOptions *vacopts); + +static void +print_processing_notice(PGconn *conn, int stage, const char *progname, bool quiet); + +static SimpleStringList * generate_catalog_list(PGconn *conn, vacuumingOptions *vacopts, + SimpleStringList *objects, bool echo, int *ntups); + static void help(const char *progname); void check_objfilter(void); @@ -386,6 +397,11 @@ main(int argc, char *argv[]) } else { + PGconn *conn; + int ntup; + SimpleStringList *found_objects; + int stage; + if (dbname == NULL) { if (getenv("PGDATABASE")) @@ -397,25 +413,37 @@ main(int argc, char *argv[]) } cparams.dbname = dbname; + stage = (analyze_in_stages) ? 0 : ANALYZE_NO_STAGE; + + conn = connectDatabase(&cparams, progname, echo, false, true); + check_conn_options(conn, &vacopts); + print_processing_notice(conn, stage, progname, quiet); + found_objects = generate_catalog_list(conn, &vacopts, &objects, echo, &ntup); if (analyze_in_stages) { - int stage; - for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++) { - vacuum_one_database(&cparams, &vacopts, - stage, + /* the last pass disconnected the conn */ + if (stage > 0) + conn = connectDatabase(&cparams, progname, echo, false, true); + + vacuum_one_database(&cparams, &vacopts, stage, &objects, concurrentCons, + conn, + found_objects, + ntup, progname, echo, quiet); } } else - vacuum_one_database(&cparams, &vacopts, - ANALYZE_NO_STAGE, + vacuum_one_database(&cparams, &vacopts, stage, &objects, concurrentCons, + conn, + found_objects, + ntup, progname, echo, quiet); } @@ -791,16 +819,17 @@ vacuum_one_database(ConnParams *cparams, int stage, SimpleStringList *objects, int concurrentCons, + PGconn *conn, + SimpleStringList *dbtables, + int ntups, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; - PGconn *conn; SimpleStringListCell *cell; ParallelSlotArray *sa; - int ntups; bool failed = false; const char *initcmd; - SimpleStringList *dbtables; + const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", @@ -810,13 +839,6 @@ vacuum_one_database(ConnParams *cparams, Assert(stage == ANALYZE_NO_STAGE || (stage >= 0 && stage < ANALYZE_NUM_STAGES)); - conn = connectDatabase(cparams, progname, echo, false, true); - - check_conn_options(conn, vacopts); - print_processing_notice(conn, stage, progname, quiet); - - dbtables = generate_catalog_list(conn, vacopts, objects, echo, &ntups); - /* * If no rows are returned, there are no matching tables, so we are done. */ @@ -928,7 +950,7 @@ finish: } /* - * Vacuum/analyze all connectable databases. + * Vacuum/analyze all ccparams->override_dbname = PQgetvalue(result, i, 0);onnectable databases. * * In analyze-in-stages mode, we process all databases in one stage before * moving on to the next stage. That ensure minimal stats are available @@ -944,8 +966,13 @@ vacuum_all_databases(ConnParams *cparams, { PGconn *conn; PGresult *result; - int stage; int i; + int stage; + + SimpleStringList **found_objects; + int *num_tuples; + + stage = (analyze_in_stages) ? 0 : ANALYZE_NO_STAGE; conn = connectMaintenanceDatabase(cparams, progname, echo); result = executeQuery(conn, @@ -953,7 +980,33 @@ vacuum_all_databases(ConnParams *cparams, echo); PQfinish(conn); - if (analyze_in_stages) + /* + * connect to each database, check validity of options, + * build the list of found objects per database, + * and run the first/only vacuum stage + */ + found_objects = palloc(PQntuples(result) * sizeof(SimpleStringList *)); + num_tuples = palloc(PQntuples(result) * sizeof (int)); + + for (i = 0; i < PQntuples(result); i++) + { + cparams->override_dbname = PQgetvalue(result, i, 0); + conn = connectDatabase(cparams, progname, echo, false, true); + check_conn_options(conn, vacopts); + print_processing_notice(conn, stage, progname, quiet); + found_objects[i] = generate_catalog_list(conn, vacopts, objects, echo, &num_tuples[i]); + + vacuum_one_database(cparams, vacopts, + stage, + objects, + concurrentCons, + conn, + found_objects[i], + num_tuples[i], + progname, echo, quiet); + } + + if (stage != ANALYZE_NO_STAGE) { /* * When analyzing all databases in stages, we analyze them all in the @@ -963,35 +1016,29 @@ vacuum_all_databases(ConnParams *cparams, * This means we establish several times as many connections, but * that's a secondary consideration. */ - for (stage = 0; stage < ANALYZE_NUM_STAGES; stage++) + for (stage = 1; stage < ANALYZE_NUM_STAGES; stage++) { for (i = 0; i < PQntuples(result); i++) { cparams->override_dbname = PQgetvalue(result, i, 0); + conn = connectDatabase(cparams, progname, echo, false, true); + print_processing_notice(conn, stage, progname, quiet); vacuum_one_database(cparams, vacopts, stage, objects, concurrentCons, + conn, + found_objects[i], + num_tuples[i], progname, echo, quiet); } } } - else - { - for (i = 0; i < PQntuples(result); i++) - { - cparams->override_dbname = PQgetvalue(result, i, 0); - - vacuum_one_database(cparams, vacopts, - ANALYZE_NO_STAGE, - objects, - concurrentCons, - progname, echo, quiet); - } - } PQclear(result); + pfree(found_objects); + pfree(num_tuples); } /* -- 2.47.0