agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v2] Change compute_query_id to an enum GUC. 4+ messages / 2 participants [nested] [flat]
* [PATCH v2] Change compute_query_id to an enum GUC. @ 2021-05-11 07:20 Julien Rouhaud <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Julien Rouhaud @ 2021-05-11 07:20 UTC (permalink / raw) The current approach that requires to explicitly enable compute_query_id on top of configuring pg_stat_statements in shared_preload_libraries has been proven to be at best unfriendly, so switch the GUC to an enum defaulting to "auto", which allows third-party plugins to enable in-core query identifier calculation if they require one. While at it, also change pg_stat_statements_internal() to raise a warning if the configuration looks broken (as in the calling query doesn't have a query_id, compute_query_id is explicitly disabled and there's nothing stored in pg_stat_statements hash). A new queryIdWanted() function is provided to let plugins inform us that require a query identifier, and which will enable query identifier calculation iff it's set to "auto". Note that if that function is called during postmaster startup (typically during process_shared_preload_libraries()) then it will be enabled globally, otherwise it will only be enabled in the session(s) that loaded a plugin calling queryIdWanted(). Author: Julien Rouhaud Discussion: https://postgr.es/m/[email protected] --- .../expected/pg_stat_statements.out | 17 +++++++++ .../pg_stat_statements/pg_stat_statements.c | 21 ++++++++++ .../pg_stat_statements.conf | 1 - .../sql/pg_stat_statements.sql | 6 +++ doc/src/sgml/config.sgml | 9 ++++- doc/src/sgml/pgstatstatements.sgml | 13 +++++-- src/backend/commands/explain.c | 2 +- src/backend/parser/analyze.c | 4 +- src/backend/tcop/postgres.c | 2 +- src/backend/utils/misc/guc.c | 38 ++++++++++++++----- src/backend/utils/misc/postgresql.conf.sample | 2 +- src/backend/utils/misc/queryjumble.c | 15 ++++++++ src/include/utils/guc.h | 1 - src/include/utils/queryjumble.h | 12 ++++++ 14 files changed, 120 insertions(+), 23 deletions(-) diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out index 40b5109b55..b071ffc9d5 100644 --- a/contrib/pg_stat_statements/expected/pg_stat_statements.out +++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out @@ -1067,4 +1067,21 @@ SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; 2 (1 row) +-- Check that pg_stat_statements() will complain if the configuration appears +-- to be broken. +SET compute_query_id = off; +SELECT pg_stat_statements_reset(); + pg_stat_statements_reset +-------------------------- + +(1 row) + +SELECT count(*) FROM pg_stat_statements; +WARNING: Query identifier calculation seems to be disabled +HINT: If you don't want to use a third-party module to compute query identifiers, you may want to enable compute_query_id + count +------- + 0 +(1 row) + DROP EXTENSION pg_stat_statements; diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c index f42f07622e..8f5285e09d 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.c +++ b/contrib/pg_stat_statements/pg_stat_statements.c @@ -369,6 +369,12 @@ _PG_init(void) if (!process_shared_preload_libraries_in_progress) return; + /* + * Informat the postmaster that we want to enable query_id calculation if + * compute_query_id is set to auto. + */ + queryIdWanted(); + /* * Define (or redefine) custom GUC variables. */ @@ -1616,6 +1622,21 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo, */ LWLockAcquire(pgss->lock, LW_SHARED); + /* + * If no query_id has been computed for the calling query and there is + * no entries stored, then there's likely a configuration error that caller + * may not be aware of so point it out. + */ + if (pgstat_get_my_query_id() == UINT64CONST(0) && + compute_query_id == COMPUTE_QUERY_ID_OFF && + hash_get_num_entries(pgss_hash) == 0) + ereport(WARNING, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("Query identifier calculation seems to be disabled"), + errhint("If you don't want to use a third-party module to" + " compute query identifiers, you may want to enable" + " compute_query_id"))); + if (showtext) { /* diff --git a/contrib/pg_stat_statements/pg_stat_statements.conf b/contrib/pg_stat_statements/pg_stat_statements.conf index e47b26040f..13346e2807 100644 --- a/contrib/pg_stat_statements/pg_stat_statements.conf +++ b/contrib/pg_stat_statements/pg_stat_statements.conf @@ -1,2 +1 @@ shared_preload_libraries = 'pg_stat_statements' -compute_query_id = on diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql index bc3b6493e6..827a8e3d18 100644 --- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql +++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql @@ -437,4 +437,10 @@ SELECT ( SELECT COUNT(*) FROM pg_stat_statements WHERE query LIKE '%SELECT GROUPING%'; +-- Check that pg_stat_statements() will complain if the configuration appears +-- to be broken. +SET compute_query_id = off; +SELECT pg_stat_statements_reset(); +SELECT count(*) FROM pg_stat_statements; + DROP EXTENSION pg_stat_statements; diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 45bd1f1b7e..60d8b24f5e 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7627,7 +7627,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; <variablelist> <varlistentry id="guc-compute-query-id" xreflabel="compute_query_id"> - <term><varname>compute_query_id</varname> (<type>boolean</type>) + <term><varname>compute_query_id</varname> (<type>enum</type>) <indexterm> <primary><varname>compute_query_id</varname> configuration parameter</primary> </indexterm> @@ -7643,7 +7643,12 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; identifier to be computed. Note that an external module can alternatively be used if the in-core query identifier computation method is not acceptable. In this case, in-core computation - must be disabled. The default is <literal>off</literal>. + must be always disabled. + Valid values are <literal>off</literal> (always disabled), + <literal>on</literal> (always enabled) and <literal>auto</literal>, + which let modules such as <xref linkend="pgstatstatements"/> + automatically enable it. + The default is <literal>auto</literal>. </para> <note> <para> diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml index bc2b6038ee..acfb134797 100644 --- a/doc/src/sgml/pgstatstatements.sgml +++ b/doc/src/sgml/pgstatstatements.sgml @@ -22,10 +22,15 @@ <para> The module will not track statistics unless query - identifiers are calculated. This can be done by enabling <xref - linkend="guc-compute-query-id"/> or using a third-party module that - computes its own query identifiers. Note that all statistics tracked - by this module must be reset if the query identifier method is changed. + identifiers are calculated. This is done by automatically when this + extension is configured in <literal>shared_preload_libraries</literal> and + <xref linkend="guc-compute-query-id"/> is set to <literal>auto</literal> + (which is the default value), or always if <xref + linkend="guc-compute-query-id"/> is set to <literal>on</literal>. + You must set <xref linkend="guc-compute-query-id"/> to <literal>off</literal> + if you want to use a third-party module that computes its own query + identifiers. Note that all statistics tracked by this module must be + reset if the query identifier method is changed. </para> <para> diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 8ab7bca866..9d384234b0 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -245,7 +245,7 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, es->summary = (summary_set) ? es->summary : es->analyze; query = castNode(Query, stmt->query); - if (compute_query_id) + if (compute_query_id == COMPUTE_QUERY_ID_ON) jstate = JumbleQuery(query, pstate->p_sourcetext); if (post_parse_analyze_hook) diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index e415bc3df0..1108f4133a 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -124,7 +124,7 @@ parse_analyze(RawStmt *parseTree, const char *sourceText, query = transformTopLevelStmt(pstate, parseTree); - if (compute_query_id) + if (compute_query_id == COMPUTE_QUERY_ID_ON) jstate = JumbleQuery(query, sourceText); if (post_parse_analyze_hook) @@ -163,7 +163,7 @@ parse_analyze_varparams(RawStmt *parseTree, const char *sourceText, /* make sure all is well with parameter types */ check_variable_parameters(pstate, query); - if (compute_query_id) + if (compute_query_id == COMPUTE_QUERY_ID_ON) jstate = JumbleQuery(query, sourceText); if (post_parse_analyze_hook) diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index 2d6d145ecc..e4b76af557 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -704,7 +704,7 @@ pg_analyze_and_rewrite_params(RawStmt *parsetree, query = transformTopLevelStmt(pstate, parsetree); - if (compute_query_id) + if (compute_query_id == COMPUTE_QUERY_ID_ON) jstate = JumbleQuery(query, query_string); if (post_parse_analyze_hook) diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index 0a180341c2..835b322c29 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -101,6 +101,7 @@ #include "utils/plancache.h" #include "utils/portal.h" #include "utils/ps_status.h" +#include "utils/queryjumble.h" #include "utils/rls.h" #include "utils/snapmgr.h" #include "utils/tzparser.h" @@ -402,6 +403,23 @@ static const struct config_enum_entry backslash_quote_options[] = { {NULL, 0, false} }; +/* + * Although only "on", "off", and "auto" are documented, we accept all the + * likely variants of "on" and "off". + */ +static const struct config_enum_entry compute_query_id_options[] = { + {"auto", COMPUTE_QUERY_ID_AUTO, false}, + {"on", COMPUTE_QUERY_ID_ON, false}, + {"off", COMPUTE_QUERY_ID_OFF, false}, + {"true", COMPUTE_QUERY_ID_ON, true}, + {"false", COMPUTE_QUERY_ID_OFF, true}, + {"yes", COMPUTE_QUERY_ID_ON, true}, + {"no", COMPUTE_QUERY_ID_OFF, true}, + {"1", COMPUTE_QUERY_ID_ON, true}, + {"0", COMPUTE_QUERY_ID_OFF, true}, + {NULL, 0, false} +}; + /* * Although only "on", "off", and "partition" are documented, we * accept all the likely variants of "on" and "off". @@ -534,7 +552,6 @@ extern const struct config_enum_entry dynamic_shared_memory_options[]; /* * GUC option variables that are exported from this module */ -bool compute_query_id = false; bool log_duration = false; bool Debug_print_plan = false; bool Debug_print_parse = false; @@ -1441,15 +1458,6 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL, NULL }, - { - {"compute_query_id", PGC_SUSET, STATS_MONITORING, - gettext_noop("Compute query identifiers."), - NULL - }, - &compute_query_id, - false, - NULL, NULL, NULL - }, { {"log_parser_stats", PGC_SUSET, STATS_MONITORING, gettext_noop("Writes parser performance statistics to the server log."), @@ -4618,6 +4626,16 @@ static struct config_enum ConfigureNamesEnum[] = NULL, NULL, NULL }, + { + {"compute_query_id", PGC_SUSET, STATS_MONITORING, + gettext_noop("Compute query identifiers."), + NULL + }, + &compute_query_id, + COMPUTE_QUERY_ID_AUTO, compute_query_id_options, + NULL, NULL, NULL + }, + { {"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Enables the planner to use constraints to optimize queries."), diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample index efde01ee56..6e36e4c2ef 100644 --- a/src/backend/utils/misc/postgresql.conf.sample +++ b/src/backend/utils/misc/postgresql.conf.sample @@ -604,7 +604,7 @@ # - Monitoring - -#compute_query_id = off +#compute_query_id = auto #log_statement_stats = off #log_parser_stats = off #log_planner_stats = off diff --git a/src/backend/utils/misc/queryjumble.c b/src/backend/utils/misc/queryjumble.c index 1bb9fe20ea..4189fdfa53 100644 --- a/src/backend/utils/misc/queryjumble.c +++ b/src/backend/utils/misc/queryjumble.c @@ -39,6 +39,9 @@ #define JUMBLE_SIZE 1024 /* query serialization buffer size */ +/* GUC parameters */ +int compute_query_id = COMPUTE_QUERY_ID_AUTO; + static uint64 compute_utility_query_id(const char *str, int query_location, int query_len); static void AppendJumble(JumbleState *jstate, const unsigned char *item, Size size); @@ -131,6 +134,18 @@ JumbleQuery(Query *query, const char *querytext) return jstate; } +/* + * Enables compute_query_id if it's set to auto. + * + * Third-party plugins can use that function to inform the core that they + * require a query identifier to be computed. + */ +void queryIdWanted(void) +{ + if (compute_query_id == COMPUTE_QUERY_ID_AUTO) + compute_query_id = COMPUTE_QUERY_ID_ON; +} + /* * Compute a query identifier for the given utility query string. */ diff --git a/src/include/utils/guc.h b/src/include/utils/guc.h index 24a5d9d3fb..a7c3a4958e 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -247,7 +247,6 @@ extern bool log_btree_build_stats; extern PGDLLIMPORT bool check_function_bodies; extern bool session_auth_is_superuser; -extern bool compute_query_id; extern bool log_duration; extern int log_parameter_max_length; extern int log_parameter_max_length_on_error; diff --git a/src/include/utils/queryjumble.h b/src/include/utils/queryjumble.h index 83ba7339fa..578ed50a7e 100644 --- a/src/include/utils/queryjumble.h +++ b/src/include/utils/queryjumble.h @@ -52,7 +52,19 @@ typedef struct JumbleState int highest_extern_param_id; } JumbleState; +/* Values for the compute_query_id GUC */ +typedef enum +{ + COMPUTE_QUERY_ID_OFF, + COMPUTE_QUERY_ID_ON, + COMPUTE_QUERY_ID_AUTO +} ComputeQueryIdType; + +/* GUC parameters */ +extern int compute_query_id; + const char *CleanQuerytext(const char *query, int *location, int *len); JumbleState *JumbleQuery(Query *query, const char *querytext); +void queryIdWanted(void); #endif /* QUERYJUMBLE_H */ -- 2.31.1 --4ntemsbjuduex3w7-- ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v6 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. @ 2025-03-11 16:18 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2025-03-11 16:18 UTC (permalink / raw) Presently, each call to vacuum_one_database() performs a catalog query to retrieve the list of tables to process. A proposed follow-up commit would add a "missing only" feature to --analyze-in-stages, which requires us to save the results of the catalog query (since tables without statistics would have them after the first stage). This commit adds this ability via a new parameter for vacuum_one_database() that specifies either a previously-retrieved list to process or a place to store the results of the catalog query for later use. Note that this commit does not make use of this new parameter for anything. That is left as a future exercise. The trade-offs of this approach are increased memory usage and less responsiveness to concurrent catalog changes in later stages, neither of which is expected to bother anyone. Author: Corey Huinker <[email protected]> Co-authored-by: Nathan Bossart <[email protected]> Reviewed-by: John Naylor <[email protected]> Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan --- src/bin/scripts/vacuumdb.c | 330 ++++++++++++++++++++++--------------- 1 file changed, 194 insertions(+), 136 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 982bf070be6..e28f82a0eba 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -62,10 +62,16 @@ typedef enum static VacObjFilter objfilter = OBJFILTER_NONE; +static SimpleStringList *retrieve_objects(PGconn *conn, + vacuumingOptions *vacopts, + SimpleStringList *objects, + bool echo); + static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet); @@ -405,7 +411,7 @@ main(int argc, char *argv[]) { vacuum_one_database(&cparams, &vacopts, stage, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -413,7 +419,7 @@ main(int argc, char *argv[]) else vacuum_one_database(&cparams, &vacopts, ANALYZE_NO_STAGE, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -461,8 +467,36 @@ escape_quotes(const char *src) /* * vacuum_one_database * - * Process tables in the given database. If the 'objects' list is empty, - * process all tables in the database. + * Process tables in the given database. + * + * There are two ways to specify the list of objects to process: + * + * 1) The "found_objs" parameter is a double pointer to a fully qualified list + * of objects to process, as returned by a previous call to + * vacuum_one_database(). + * + * a) If both "found_objs" (the double pointer) and "*found_objs" (the + * once-dereferenced double pointer) are not NULL, this list takes + * priority, and anything specified in "objects" is ignored. + * + * b) If "found_objs" (the double pointer) is not NULL but "*found_objs" + * (the once-dereferenced double pointer) _is_ NULL, the "objects" + * parameter takes priority, and the results of the catalog query + * described in (2) are stored in "found_objs". + * + * c) If "found_objs" (the double pointer) is NULL, the "objects" + * parameter again takes priority, and the results of the catalog query + * are not saved. + * + * 2) The "objects" parameter is a user-specified list of objects to process. + * When (1b) or (1c) applies, this function performs a catalog query to + * retrieve a fully qualified list of objects to process, as described + * below. + * + * a) If "objects" is not NULL, the catalog query gathers only the objects + * listed in "objects". + * + * b) If "objects" is NULL, all tables in the database are gathered. * * Note that this function is only concerned with running exactly one stage * when in analyze-in-stages mode; caller must iterate on us if necessary. @@ -475,22 +509,18 @@ vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; - PQExpBufferData buf; - PQExpBufferData catalog_query; - PGresult *res; PGconn *conn; SimpleStringListCell *cell; ParallelSlotArray *sa; - SimpleStringList dbtables = {NULL, NULL}; - int i; - int ntups; + int ntups = 0; bool failed = false; - bool objects_listed = false; const char *initcmd; + SimpleStringList *ret = NULL; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", @@ -599,19 +629,155 @@ vacuum_one_database(ConnParams *cparams, } /* - * Prepare the list of tables to process by querying the catalogs. - * - * Since we execute the constructed query with the default search_path - * (which could be unsafe), everything in this query MUST be fully - * qualified. - * - * First, build a WITH clause for the catalog query if any tables were - * specified, with a set of values made of relation names and their - * optional set of columns. This is used to match any provided column - * lists with the generated qualified identifiers and to filter for the - * tables provided via --table. If a listed table does not exist, the - * catalog query will fail. + * If the caller provided the results of a previous catalog query, just + * use that. Otherwise, run the catalog query ourselves and set the + * return variable if provided. + */ + if (found_objs && *found_objs) + ret = *found_objs; + else + { + ret = retrieve_objects(conn, vacopts, objects, echo); + if (found_objs) + *found_objs = ret; + } + + /* + * Count the number of objects in the catalog query result. If there are + * none, we are done. + */ + for (cell = ret ? ret->head : NULL; cell; cell = cell->next) + ntups++; + + if (ntups == 0) + { + PQfinish(conn); + return; + } + + /* + * Ensure concurrentCons is sane. If there are more connections than + * vacuumable relations, we don't need to use them all. */ + if (concurrentCons > ntups) + concurrentCons = ntups; + if (concurrentCons <= 0) + concurrentCons = 1; + + /* + * All slots need to be prepared to run the appropriate analyze stage, if + * caller requested that mode. We have to prepare the initial connection + * ourselves before setting up the slots. + */ + if (stage == ANALYZE_NO_STAGE) + initcmd = NULL; + else + { + initcmd = stage_commands[stage]; + executeCommand(conn, initcmd, echo); + } + + /* + * Setup the database connections. We reuse the connection we already have + * for the first slot. If not in parallel mode, the first slot in the + * array contains the connection. + */ + sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); + ParallelSlotsAdoptConn(sa, conn); + + initPQExpBuffer(&sql); + + cell = ret->head; + do + { + const char *tabname = cell->val; + ParallelSlot *free_slot; + + if (CancelRequested) + { + failed = true; + goto finish; + } + + free_slot = ParallelSlotsGetIdle(sa, NULL); + if (!free_slot) + { + failed = true; + goto finish; + } + + prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), + vacopts, tabname); + + /* + * Execute the vacuum. All errors are handled in processQueryResult + * through ParallelSlotsGetIdle. + */ + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, sql.data, + echo, tabname); + + cell = cell->next; + } while (cell != NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + { + failed = true; + goto finish; + } + + /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ + if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) + { + const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; + ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); + + if (!free_slot) + { + failed = true; + goto finish; + } + + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, cmd, echo, NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + failed = true; + } + +finish: + ParallelSlotsTerminate(sa); + pg_free(sa); + + termPQExpBuffer(&sql); + + if (failed) + exit(1); +} + +/* + * Prepare the list of tables to process by querying the catalogs. + * + * Since we execute the constructed query with the default search_path (which + * could be unsafe), everything in this query MUST be fully qualified. + * + * First, build a WITH clause for the catalog query if any tables were + * specified, with a set of values made of relation names and their optional + * set of columns. This is used to match any provided column lists with the + * generated qualified identifiers and to filter for the tables provided via + * --table. If a listed table does not exist, the catalog query will fail. + */ +static SimpleStringList * +retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, + SimpleStringList *objects, bool echo) +{ + PQExpBufferData buf; + PQExpBufferData catalog_query; + PGresult *res; + SimpleStringListCell *cell; + SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList)); + bool objects_listed = false; + initPQExpBuffer(&catalog_query); for (cell = objects ? objects->head : NULL; cell; cell = cell->next) { @@ -765,23 +931,12 @@ vacuum_one_database(ConnParams *cparams, termPQExpBuffer(&catalog_query); PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo)); - /* - * If no rows are returned, there are no matching tables, so we are done. - */ - ntups = PQntuples(res); - if (ntups == 0) - { - PQclear(res); - PQfinish(conn); - return; - } - /* * Build qualified identifiers for each table, including the column list * if given. */ initPQExpBuffer(&buf); - for (i = 0; i < ntups; i++) + for (int i = 0; i < PQntuples(res); i++) { appendPQExpBufferStr(&buf, fmtQualifiedIdEnc(PQgetvalue(res, i, 1), @@ -791,110 +946,13 @@ vacuum_one_database(ConnParams *cparams, if (objects_listed && !PQgetisnull(res, i, 2)) appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2)); - simple_string_list_append(&dbtables, buf.data); + simple_string_list_append(found_objs, buf.data); resetPQExpBuffer(&buf); } termPQExpBuffer(&buf); PQclear(res); - /* - * Ensure concurrentCons is sane. If there are more connections than - * vacuumable relations, we don't need to use them all. - */ - if (concurrentCons > ntups) - concurrentCons = ntups; - if (concurrentCons <= 0) - concurrentCons = 1; - - /* - * All slots need to be prepared to run the appropriate analyze stage, if - * caller requested that mode. We have to prepare the initial connection - * ourselves before setting up the slots. - */ - if (stage == ANALYZE_NO_STAGE) - initcmd = NULL; - else - { - initcmd = stage_commands[stage]; - executeCommand(conn, initcmd, echo); - } - - /* - * Setup the database connections. We reuse the connection we already have - * for the first slot. If not in parallel mode, the first slot in the - * array contains the connection. - */ - sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); - ParallelSlotsAdoptConn(sa, conn); - - initPQExpBuffer(&sql); - - cell = dbtables.head; - do - { - const char *tabname = cell->val; - ParallelSlot *free_slot; - - if (CancelRequested) - { - failed = true; - goto finish; - } - - free_slot = ParallelSlotsGetIdle(sa, NULL); - if (!free_slot) - { - failed = true; - goto finish; - } - - prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), - vacopts, tabname); - - /* - * Execute the vacuum. All errors are handled in processQueryResult - * through ParallelSlotsGetIdle. - */ - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, sql.data, - echo, tabname); - - cell = cell->next; - } while (cell != NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - { - failed = true; - goto finish; - } - - /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ - if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) - { - const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; - ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); - - if (!free_slot) - { - failed = true; - goto finish; - } - - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, cmd, echo, NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - failed = true; - } - -finish: - ParallelSlotsTerminate(sa); - pg_free(sa); - - termPQExpBuffer(&sql); - - if (failed) - exit(1); + return found_objs; } /* @@ -941,7 +999,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, stage, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } @@ -955,7 +1013,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, ANALYZE_NO_STAGE, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } -- 2.39.5 (Apple Git-154) --B27oui4/QxrBRsZE Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v6-0002-vacuumdb-Add-option-for-analyzing-only-relations-.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v5 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. @ 2025-03-11 16:18 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2025-03-11 16:18 UTC (permalink / raw) Presently, each call to vacuum_one_database() performs a catalog query to retrieve the list of tables to process. A proposed follow-up commit would add a "missing only" feature to --analyze-in-stages, which requires us to save the results of the catalog query (since tables without statistics would have them after the first stage). This commit adds this ability via a new parameter for vacuum_one_database() that specifies either a previously-retrieved list to process or a place to store the results of the catalog query for later use. Note that this commit does not make use of this new parameter for anything. That is left as a future exercise. The trade-offs of this approach are increased memory usage and less responsiveness to concurrent catalog changes in later stages, neither of which is expected to bother anyone. Author: Corey Huinker <[email protected]> Co-authored-by: Nathan Bossart <[email protected]> Reviewed-by: John Naylor <[email protected]> Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan --- src/bin/scripts/vacuumdb.c | 330 ++++++++++++++++++++++--------------- 1 file changed, 194 insertions(+), 136 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 982bf070be6..e28f82a0eba 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -62,10 +62,16 @@ typedef enum static VacObjFilter objfilter = OBJFILTER_NONE; +static SimpleStringList *retrieve_objects(PGconn *conn, + vacuumingOptions *vacopts, + SimpleStringList *objects, + bool echo); + static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet); @@ -405,7 +411,7 @@ main(int argc, char *argv[]) { vacuum_one_database(&cparams, &vacopts, stage, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -413,7 +419,7 @@ main(int argc, char *argv[]) else vacuum_one_database(&cparams, &vacopts, ANALYZE_NO_STAGE, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -461,8 +467,36 @@ escape_quotes(const char *src) /* * vacuum_one_database * - * Process tables in the given database. If the 'objects' list is empty, - * process all tables in the database. + * Process tables in the given database. + * + * There are two ways to specify the list of objects to process: + * + * 1) The "found_objs" parameter is a double pointer to a fully qualified list + * of objects to process, as returned by a previous call to + * vacuum_one_database(). + * + * a) If both "found_objs" (the double pointer) and "*found_objs" (the + * once-dereferenced double pointer) are not NULL, this list takes + * priority, and anything specified in "objects" is ignored. + * + * b) If "found_objs" (the double pointer) is not NULL but "*found_objs" + * (the once-dereferenced double pointer) _is_ NULL, the "objects" + * parameter takes priority, and the results of the catalog query + * described in (2) are stored in "found_objs". + * + * c) If "found_objs" (the double pointer) is NULL, the "objects" + * parameter again takes priority, and the results of the catalog query + * are not saved. + * + * 2) The "objects" parameter is a user-specified list of objects to process. + * When (1b) or (1c) applies, this function performs a catalog query to + * retrieve a fully qualified list of objects to process, as described + * below. + * + * a) If "objects" is not NULL, the catalog query gathers only the objects + * listed in "objects". + * + * b) If "objects" is NULL, all tables in the database are gathered. * * Note that this function is only concerned with running exactly one stage * when in analyze-in-stages mode; caller must iterate on us if necessary. @@ -475,22 +509,18 @@ vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; - PQExpBufferData buf; - PQExpBufferData catalog_query; - PGresult *res; PGconn *conn; SimpleStringListCell *cell; ParallelSlotArray *sa; - SimpleStringList dbtables = {NULL, NULL}; - int i; - int ntups; + int ntups = 0; bool failed = false; - bool objects_listed = false; const char *initcmd; + SimpleStringList *ret = NULL; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", @@ -599,19 +629,155 @@ vacuum_one_database(ConnParams *cparams, } /* - * Prepare the list of tables to process by querying the catalogs. - * - * Since we execute the constructed query with the default search_path - * (which could be unsafe), everything in this query MUST be fully - * qualified. - * - * First, build a WITH clause for the catalog query if any tables were - * specified, with a set of values made of relation names and their - * optional set of columns. This is used to match any provided column - * lists with the generated qualified identifiers and to filter for the - * tables provided via --table. If a listed table does not exist, the - * catalog query will fail. + * If the caller provided the results of a previous catalog query, just + * use that. Otherwise, run the catalog query ourselves and set the + * return variable if provided. + */ + if (found_objs && *found_objs) + ret = *found_objs; + else + { + ret = retrieve_objects(conn, vacopts, objects, echo); + if (found_objs) + *found_objs = ret; + } + + /* + * Count the number of objects in the catalog query result. If there are + * none, we are done. + */ + for (cell = ret ? ret->head : NULL; cell; cell = cell->next) + ntups++; + + if (ntups == 0) + { + PQfinish(conn); + return; + } + + /* + * Ensure concurrentCons is sane. If there are more connections than + * vacuumable relations, we don't need to use them all. */ + if (concurrentCons > ntups) + concurrentCons = ntups; + if (concurrentCons <= 0) + concurrentCons = 1; + + /* + * All slots need to be prepared to run the appropriate analyze stage, if + * caller requested that mode. We have to prepare the initial connection + * ourselves before setting up the slots. + */ + if (stage == ANALYZE_NO_STAGE) + initcmd = NULL; + else + { + initcmd = stage_commands[stage]; + executeCommand(conn, initcmd, echo); + } + + /* + * Setup the database connections. We reuse the connection we already have + * for the first slot. If not in parallel mode, the first slot in the + * array contains the connection. + */ + sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); + ParallelSlotsAdoptConn(sa, conn); + + initPQExpBuffer(&sql); + + cell = ret->head; + do + { + const char *tabname = cell->val; + ParallelSlot *free_slot; + + if (CancelRequested) + { + failed = true; + goto finish; + } + + free_slot = ParallelSlotsGetIdle(sa, NULL); + if (!free_slot) + { + failed = true; + goto finish; + } + + prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), + vacopts, tabname); + + /* + * Execute the vacuum. All errors are handled in processQueryResult + * through ParallelSlotsGetIdle. + */ + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, sql.data, + echo, tabname); + + cell = cell->next; + } while (cell != NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + { + failed = true; + goto finish; + } + + /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ + if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) + { + const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; + ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); + + if (!free_slot) + { + failed = true; + goto finish; + } + + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, cmd, echo, NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + failed = true; + } + +finish: + ParallelSlotsTerminate(sa); + pg_free(sa); + + termPQExpBuffer(&sql); + + if (failed) + exit(1); +} + +/* + * Prepare the list of tables to process by querying the catalogs. + * + * Since we execute the constructed query with the default search_path (which + * could be unsafe), everything in this query MUST be fully qualified. + * + * First, build a WITH clause for the catalog query if any tables were + * specified, with a set of values made of relation names and their optional + * set of columns. This is used to match any provided column lists with the + * generated qualified identifiers and to filter for the tables provided via + * --table. If a listed table does not exist, the catalog query will fail. + */ +static SimpleStringList * +retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, + SimpleStringList *objects, bool echo) +{ + PQExpBufferData buf; + PQExpBufferData catalog_query; + PGresult *res; + SimpleStringListCell *cell; + SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList)); + bool objects_listed = false; + initPQExpBuffer(&catalog_query); for (cell = objects ? objects->head : NULL; cell; cell = cell->next) { @@ -765,23 +931,12 @@ vacuum_one_database(ConnParams *cparams, termPQExpBuffer(&catalog_query); PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo)); - /* - * If no rows are returned, there are no matching tables, so we are done. - */ - ntups = PQntuples(res); - if (ntups == 0) - { - PQclear(res); - PQfinish(conn); - return; - } - /* * Build qualified identifiers for each table, including the column list * if given. */ initPQExpBuffer(&buf); - for (i = 0; i < ntups; i++) + for (int i = 0; i < PQntuples(res); i++) { appendPQExpBufferStr(&buf, fmtQualifiedIdEnc(PQgetvalue(res, i, 1), @@ -791,110 +946,13 @@ vacuum_one_database(ConnParams *cparams, if (objects_listed && !PQgetisnull(res, i, 2)) appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2)); - simple_string_list_append(&dbtables, buf.data); + simple_string_list_append(found_objs, buf.data); resetPQExpBuffer(&buf); } termPQExpBuffer(&buf); PQclear(res); - /* - * Ensure concurrentCons is sane. If there are more connections than - * vacuumable relations, we don't need to use them all. - */ - if (concurrentCons > ntups) - concurrentCons = ntups; - if (concurrentCons <= 0) - concurrentCons = 1; - - /* - * All slots need to be prepared to run the appropriate analyze stage, if - * caller requested that mode. We have to prepare the initial connection - * ourselves before setting up the slots. - */ - if (stage == ANALYZE_NO_STAGE) - initcmd = NULL; - else - { - initcmd = stage_commands[stage]; - executeCommand(conn, initcmd, echo); - } - - /* - * Setup the database connections. We reuse the connection we already have - * for the first slot. If not in parallel mode, the first slot in the - * array contains the connection. - */ - sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); - ParallelSlotsAdoptConn(sa, conn); - - initPQExpBuffer(&sql); - - cell = dbtables.head; - do - { - const char *tabname = cell->val; - ParallelSlot *free_slot; - - if (CancelRequested) - { - failed = true; - goto finish; - } - - free_slot = ParallelSlotsGetIdle(sa, NULL); - if (!free_slot) - { - failed = true; - goto finish; - } - - prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), - vacopts, tabname); - - /* - * Execute the vacuum. All errors are handled in processQueryResult - * through ParallelSlotsGetIdle. - */ - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, sql.data, - echo, tabname); - - cell = cell->next; - } while (cell != NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - { - failed = true; - goto finish; - } - - /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ - if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) - { - const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; - ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); - - if (!free_slot) - { - failed = true; - goto finish; - } - - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, cmd, echo, NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - failed = true; - } - -finish: - ParallelSlotsTerminate(sa); - pg_free(sa); - - termPQExpBuffer(&sql); - - if (failed) - exit(1); + return found_objs; } /* @@ -941,7 +999,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, stage, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } @@ -955,7 +1013,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, ANALYZE_NO_STAGE, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } -- 2.39.5 (Apple Git-154) --zUTpaz3RFkyl1aol Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v5-0002-vacuumdb-Add-option-for-analyzing-only-relations-.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v7 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. @ 2025-03-17 21:11 Nathan Bossart <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Nathan Bossart @ 2025-03-17 21:11 UTC (permalink / raw) Presently, each call to vacuum_one_database() performs a catalog query to retrieve the list of tables to process. A proposed follow-up commit would add a "missing only" feature to --analyze-in-stages, which requires us to save the results of the catalog query (since tables without statistics would have them after the first stage). This commit adds this ability via a new parameter for vacuum_one_database() that specifies either a previously-retrieved list to process or a place to return the results of the catalog query. Note that this commit does not make use of this new parameter for anything. That is left for a follow-up commit. Author: Corey Huinker <[email protected]> Co-authored-by: Nathan Bossart <[email protected]> Reviewed-by: John Naylor <[email protected]> Discussion: https://postgr.es/m/Z5O1bpcwDrMgyrYy%40nathan --- src/bin/scripts/vacuumdb.c | 330 ++++++++++++++++++++++--------------- 1 file changed, 194 insertions(+), 136 deletions(-) diff --git a/src/bin/scripts/vacuumdb.c b/src/bin/scripts/vacuumdb.c index 982bf070be6..e28f82a0eba 100644 --- a/src/bin/scripts/vacuumdb.c +++ b/src/bin/scripts/vacuumdb.c @@ -62,10 +62,16 @@ typedef enum static VacObjFilter objfilter = OBJFILTER_NONE; +static SimpleStringList *retrieve_objects(PGconn *conn, + vacuumingOptions *vacopts, + SimpleStringList *objects, + bool echo); + static void vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet); @@ -405,7 +411,7 @@ main(int argc, char *argv[]) { vacuum_one_database(&cparams, &vacopts, stage, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -413,7 +419,7 @@ main(int argc, char *argv[]) else vacuum_one_database(&cparams, &vacopts, ANALYZE_NO_STAGE, - &objects, + &objects, NULL, concurrentCons, progname, echo, quiet); } @@ -461,8 +467,36 @@ escape_quotes(const char *src) /* * vacuum_one_database * - * Process tables in the given database. If the 'objects' list is empty, - * process all tables in the database. + * Process tables in the given database. + * + * There are two ways to specify the list of objects to process: + * + * 1) The "found_objs" parameter is a double pointer to a fully qualified list + * of objects to process, as returned by a previous call to + * vacuum_one_database(). + * + * a) If both "found_objs" (the double pointer) and "*found_objs" (the + * once-dereferenced double pointer) are not NULL, this list takes + * priority, and anything specified in "objects" is ignored. + * + * b) If "found_objs" (the double pointer) is not NULL but "*found_objs" + * (the once-dereferenced double pointer) _is_ NULL, the "objects" + * parameter takes priority, and the results of the catalog query + * described in (2) are stored in "found_objs". + * + * c) If "found_objs" (the double pointer) is NULL, the "objects" + * parameter again takes priority, and the results of the catalog query + * are not saved. + * + * 2) The "objects" parameter is a user-specified list of objects to process. + * When (1b) or (1c) applies, this function performs a catalog query to + * retrieve a fully qualified list of objects to process, as described + * below. + * + * a) If "objects" is not NULL, the catalog query gathers only the objects + * listed in "objects". + * + * b) If "objects" is NULL, all tables in the database are gathered. * * Note that this function is only concerned with running exactly one stage * when in analyze-in-stages mode; caller must iterate on us if necessary. @@ -475,22 +509,18 @@ vacuum_one_database(ConnParams *cparams, vacuumingOptions *vacopts, int stage, SimpleStringList *objects, + SimpleStringList **found_objs, int concurrentCons, const char *progname, bool echo, bool quiet) { PQExpBufferData sql; - PQExpBufferData buf; - PQExpBufferData catalog_query; - PGresult *res; PGconn *conn; SimpleStringListCell *cell; ParallelSlotArray *sa; - SimpleStringList dbtables = {NULL, NULL}; - int i; - int ntups; + int ntups = 0; bool failed = false; - bool objects_listed = false; const char *initcmd; + SimpleStringList *ret = NULL; const char *stage_commands[] = { "SET default_statistics_target=1; SET vacuum_cost_delay=0;", "SET default_statistics_target=10; RESET vacuum_cost_delay;", @@ -599,19 +629,155 @@ vacuum_one_database(ConnParams *cparams, } /* - * Prepare the list of tables to process by querying the catalogs. - * - * Since we execute the constructed query with the default search_path - * (which could be unsafe), everything in this query MUST be fully - * qualified. - * - * First, build a WITH clause for the catalog query if any tables were - * specified, with a set of values made of relation names and their - * optional set of columns. This is used to match any provided column - * lists with the generated qualified identifiers and to filter for the - * tables provided via --table. If a listed table does not exist, the - * catalog query will fail. + * If the caller provided the results of a previous catalog query, just + * use that. Otherwise, run the catalog query ourselves and set the + * return variable if provided. + */ + if (found_objs && *found_objs) + ret = *found_objs; + else + { + ret = retrieve_objects(conn, vacopts, objects, echo); + if (found_objs) + *found_objs = ret; + } + + /* + * Count the number of objects in the catalog query result. If there are + * none, we are done. + */ + for (cell = ret ? ret->head : NULL; cell; cell = cell->next) + ntups++; + + if (ntups == 0) + { + PQfinish(conn); + return; + } + + /* + * Ensure concurrentCons is sane. If there are more connections than + * vacuumable relations, we don't need to use them all. */ + if (concurrentCons > ntups) + concurrentCons = ntups; + if (concurrentCons <= 0) + concurrentCons = 1; + + /* + * All slots need to be prepared to run the appropriate analyze stage, if + * caller requested that mode. We have to prepare the initial connection + * ourselves before setting up the slots. + */ + if (stage == ANALYZE_NO_STAGE) + initcmd = NULL; + else + { + initcmd = stage_commands[stage]; + executeCommand(conn, initcmd, echo); + } + + /* + * Setup the database connections. We reuse the connection we already have + * for the first slot. If not in parallel mode, the first slot in the + * array contains the connection. + */ + sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); + ParallelSlotsAdoptConn(sa, conn); + + initPQExpBuffer(&sql); + + cell = ret->head; + do + { + const char *tabname = cell->val; + ParallelSlot *free_slot; + + if (CancelRequested) + { + failed = true; + goto finish; + } + + free_slot = ParallelSlotsGetIdle(sa, NULL); + if (!free_slot) + { + failed = true; + goto finish; + } + + prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), + vacopts, tabname); + + /* + * Execute the vacuum. All errors are handled in processQueryResult + * through ParallelSlotsGetIdle. + */ + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, sql.data, + echo, tabname); + + cell = cell->next; + } while (cell != NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + { + failed = true; + goto finish; + } + + /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ + if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) + { + const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; + ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); + + if (!free_slot) + { + failed = true; + goto finish; + } + + ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); + run_vacuum_command(free_slot->connection, cmd, echo, NULL); + + if (!ParallelSlotsWaitCompletion(sa)) + failed = true; + } + +finish: + ParallelSlotsTerminate(sa); + pg_free(sa); + + termPQExpBuffer(&sql); + + if (failed) + exit(1); +} + +/* + * Prepare the list of tables to process by querying the catalogs. + * + * Since we execute the constructed query with the default search_path (which + * could be unsafe), everything in this query MUST be fully qualified. + * + * First, build a WITH clause for the catalog query if any tables were + * specified, with a set of values made of relation names and their optional + * set of columns. This is used to match any provided column lists with the + * generated qualified identifiers and to filter for the tables provided via + * --table. If a listed table does not exist, the catalog query will fail. + */ +static SimpleStringList * +retrieve_objects(PGconn *conn, vacuumingOptions *vacopts, + SimpleStringList *objects, bool echo) +{ + PQExpBufferData buf; + PQExpBufferData catalog_query; + PGresult *res; + SimpleStringListCell *cell; + SimpleStringList *found_objs = palloc0(sizeof(SimpleStringList)); + bool objects_listed = false; + initPQExpBuffer(&catalog_query); for (cell = objects ? objects->head : NULL; cell; cell = cell->next) { @@ -765,23 +931,12 @@ vacuum_one_database(ConnParams *cparams, termPQExpBuffer(&catalog_query); PQclear(executeQuery(conn, ALWAYS_SECURE_SEARCH_PATH_SQL, echo)); - /* - * If no rows are returned, there are no matching tables, so we are done. - */ - ntups = PQntuples(res); - if (ntups == 0) - { - PQclear(res); - PQfinish(conn); - return; - } - /* * Build qualified identifiers for each table, including the column list * if given. */ initPQExpBuffer(&buf); - for (i = 0; i < ntups; i++) + for (int i = 0; i < PQntuples(res); i++) { appendPQExpBufferStr(&buf, fmtQualifiedIdEnc(PQgetvalue(res, i, 1), @@ -791,110 +946,13 @@ vacuum_one_database(ConnParams *cparams, if (objects_listed && !PQgetisnull(res, i, 2)) appendPQExpBufferStr(&buf, PQgetvalue(res, i, 2)); - simple_string_list_append(&dbtables, buf.data); + simple_string_list_append(found_objs, buf.data); resetPQExpBuffer(&buf); } termPQExpBuffer(&buf); PQclear(res); - /* - * Ensure concurrentCons is sane. If there are more connections than - * vacuumable relations, we don't need to use them all. - */ - if (concurrentCons > ntups) - concurrentCons = ntups; - if (concurrentCons <= 0) - concurrentCons = 1; - - /* - * All slots need to be prepared to run the appropriate analyze stage, if - * caller requested that mode. We have to prepare the initial connection - * ourselves before setting up the slots. - */ - if (stage == ANALYZE_NO_STAGE) - initcmd = NULL; - else - { - initcmd = stage_commands[stage]; - executeCommand(conn, initcmd, echo); - } - - /* - * Setup the database connections. We reuse the connection we already have - * for the first slot. If not in parallel mode, the first slot in the - * array contains the connection. - */ - sa = ParallelSlotsSetup(concurrentCons, cparams, progname, echo, initcmd); - ParallelSlotsAdoptConn(sa, conn); - - initPQExpBuffer(&sql); - - cell = dbtables.head; - do - { - const char *tabname = cell->val; - ParallelSlot *free_slot; - - if (CancelRequested) - { - failed = true; - goto finish; - } - - free_slot = ParallelSlotsGetIdle(sa, NULL); - if (!free_slot) - { - failed = true; - goto finish; - } - - prepare_vacuum_command(&sql, PQserverVersion(free_slot->connection), - vacopts, tabname); - - /* - * Execute the vacuum. All errors are handled in processQueryResult - * through ParallelSlotsGetIdle. - */ - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, sql.data, - echo, tabname); - - cell = cell->next; - } while (cell != NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - { - failed = true; - goto finish; - } - - /* If we used SKIP_DATABASE_STATS, mop up with ONLY_DATABASE_STATS */ - if (vacopts->skip_database_stats && stage == ANALYZE_NO_STAGE) - { - const char *cmd = "VACUUM (ONLY_DATABASE_STATS);"; - ParallelSlot *free_slot = ParallelSlotsGetIdle(sa, NULL); - - if (!free_slot) - { - failed = true; - goto finish; - } - - ParallelSlotSetHandler(free_slot, TableCommandResultHandler, NULL); - run_vacuum_command(free_slot->connection, cmd, echo, NULL); - - if (!ParallelSlotsWaitCompletion(sa)) - failed = true; - } - -finish: - ParallelSlotsTerminate(sa); - pg_free(sa); - - termPQExpBuffer(&sql); - - if (failed) - exit(1); + return found_objs; } /* @@ -941,7 +999,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, stage, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } @@ -955,7 +1013,7 @@ vacuum_all_databases(ConnParams *cparams, vacuum_one_database(cparams, vacopts, ANALYZE_NO_STAGE, - objects, + objects, NULL, concurrentCons, progname, echo, quiet); } -- 2.39.5 (Apple Git-154) --GMqUTp9yliU3ROhG Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="v7-0002-vacuumdb-Add-option-for-analyzing-only-relations-.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-03-17 21:11 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-05-11 07:20 [PATCH v2] Change compute_query_id to an enum GUC. Julien Rouhaud <[email protected]> 2025-03-11 16:18 [PATCH v6 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. Nathan Bossart <[email protected]> 2025-03-11 16:18 [PATCH v5 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. Nathan Bossart <[email protected]> 2025-03-17 21:11 [PATCH v7 1/3] vacuumdb: Teach vacuum_one_database() to reuse query results. Nathan Bossart <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox