v34-0009-preserve-catalog-lists-across-staged-runs.patch

From 431cc9dc617e4ba314637b48c190e22736509b36 Mon Sep 17 00:00:00 2001
From: Corey Huinker <corey.huinker@gmail.com>
Date: Wed, 6 Nov 2024 21:30:49 -0500
Subject: [PATCH v34 09/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.1