Okay, here is a new patch set that aims to actually fix the issues, not just remove the TOAST reloptions. I followed roughly the approach I originally suggested in my first post: autovacuum merges the relopts, and VACUUM passes them to the TOAST table when recursing. As previously mentioned, vacuuming a TOAST table directly isn't fixed, but I think that's okay. Our main supported way to VACUUM a TOAST table is to use "VACUUM (PROCESS_MAIN false) main_table".
Something else this patch makes worse is that we remain oblivious to concurrent storage parameter changes on the main table. That is, if someone changes a relopt during a long-running vacuum on the main table, we might use a stale relopt value when we process the TOAST table. To fix that, I suspect we'd need to do more lookups, which I was hoping to avoid. But this doesn't seem like a pressing issue, and AFAICT this stuff has been broken for a very long time, so IMHO it's not worth the additional effort. -- nathan
>From 9ae7499234c20796f3846952d33419c567847370 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 8 Jun 2026 14:35:34 -0500 Subject: [PATCH v5 1/4] Remove extract_autovac_opts(). extract_autovac_opts() returned a palloc'd copy of only the AutoVacOpts portion of a relation's reloptions. Upcoming work needs the rest of the StdRdOptions as well, so the callers must keep the whole struct around. Remove the helper and have the callers obtain reloptions from extractRelOptions() directly. av_relation now caches a StdRdOptions instead of an AutoVacOpts, and relation_needs_vacanalyze() takes a StdRdOptions and extracts the autovacuum portion itself. This is preparatory refactoring with no change in behavior. --- src/backend/postmaster/autovacuum.c | 122 ++++++++++------------------ 1 file changed, 44 insertions(+), 78 deletions(-) diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index a5a8db2ff88..203a146b1c0 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -202,8 +202,7 @@ typedef struct av_relation Oid ar_toastrelid; /* hash key - must be first */ Oid ar_relid; bool ar_hasrelopts; - AutoVacOpts ar_reloptions; /* copy of AutoVacOpts from the main table's - * reloptions, or NULL if none */ + StdRdOptions ar_reloptions; /* copy of main table's reloptions */ } av_relation; /* struct to keep track of tables to vacuum and/or analyze, after rechecking */ @@ -381,7 +380,7 @@ static void FreeWorkerInfo(int code, Datum arg); static autovac_table *table_recheck_autovac(Oid relid, HTAB *table_toast_map, TupleDesc pg_class_desc, int effective_multixact_freeze_max_age); -static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, +static void relation_needs_vacanalyze(Oid relid, StdRdOptions *relopts, Form_pg_class classForm, int effective_multixact_freeze_max_age, int elevel, @@ -390,8 +389,6 @@ static void relation_needs_vacanalyze(Oid relid, AutoVacOpts *relopts, static void autovacuum_do_vac_analyze(autovac_table *tab, BufferAccessStrategy bstrategy); -static AutoVacOpts *extract_autovac_opts(HeapTuple tup, - TupleDesc pg_class_desc); static void perform_work_item(AutoVacuumWorkItem *workitem); static void autovac_report_activity(autovac_table *tab); static void autovac_report_workitem(AutoVacuumWorkItem *workitem, @@ -2035,7 +2032,7 @@ do_autovacuum(void) while ((tuple = heap_getnext(relScan, ForwardScanDirection)) != NULL) { Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); - AutoVacOpts *relopts; + StdRdOptions *relopts; Oid relid; bool dovacuum; bool doanalyze; @@ -2074,7 +2071,7 @@ do_autovacuum(void) } /* Fetch reloptions and the pgstat entry for this table */ - relopts = extract_autovac_opts(tuple, pg_class_desc); + relopts = (StdRdOptions *) extractRelOptions(tuple, pg_class_desc, NULL); /* Check if it needs vacuum or analyze */ relation_needs_vacanalyze(relid, relopts, classForm, @@ -2116,7 +2113,7 @@ do_autovacuum(void) { hentry->ar_hasrelopts = true; memcpy(&hentry->ar_reloptions, relopts, - sizeof(AutoVacOpts)); + sizeof(StdRdOptions)); } } } @@ -2139,7 +2136,7 @@ do_autovacuum(void) { Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple); Oid relid; - AutoVacOpts *relopts; + StdRdOptions *relopts; bool free_relopts = false; bool dovacuum; bool doanalyze; @@ -2158,7 +2155,7 @@ do_autovacuum(void) * fetch reloptions -- if this toast table does not have them, try the * main rel */ - relopts = extract_autovac_opts(tuple, pg_class_desc); + relopts = (StdRdOptions *) extractRelOptions(tuple, pg_class_desc, NULL); if (relopts) free_relopts = true; else @@ -2774,39 +2771,6 @@ deleted2: pfree(cur_relname); } -/* - * extract_autovac_opts - * - * Given a relation's pg_class tuple, return a palloc'd copy of the - * AutoVacOpts portion of reloptions, if set; otherwise, return NULL. - * - * Note: callers do not have a relation lock on the table at this point, - * so the table could have been dropped, and its catalog rows gone, after - * we acquired the pg_class row. If pg_class had a TOAST table, this would - * be a risk; fortunately, it doesn't. - */ -static AutoVacOpts * -extract_autovac_opts(HeapTuple tup, TupleDesc pg_class_desc) -{ - bytea *relopts; - AutoVacOpts *av; - - Assert(((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_RELATION || - ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_MATVIEW || - ((Form_pg_class) GETSTRUCT(tup))->relkind == RELKIND_TOASTVALUE); - - relopts = extractRelOptions(tup, pg_class_desc, NULL); - if (relopts == NULL) - return NULL; - - av = palloc_object(AutoVacOpts); - memcpy(av, &(((StdRdOptions *) relopts)->autovacuum), sizeof(AutoVacOpts)); - pfree(relopts); - - return av; -} - - /* * table_recheck_autovac * @@ -2826,8 +2790,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, bool doanalyze; autovac_table *tab = NULL; bool wraparound; - AutoVacOpts *avopts; - bool free_avopts = false; + StdRdOptions *relopts; + bool free_relopts = false; AutoVacuumScores scores; /* fetch the relation's relcache entry */ @@ -2840,9 +2804,9 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * Get the applicable reloptions. If it is a TOAST table, try to get the * main table reloptions if the toast table itself doesn't have. */ - avopts = extract_autovac_opts(classTup, pg_class_desc); - if (avopts) - free_avopts = true; + relopts = (StdRdOptions *) extractRelOptions(classTup, pg_class_desc, NULL); + if (relopts) + free_relopts = true; else if (classForm->relkind == RELKIND_TOASTVALUE && table_toast_map != NULL) { @@ -2851,10 +2815,10 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); if (found && hentry->ar_hasrelopts) - avopts = &hentry->ar_reloptions; + relopts = &hentry->ar_reloptions; } - relation_needs_vacanalyze(relid, avopts, classForm, + relation_needs_vacanalyze(relid, relopts, classForm, effective_multixact_freeze_max_age, DEBUG3, &dovacuum, &doanalyze, &wraparound, @@ -2869,6 +2833,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, int multixact_freeze_table_age; int log_vacuum_min_duration; int log_analyze_min_duration; + AutoVacOpts *avopts = (relopts ? &relopts->autovacuum : NULL); /* * Calculate the vacuum cost parameters and the freeze ages. If there @@ -2980,8 +2945,8 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, avopts->vacuum_cost_delay >= 0)); } - if (free_avopts) - pfree(avopts); + if (free_relopts) + pfree(relopts); heap_freetuple(classTup); return tab; } @@ -2993,7 +2958,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, * "dovacuum" and "doanalyze", respectively. Also return whether the vacuum is * being forced because of Xid or multixact wraparound. * - * relopts is a pointer to the AutoVacOpts options (either for itself in the + * relopts is a pointer to the StdRdOptions options (either for itself in the * case of a plain table, or for either itself or its parent table in the case * of a TOAST table), NULL if none. * @@ -3067,7 +3032,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, */ static void relation_needs_vacanalyze(Oid relid, - AutoVacOpts *relopts, + StdRdOptions *relopts, Form_pg_class classForm, int effective_multixact_freeze_max_age, int elevel, @@ -3081,6 +3046,7 @@ relation_needs_vacanalyze(Oid relid, bool force_vacuum; bool av_enabled; bool may_free = false; + AutoVacOpts *avopts = (relopts ? &relopts->autovacuum : NULL); /* constants from reloptions or GUC variables */ int vac_base_thresh, @@ -3132,45 +3098,45 @@ relation_needs_vacanalyze(Oid relid, */ /* -1 in autovac setting means use plain vacuum_scale_factor */ - vac_scale_factor = (relopts && relopts->vacuum_scale_factor >= 0) - ? relopts->vacuum_scale_factor + vac_scale_factor = (avopts && avopts->vacuum_scale_factor >= 0) + ? avopts->vacuum_scale_factor : autovacuum_vac_scale; - vac_base_thresh = (relopts && relopts->vacuum_threshold >= 0) - ? relopts->vacuum_threshold + vac_base_thresh = (avopts && avopts->vacuum_threshold >= 0) + ? avopts->vacuum_threshold : autovacuum_vac_thresh; /* -1 is used to disable max threshold */ - vac_max_thresh = (relopts && relopts->vacuum_max_threshold >= -1) - ? relopts->vacuum_max_threshold + vac_max_thresh = (avopts && avopts->vacuum_max_threshold >= -1) + ? avopts->vacuum_max_threshold : autovacuum_vac_max_thresh; - vac_ins_scale_factor = (relopts && relopts->vacuum_ins_scale_factor >= 0) - ? relopts->vacuum_ins_scale_factor + vac_ins_scale_factor = (avopts && avopts->vacuum_ins_scale_factor >= 0) + ? avopts->vacuum_ins_scale_factor : autovacuum_vac_ins_scale; /* -1 is used to disable insert vacuums */ - vac_ins_base_thresh = (relopts && relopts->vacuum_ins_threshold >= -1) - ? relopts->vacuum_ins_threshold + vac_ins_base_thresh = (avopts && avopts->vacuum_ins_threshold >= -1) + ? avopts->vacuum_ins_threshold : autovacuum_vac_ins_thresh; - anl_scale_factor = (relopts && relopts->analyze_scale_factor >= 0) - ? relopts->analyze_scale_factor + anl_scale_factor = (avopts && avopts->analyze_scale_factor >= 0) + ? avopts->analyze_scale_factor : autovacuum_anl_scale; - anl_base_thresh = (relopts && relopts->analyze_threshold >= 0) - ? relopts->analyze_threshold + anl_base_thresh = (avopts && avopts->analyze_threshold >= 0) + ? avopts->analyze_threshold : autovacuum_anl_thresh; - freeze_max_age = (relopts && relopts->freeze_max_age >= 0) - ? Min(relopts->freeze_max_age, autovacuum_freeze_max_age) + freeze_max_age = (avopts && avopts->freeze_max_age >= 0) + ? Min(avopts->freeze_max_age, autovacuum_freeze_max_age) : autovacuum_freeze_max_age; - multixact_freeze_max_age = (relopts && relopts->multixact_freeze_max_age >= 0) - ? Min(relopts->multixact_freeze_max_age, effective_multixact_freeze_max_age) + multixact_freeze_max_age = (avopts && avopts->multixact_freeze_max_age >= 0) + ? Min(avopts->multixact_freeze_max_age, effective_multixact_freeze_max_age) : effective_multixact_freeze_max_age; - av_enabled = (relopts ? relopts->enabled : true); + av_enabled = (avopts ? avopts->enabled : true); av_enabled &= AutoVacuumingActive(); relfrozenxid = classForm->relfrozenxid; @@ -3661,7 +3627,7 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL) { Form_pg_class form = (Form_pg_class) GETSTRUCT(tup); - AutoVacOpts *avopts; + StdRdOptions *relopts; bool dovacuum; bool doanalyze; bool wraparound; @@ -3677,14 +3643,14 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) if (form->relpersistence == RELPERSISTENCE_TEMP) continue; - avopts = extract_autovac_opts(tup, RelationGetDescr(rel)); - relation_needs_vacanalyze(form->oid, avopts, form, + relopts = (StdRdOptions *) extractRelOptions(tup, RelationGetDescr(rel), NULL); + relation_needs_vacanalyze(form->oid, relopts, form, effective_multixact_freeze_max_age, LOG_NEVER, &dovacuum, &doanalyze, &wraparound, &scores); - if (avopts) - pfree(avopts); + if (relopts) + pfree(relopts); vals[0] = ObjectIdGetDatum(form->oid); vals[1] = Float8GetDatum(scores.max); -- 2.50.1 (Apple Git-155)
>From 5f9a8cfb16bc1d83119d7273d6ba9bf9d3630c71 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 8 Jun 2026 14:50:36 -0500 Subject: [PATCH v5 2/4] Make autovacuum_enabled a ternary reloption. This commit reimplements autovacuum_enabled as a ternary, using the support added in commit 4d6a66f675 and following the example of vacuum_truncate. This changes only the internal representation: an unset value still behaves as enabled, and the option accepts the same input as before. This is preparatory work for a follow-up commit that will make use of the new "unset" state. --- src/backend/access/common/reloptions.c | 19 +++++++++---------- src/backend/catalog/index.c | 3 ++- src/backend/postmaster/autovacuum.c | 2 +- src/include/utils/rel.h | 2 +- 4 files changed, 13 insertions(+), 13 deletions(-) diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 3e832c3797e..79834126f2f 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -107,15 +107,6 @@ static relopt_bool boolRelOpts[] = }, false }, - { - { - "autovacuum_enabled", - "Enables autovacuum in this relation", - RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, - ShareUpdateExclusiveLock - }, - true - }, { { "user_catalog_table", @@ -168,6 +159,14 @@ static relopt_bool boolRelOpts[] = static relopt_ternary ternaryRelOpts[] = { + { + { + "autovacuum_enabled", + "Enables autovacuum in this relation", + RELOPT_KIND_HEAP | RELOPT_KIND_TOAST, + ShareUpdateExclusiveLock + } + }, { { "vacuum_truncate", @@ -1976,7 +1975,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind) { static const relopt_parse_elt tab[] = { {"fillfactor", RELOPT_TYPE_INT, offsetof(StdRdOptions, fillfactor)}, - {"autovacuum_enabled", RELOPT_TYPE_BOOL, + {"autovacuum_enabled", RELOPT_TYPE_TERNARY, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, enabled)}, {"autovacuum_parallel_workers", RELOPT_TYPE_INT, offsetof(StdRdOptions, autovacuum) + offsetof(AutoVacOpts, autovacuum_parallel_workers)}, diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 9407c357f27..c9f32728902 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -2873,7 +2873,8 @@ index_update_stats(Relation rel, { StdRdOptions *options = (StdRdOptions *) rel->rd_options; - if (options != NULL && !options->autovacuum.enabled) + if (options != NULL && + options->autovacuum.enabled == PG_TERNARY_FALSE) update_stats = false; } else diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index 203a146b1c0..aa2aca8fc4b 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -3136,7 +3136,7 @@ relation_needs_vacanalyze(Oid relid, ? Min(avopts->multixact_freeze_max_age, effective_multixact_freeze_max_age) : effective_multixact_freeze_max_age; - av_enabled = (avopts ? avopts->enabled : true); + av_enabled = (avopts ? avopts->enabled != PG_TERNARY_FALSE : true); av_enabled &= AutoVacuumingActive(); relfrozenxid = classForm->relfrozenxid; diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index fa07ebf8ff7..f0824b6899a 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -310,7 +310,7 @@ typedef struct ForeignKeyCacheInfo /* autovacuum-related reloptions. */ typedef struct AutoVacOpts { - bool enabled; + pg_ternary enabled; int autovacuum_parallel_workers; int vacuum_threshold; -- 2.50.1 (Apple Git-155)
>From c4540d163b6b901d409416a7c55dabb174778978 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Mon, 8 Jun 2026 15:27:58 -0500 Subject: [PATCH v5 3/4] Add an "unset" value for vacuum_index_cleanup. This commit adds a new value to StdRdOptIndexCleanup to distinguish whether it is explicitly set, similar to ViewOptCheckOption's VIEW_OPTION_CHECK_OPTION_NOT_SET. This changes only the internal representation; an unset value still defaults to AUTO, and the option accepts the same input as before. This is preparatory work for a follow-up commit that will make use of the new "unset" state. --- src/backend/access/common/reloptions.c | 3 ++- src/backend/commands/vacuum.c | 23 ++++++++++++++--------- src/include/utils/rel.h | 1 + 3 files changed, 17 insertions(+), 10 deletions(-) diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c index 79834126f2f..58eb72e2339 100644 --- a/src/backend/access/common/reloptions.c +++ b/src/backend/access/common/reloptions.c @@ -517,6 +517,7 @@ static relopt_real realRelOpts[] = /* values from StdRdOptIndexCleanup */ static relopt_enum_elt_def StdRdOptIndexCleanupValues[] = { + /* no value for NOT_SET */ {"auto", STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO}, {"on", STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON}, {"off", STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF}, @@ -557,7 +558,7 @@ static relopt_enum enumRelOpts[] = ShareUpdateExclusiveLock }, StdRdOptIndexCleanupValues, - STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO, + STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET, gettext_noop("Valid values are \"on\", \"off\", and \"auto\".") }, { diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a4abb29cf64..4ee1f913b64 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -2191,20 +2191,25 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, StdRdOptIndexCleanup vacuum_index_cleanup; if (rel->rd_options == NULL) - vacuum_index_cleanup = STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO; + vacuum_index_cleanup = STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET; else vacuum_index_cleanup = ((StdRdOptions *) rel->rd_options)->vacuum_index_cleanup; - if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO) - params.index_cleanup = VACOPTVALUE_AUTO; - else if (vacuum_index_cleanup == STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON) - params.index_cleanup = VACOPTVALUE_ENABLED; - else + switch (vacuum_index_cleanup) { - Assert(vacuum_index_cleanup == - STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF); - params.index_cleanup = VACOPTVALUE_DISABLED; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON: + params.index_cleanup = VACOPTVALUE_ENABLED; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF: + params.index_cleanup = VACOPTVALUE_DISABLED; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO: + params.index_cleanup = VACOPTVALUE_AUTO; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET: + params.index_cleanup = VACOPTVALUE_AUTO; + break; } } diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index f0824b6899a..f1b96b1099d 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -338,6 +338,7 @@ typedef enum StdRdOptIndexCleanup STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO = 0, STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF, STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON, + STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET, } StdRdOptIndexCleanup; typedef struct StdRdOptions -- 2.50.1 (Apple Git-155)
>From c7f6f3ddd622e9936a64aaa26de4ffadf8506405 Mon Sep 17 00:00:00 2001 From: Nathan Bossart <[email protected]> Date: Tue, 9 Jun 2026 11:53:12 -0500 Subject: [PATCH v5 4/4] Fix VACUUM and autovacuum handling of TOAST storage parameters. Per the documentation for CREATE TABLE: If a table parameter value is set and the equivalent toast. parameter is not, the TOAST table will use the table's parameter value. Unfortunately, current reality does not match this description. Neither VACUUM nor autovacuum consults the main table's non-autovacuum storage parameters, and autovacuum only consults the main table's autovacuum-related storage parameters if the TOAST table lacks any. One silver lining is that all currently-supported TOAST storage parameters are related to vacuum, whose code paths already access the main table's parameters. This means that our solution needn't involve more lookups; we just need to propagate them correctly. To fix, this commit teaches autovacuum to combine the TOAST storage parameters with the main table's (with the toast.* ones winning if both are set), and it teaches VACUUM to send down the main table's parameters when recursing to a TOAST table. This doesn't fix VACUUM against a TOAST table directly (e.g., VACUUM pg_toast.pg_toast_5432), but that's probably okay because it's not the main supported way to vacuum a TOAST table (see VACUUM's PROCESS_MAIN and PROCESS_TOAST options). An existing shortcoming that this patch only makes worse is that autovacuum/VACUUM remain oblivious to concurrent storage parameter changes on the main table. That is, the main table's parameters may be captured long before its TOAST table is processed, and a user may very well have altered the settings in the meantime. Fixing that would likely require additional pg_class lookups, and it's not clear if it's worth the trouble. While this is a bug fix, it's too intrusive for back-patching, but the issue seems to have gone unnoticed for a very long time, anyway. --- src/backend/commands/vacuum.c | 36 +++- src/backend/postmaster/autovacuum.c | 187 ++++++++++++++++-- src/include/commands/vacuum.h | 8 + src/include/utils/rel.h | 3 + .../injection_points/expected/vacuum.out | 11 ++ .../modules/injection_points/sql/vacuum.sql | 8 + 6 files changed, 234 insertions(+), 19 deletions(-) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index 4ee1f913b64..a2f77b349b2 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -187,6 +187,9 @@ ExecVacuum(ParseState *pstate, VacuumStmt *vacstmt, bool isTopLevel) /* Will be set later if we recurse to a TOAST table. */ params.toast_parent = InvalidOid; + params.main_index_cleanup = VACOPTVALUE_UNSPECIFIED; + params.main_truncate = VACOPTVALUE_UNSPECIFIED; + params.main_max_eager_freeze_failure_rate = -1.0; /* * Set this to an invalid value so it is clear whether or not a @@ -2184,7 +2187,8 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, /* * Set index_cleanup option based on index_cleanup reloption if it wasn't - * specified in VACUUM command, or when running in an autovacuum worker + * specified in VACUUM command, or when running in an autovacuum worker. A + * TOAST table with no setting of its own inherits the main table's value. */ if (params.index_cleanup == VACOPTVALUE_UNSPECIFIED) { @@ -2200,15 +2204,21 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, { case STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON: params.index_cleanup = VACOPTVALUE_ENABLED; + toast_vacuum_params.main_index_cleanup = VACOPTVALUE_ENABLED; break; case STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF: params.index_cleanup = VACOPTVALUE_DISABLED; + toast_vacuum_params.main_index_cleanup = VACOPTVALUE_DISABLED; break; case STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO: params.index_cleanup = VACOPTVALUE_AUTO; + toast_vacuum_params.main_index_cleanup = VACOPTVALUE_AUTO; break; case STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET: - params.index_cleanup = VACOPTVALUE_AUTO; + if (params.main_index_cleanup != VACOPTVALUE_UNSPECIFIED) + params.index_cleanup = params.main_index_cleanup; + else + params.index_cleanup = VACOPTVALUE_AUTO; break; } } @@ -2224,16 +2234,26 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, /* * Check if the vacuum_max_eager_freeze_failure_rate table storage - * parameter was specified. This overrides the GUC value. + * parameter was specified. This overrides the GUC value. A TOAST table + * with no setting of its own inherits the main table's value. */ if (rel->rd_options != NULL && ((StdRdOptions *) rel->rd_options)->vacuum_max_eager_freeze_failure_rate >= 0) + { params.max_eager_freeze_failure_rate = ((StdRdOptions *) rel->rd_options)->vacuum_max_eager_freeze_failure_rate; + toast_vacuum_params.main_max_eager_freeze_failure_rate = + ((StdRdOptions *) rel->rd_options)->vacuum_max_eager_freeze_failure_rate; + } + else if (params.main_max_eager_freeze_failure_rate >= 0.0) + params.max_eager_freeze_failure_rate = + params.main_max_eager_freeze_failure_rate; /* * Set truncate option based on truncate reloption or GUC if it wasn't - * specified in VACUUM command, or when running in an autovacuum worker + * specified in VACUUM command, or when running in an autovacuum worker. A + * TOAST table with no setting of its own inherits the main table's value + * before falling back to the GUC. */ if (params.truncate == VACOPTVALUE_UNSPECIFIED) { @@ -2242,10 +2262,18 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params, if (opts && opts->vacuum_truncate != PG_TERNARY_UNSET) { if (opts->vacuum_truncate == PG_TERNARY_TRUE) + { params.truncate = VACOPTVALUE_ENABLED; + toast_vacuum_params.main_truncate = VACOPTVALUE_ENABLED; + } else + { params.truncate = VACOPTVALUE_DISABLED; + toast_vacuum_params.main_truncate = VACOPTVALUE_DISABLED; + } } + else if (params.main_truncate != VACOPTVALUE_UNSPECIFIED) + params.truncate = params.main_truncate; else if (vacuum_truncate) params.truncate = VACOPTVALUE_ENABLED; else diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c index aa2aca8fc4b..50c7f3d171d 100644 --- a/src/backend/postmaster/autovacuum.c +++ b/src/backend/postmaster/autovacuum.c @@ -396,6 +396,8 @@ static void autovac_report_workitem(AutoVacuumWorkItem *workitem, static void avl_sigusr2_handler(SIGNAL_ARGS); static bool av_worker_available(void); static void check_av_worker_gucs(void); +static StdRdOptions *merge_autovac_opts(StdRdOptions *toast_opts, + StdRdOptions *main_opts); @@ -2142,6 +2144,8 @@ do_autovacuum(void) bool doanalyze; bool wraparound; AutoVacuumScores scores; + av_relation *hentry; + bool found; /* * We cannot safely process other backends' temp tables, so skip 'em. @@ -2152,21 +2156,19 @@ do_autovacuum(void) relid = classForm->oid; /* - * fetch reloptions -- if this toast table does not have them, try the - * main rel + * fetch reloptions -- merge any unset options from the main rel + * + * Note that we don't bother merging the non-autovacuum relopts here + * because they do not impact our choice of whether to process the + * table. */ relopts = (StdRdOptions *) extractRelOptions(tuple, pg_class_desc, NULL); if (relopts) free_relopts = true; - else - { - av_relation *hentry; - bool found; - hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); - if (found && hentry->ar_hasrelopts) - relopts = &hentry->ar_reloptions; - } + hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); + if (found && hentry->ar_hasrelopts) + relopts = merge_autovac_opts(relopts, &hentry->ar_reloptions); relation_needs_vacanalyze(relid, relopts, classForm, effective_multixact_freeze_max_age, @@ -2791,6 +2793,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, autovac_table *tab = NULL; bool wraparound; StdRdOptions *relopts; + StdRdOptions *main_relopts = NULL; bool free_relopts = false; AutoVacuumScores scores; @@ -2801,21 +2804,25 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, classForm = (Form_pg_class) GETSTRUCT(classTup); /* - * Get the applicable reloptions. If it is a TOAST table, try to get the - * main table reloptions if the toast table itself doesn't have. + * Get the applicable reloptions. If it is a TOAST table, merge in the + * main table's reloptions where they are unset. */ relopts = (StdRdOptions *) extractRelOptions(classTup, pg_class_desc, NULL); if (relopts) free_relopts = true; - else if (classForm->relkind == RELKIND_TOASTVALUE && - table_toast_map != NULL) + + if (classForm->relkind == RELKIND_TOASTVALUE && + table_toast_map != NULL) { av_relation *hentry; bool found; hentry = hash_search(table_toast_map, &relid, HASH_FIND, &found); if (found && hentry->ar_hasrelopts) - relopts = &hentry->ar_reloptions; + { + main_relopts = &hentry->ar_reloptions; + relopts = merge_autovac_opts(relopts, main_relopts); + } } relation_needs_vacanalyze(relid, relopts, classForm, @@ -2902,6 +2909,48 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map, tab->at_params.log_analyze_min_duration = log_analyze_min_duration; tab->at_params.toast_parent = InvalidOid; + /* + * For TOAST tables, provide fallbacks for options that are not part + * of AutoVacOpts (and thus are not handled by merge_autovac_opts()). + */ + tab->at_params.main_index_cleanup = VACOPTVALUE_UNSPECIFIED; + tab->at_params.main_truncate = VACOPTVALUE_UNSPECIFIED; + tab->at_params.main_max_eager_freeze_failure_rate = -1.0; + + if (main_relopts != NULL) + { + switch (main_relopts->vacuum_index_cleanup) + { + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_ON: + tab->at_params.main_index_cleanup = VACOPTVALUE_ENABLED; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_OFF: + tab->at_params.main_index_cleanup = VACOPTVALUE_DISABLED; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_AUTO: + tab->at_params.main_index_cleanup = VACOPTVALUE_AUTO; + break; + case STDRD_OPTION_VACUUM_INDEX_CLEANUP_NOT_SET: + break; + } + + switch (main_relopts->vacuum_truncate) + { + case PG_TERNARY_TRUE: + tab->at_params.main_truncate = VACOPTVALUE_ENABLED; + break; + case PG_TERNARY_FALSE: + tab->at_params.main_truncate = VACOPTVALUE_DISABLED; + break; + case PG_TERNARY_UNSET: + break; + } + + if (main_relopts->vacuum_max_eager_freeze_failure_rate >= 0.0) + tab->at_params.main_max_eager_freeze_failure_rate = + main_relopts->vacuum_max_eager_freeze_failure_rate; + } + /* Determine the number of parallel vacuum workers to use */ tab->at_params.nworkers = 0; if (avopts) @@ -3670,3 +3719,111 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS) return (Datum) 0; } + +/* + * Combine a TOAST table's autovacuum options with the main table's. Any + * option the TOAST table leaves unset is taken from the main table's options. + * Either argument may be NULL. If both are NULL, NULL is returned. + * Otherwise, the options to use are returned. + * + * NB: This function destructively modifies toast_opts! + */ +static StdRdOptions * +merge_autovac_opts(StdRdOptions *toast_opts, StdRdOptions *main_opts) +{ + /* ternary fields */ + static const int ternary_offsets[] = { + offsetof(AutoVacOpts, enabled), + }; + + /* integer fields whose unset sentinel is -1 */ + static const int int_offsets_1[] = { + offsetof(AutoVacOpts, autovacuum_parallel_workers), + offsetof(AutoVacOpts, vacuum_threshold), + offsetof(AutoVacOpts, analyze_threshold), + offsetof(AutoVacOpts, vacuum_cost_limit), + offsetof(AutoVacOpts, freeze_min_age), + offsetof(AutoVacOpts, freeze_max_age), + offsetof(AutoVacOpts, freeze_table_age), + offsetof(AutoVacOpts, multixact_freeze_min_age), + offsetof(AutoVacOpts, multixact_freeze_max_age), + offsetof(AutoVacOpts, multixact_freeze_table_age), + offsetof(AutoVacOpts, log_vacuum_min_duration), + offsetof(AutoVacOpts, log_analyze_min_duration), + }; + + /* integer fields whose unset sentinel is -2 */ + static const int int_offsets_2[] = { + offsetof(AutoVacOpts, vacuum_max_threshold), + offsetof(AutoVacOpts, vacuum_ins_threshold), + }; + + /* float fields */ + static const int float_offsets[] = { + offsetof(AutoVacOpts, vacuum_cost_delay), + offsetof(AutoVacOpts, vacuum_scale_factor), + offsetof(AutoVacOpts, vacuum_ins_scale_factor), + offsetof(AutoVacOpts, analyze_scale_factor), + }; + + AutoVacOpts *toast_avopts; + AutoVacOpts *main_avopts; + + if (toast_opts == NULL) + return main_opts; + if (main_opts == NULL) + return toast_opts; + + toast_avopts = &toast_opts->autovacuum; + main_avopts = &main_opts->autovacuum; + + for (int i = 0; i < lengthof(ternary_offsets); i++) + { + pg_ternary *toast_opt; + pg_ternary *main_opt; + + toast_opt = (pg_ternary *) ((char *) toast_avopts + ternary_offsets[i]); + main_opt = (pg_ternary *) ((char *) main_avopts + ternary_offsets[i]); + + if (*toast_opt == PG_TERNARY_UNSET) + *toast_opt = *main_opt; + } + + for (int i = 0; i < lengthof(int_offsets_1); i++) + { + int *toast_opt; + int *main_opt; + + toast_opt = (int *) ((char *) toast_avopts + int_offsets_1[i]); + main_opt = (int *) ((char *) main_avopts + int_offsets_1[i]); + + if (*toast_opt == -1) + *toast_opt = *main_opt; + } + + for (int i = 0; i < lengthof(int_offsets_2); i++) + { + int *toast_opt; + int *main_opt; + + toast_opt = (int *) ((char *) toast_avopts + int_offsets_2[i]); + main_opt = (int *) ((char *) main_avopts + int_offsets_2[i]); + + if (*toast_opt == -2) + *toast_opt = *main_opt; + } + + for (int i = 0; i < lengthof(float_offsets); i++) + { + double *toast_opt; + double *main_opt; + + toast_opt = (double *) ((char *) toast_avopts + float_offsets[i]); + main_opt = (double *) ((char *) main_avopts + float_offsets[i]); + + if (*toast_opt == -1.0) + *toast_opt = *main_opt; + } + + return toast_opts; +} diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h index 956d9cea36d..335d1516a7e 100644 --- a/src/include/commands/vacuum.h +++ b/src/include/commands/vacuum.h @@ -248,6 +248,14 @@ typedef struct VacuumParams * disabled. */ int nworkers; + + /* + * Main table fallback values for TOAST tables to inherit when they have + * no setting of their own. + */ + VacOptValue main_index_cleanup; + VacOptValue main_truncate; + double main_max_eager_freeze_failure_rate; } VacuumParams; /* diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h index f1b96b1099d..9b55c601e94 100644 --- a/src/include/utils/rel.h +++ b/src/include/utils/rel.h @@ -306,6 +306,9 @@ typedef struct ForeignKeyCacheInfo * RelationGetFillFactor() and RelationGetTargetPageFreeSpace() can only * be applied to relations that use this format or a superset for * private options data. + * + * NB: When adding a new member, be sure to update merge_autovac_opts() and/or + * table_recheck_autovac() as necessary! */ /* autovacuum-related reloptions. */ typedef struct AutoVacOpts diff --git a/src/test/modules/injection_points/expected/vacuum.out b/src/test/modules/injection_points/expected/vacuum.out index 58df59fa927..0f0790a8584 100644 --- a/src/test/modules/injection_points/expected/vacuum.out +++ b/src/test/modules/injection_points/expected/vacuum.out @@ -79,6 +79,17 @@ NOTICE: notice triggered for injection point vacuum-truncate-enabled NOTICE: notice triggered for injection point vacuum-index-cleanup-auto NOTICE: notice triggered for injection point vacuum-truncate-enabled RESET vacuum_truncate; +-- TOAST table inherits main table's resolved values +CREATE TABLE vac_tab_toast_inherit(i int, j text) WITH + (autovacuum_enabled=false, + vacuum_index_cleanup=false, + vacuum_truncate=false, toast.vacuum_truncate=true); +VACUUM vac_tab_toast_inherit; +NOTICE: notice triggered for injection point vacuum-index-cleanup-disabled +NOTICE: notice triggered for injection point vacuum-truncate-disabled +NOTICE: notice triggered for injection point vacuum-index-cleanup-disabled +NOTICE: notice triggered for injection point vacuum-truncate-enabled +DROP TABLE vac_tab_toast_inherit; DROP TABLE vac_tab_auto; DROP TABLE vac_tab_on_toast_off; DROP TABLE vac_tab_off_toast_on; diff --git a/src/test/modules/injection_points/sql/vacuum.sql b/src/test/modules/injection_points/sql/vacuum.sql index 23760dd0f38..11a371e4ff7 100644 --- a/src/test/modules/injection_points/sql/vacuum.sql +++ b/src/test/modules/injection_points/sql/vacuum.sql @@ -33,6 +33,14 @@ SET vacuum_truncate = true; VACUUM vac_tab_auto; RESET vacuum_truncate; +-- TOAST table inherits main table's resolved values +CREATE TABLE vac_tab_toast_inherit(i int, j text) WITH + (autovacuum_enabled=false, + vacuum_index_cleanup=false, + vacuum_truncate=false, toast.vacuum_truncate=true); +VACUUM vac_tab_toast_inherit; +DROP TABLE vac_tab_toast_inherit; + DROP TABLE vac_tab_auto; DROP TABLE vac_tab_on_toast_off; DROP TABLE vac_tab_off_toast_on; -- 2.50.1 (Apple Git-155)
