agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedpg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
8+ messages / 2 participants
[nested] [flat]
* pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-27 22:42 Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Masahiko Sawada @ 2026-08-27 22:42 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Nathan Bossart <nathandbossart@gmail.com>
Hi all,
(CCing Nathan as the committer of 87f61f0c8280 and fad70a09ff43)
I found that pg_get_autovacuum_scores reports wrong values for TOAST
tables. In do_autovacuum(), we fall back to the main table's reloption
when the TOAST table has no reloptions of its own, and
table_recheck_autovac() does the same. However,
pg_stat_get_autovacuum_scores() only calls extract_autovac_opts() and
passes NULL when the TOAST table has none.
So the view computes the TOAST table's scores from rht eGUC defaults
whenever the user sets autovacuum_* on the main table without setting
the corresponding toast.* option. Here is a simple reproducer:
create table test (a int, b text);
alter table test alter column b set storage external;
alter table test set (autovacuum_vacuum_threshold = 1,
autovacuum_vacuum_scale_factor = 0, autovacuum_enabled = false);
insert into test select i, repeat('z',4000) from generate_series(1,200) i;
delete from test where a <= 100;
select relid::regclass, vacuum_score, do_vacuum from
pg_stat_autovacuum_scores where relid in ('test'::regclass, (select
reltoastrelid from pg_class where relname = 'test'));
relid | vacuum_score | do_vacuum
-------------------------+--------------+-----------
test | 100 | f
pg_toast.pg_toast_16384 | 6 | t
(2 rows)
And what the autovacuum worker computes for the same two relations is:
DEBUG: test: vac: 100 (thresh 1, score 100.00), ...
DEBUG: pg_toast_16384: vac: 300 (thresh 1, score 300.00), ...
Which doesn't match what the view shows.
Note that this issue happens only in v19 as commit fad70a09ff43 fixed
autovacuum's handling of TOAST reloptions. While I agree that the
commit was not backpatched to v19, I think we should fix how the view
computes the toast table's reloptions.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 13:14 Nathan Bossart <nathandbossart@gmail.com>
parent: Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2026-08-28 13:14 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers
On Thu, Aug 27, 2026 at 03:42:23PM -0700, Masahiko Sawada wrote:
> I found that pg_get_autovacuum_scores reports wrong values for TOAST
> tables. In do_autovacuum(), we fall back to the main table's reloption
> when the TOAST table has no reloptions of its own, and
> table_recheck_autovac() does the same. However,
> pg_stat_get_autovacuum_scores() only calls extract_autovac_opts() and
> passes NULL when the TOAST table has none.
>
> So the view computes the TOAST table's scores from rht eGUC defaults
> whenever the user sets autovacuum_* on the main table without setting
> the corresponding toast.* option. Here is a simple reproducer:
You're right. I will work on a patch for this and get it fixed ASAP.
--
nathan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 15:02 Nathan Bossart <nathandbossart@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2026-08-28 15:02 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers
Here is a patch.
--
nathan
From cd6e33acf8086d0c1b5b2d3fb0d177ea5c77cb8f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 28 Aug 2026 09:54:06 -0500
Subject: [PATCH v1 1/1] Fix pg_stat_autovacuum_scores for TOAST tables.
In v19, pg_stat_autovacuum_scores computes a TOAST table's scores
from its own storage parameters alone. Autovacuum instead falls
back to the main table's parameters when the TOAST table has none
of its own, so the view may report scores that don't match what
autovacuum would calculate. This contradicts the documented
promise that the view generates its results the same way autovacuum
workers do. To fix, teach the view to do the same fallback. As in
do_autovacuum(), we cannot know a TOAST table's parameters until we
have seen its main relation, so the view now makes a preliminary
pass over pg_class to collect the main relations' parameters.
Commit fad70a09ff for v20 improved autovacuum's handling of TOAST
storage parameters and adjusted the view to match, but it was
deemed too intrusive to back-patch. This fix is for v19 only.
Oversight in commit 87f61f0c82.
Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/CAD21AoB1CJRVfCDh8qYuD3eueiygXxk7F3nybgjN0RZXSD-QUw%40mail.gmail.com
Backpatch-through: 19 only
---
src/backend/postmaster/autovacuum.c | 75 ++++++++++++++++++++++++++++-
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0c975d0eda6..089b9c468a5 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3652,6 +3652,8 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
TableScanDesc scan;
HeapTuple tup;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HTAB *table_toast_map;
+ HASHCTL ctl;
InitMaterializedSRF(fcinfo, 0);
@@ -3660,13 +3662,64 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
recentXid = ReadNextTransactionId();
recentMulti = ReadNextMultiXactId();
- /* scan pg_class */
+ /* create hash table for toast <-> main relid mapping */
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(av_relation);
+ ctl.hcxt = CurrentMemoryContext;
+ table_toast_map = hash_create("TOAST to main relid map",
+ 100,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
rel = table_open(RelationRelationId, AccessShareLock);
+
+ /*
+ * Do an initial pass over pg_class to collect the main relations'
+ * autovacuum paramters, which a TOAST table with none of its own
+ * inherits. We cannot gather these as we go, since a TOAST table may
+ * precede its main relation in the scan.
+ */
scan = table_beginscan_catalog(rel, 0, NULL);
while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
AutoVacOpts *avopts;
+ av_relation *hentry;
+ bool found;
+
+ /* skip ineligible entries */
+ if (form->relkind != RELKIND_RELATION &&
+ form->relkind != RELKIND_MATVIEW)
+ continue;
+ if (form->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+ if (!OidIsValid(form->reltoastrelid))
+ continue;
+
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts == NULL)
+ continue;
+
+ hentry = hash_search(table_toast_map, &form->reltoastrelid,
+ HASH_ENTER, &found);
+ Assert(!found); /* rels cannot share a TOAST table */
+
+ /* hash_search already filled in the key */
+ hentry->ar_relid = form->oid;
+ hentry->ar_hasrelopts = true;
+ memcpy(&hentry->ar_reloptions, avopts, sizeof(AutoVacOpts));
+
+ pfree(avopts);
+ }
+ table_endscan(scan);
+
+ /* now scan pg_class again to compute the scores */
+ scan = table_beginscan_catalog(rel, 0, NULL);
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
+ AutoVacOpts *avopts;
+ bool free_avopts = false;
bool dovacuum;
bool doanalyze;
bool wraparound;
@@ -3682,13 +3735,30 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
if (form->relpersistence == RELPERSISTENCE_TEMP)
continue;
+ /*
+ * fetch reloptions -- if this toast table does not have them, try the
+ * main rel
+ */
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts)
+ free_avopts = true;
+ else if (form->relkind == RELKIND_TOASTVALUE)
+ {
+ av_relation *hentry;
+ bool found;
+
+ hentry = hash_search(table_toast_map, &form->oid, HASH_FIND, &found);
+ if (found && hentry->ar_hasrelopts)
+ avopts = &hentry->ar_reloptions;
+ }
+
avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
relation_needs_vacanalyze(form->oid, avopts, form,
effective_multixact_freeze_max_age,
LOG_NEVER,
&dovacuum, &doanalyze, &wraparound,
&scores);
- if (avopts)
+ if (free_avopts)
pfree(avopts);
vals[0] = ObjectIdGetDatum(form->oid);
@@ -3706,6 +3776,7 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
}
table_endscan(scan);
table_close(rel, AccessShareLock);
+ hash_destroy(table_toast_map);
return (Datum) 0;
}
--
2.55.0
Attachments:
[text/plain] v1-0001-Fix-pg_stat_autovacuum_scores-for-TOAST-tables.patch (5.0K, ../../apGjEvo15Z5oKHEM@nathan/2-v1-0001-Fix-pg_stat_autovacuum_scores-for-TOAST-tables.patch)
download | inline diff:
From cd6e33acf8086d0c1b5b2d3fb0d177ea5c77cb8f Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 28 Aug 2026 09:54:06 -0500
Subject: [PATCH v1 1/1] Fix pg_stat_autovacuum_scores for TOAST tables.
In v19, pg_stat_autovacuum_scores computes a TOAST table's scores
from its own storage parameters alone. Autovacuum instead falls
back to the main table's parameters when the TOAST table has none
of its own, so the view may report scores that don't match what
autovacuum would calculate. This contradicts the documented
promise that the view generates its results the same way autovacuum
workers do. To fix, teach the view to do the same fallback. As in
do_autovacuum(), we cannot know a TOAST table's parameters until we
have seen its main relation, so the view now makes a preliminary
pass over pg_class to collect the main relations' parameters.
Commit fad70a09ff for v20 improved autovacuum's handling of TOAST
storage parameters and adjusted the view to match, but it was
deemed too intrusive to back-patch. This fix is for v19 only.
Oversight in commit 87f61f0c82.
Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/CAD21AoB1CJRVfCDh8qYuD3eueiygXxk7F3nybgjN0RZXSD-QUw%40mail.gmail.com
Backpatch-through: 19 only
---
src/backend/postmaster/autovacuum.c | 75 ++++++++++++++++++++++++++++-
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0c975d0eda6..089b9c468a5 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3652,6 +3652,8 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
TableScanDesc scan;
HeapTuple tup;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HTAB *table_toast_map;
+ HASHCTL ctl;
InitMaterializedSRF(fcinfo, 0);
@@ -3660,13 +3662,64 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
recentXid = ReadNextTransactionId();
recentMulti = ReadNextMultiXactId();
- /* scan pg_class */
+ /* create hash table for toast <-> main relid mapping */
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(av_relation);
+ ctl.hcxt = CurrentMemoryContext;
+ table_toast_map = hash_create("TOAST to main relid map",
+ 100,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
rel = table_open(RelationRelationId, AccessShareLock);
+
+ /*
+ * Do an initial pass over pg_class to collect the main relations'
+ * autovacuum paramters, which a TOAST table with none of its own
+ * inherits. We cannot gather these as we go, since a TOAST table may
+ * precede its main relation in the scan.
+ */
scan = table_beginscan_catalog(rel, 0, NULL);
while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
AutoVacOpts *avopts;
+ av_relation *hentry;
+ bool found;
+
+ /* skip ineligible entries */
+ if (form->relkind != RELKIND_RELATION &&
+ form->relkind != RELKIND_MATVIEW)
+ continue;
+ if (form->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+ if (!OidIsValid(form->reltoastrelid))
+ continue;
+
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts == NULL)
+ continue;
+
+ hentry = hash_search(table_toast_map, &form->reltoastrelid,
+ HASH_ENTER, &found);
+ Assert(!found); /* rels cannot share a TOAST table */
+
+ /* hash_search already filled in the key */
+ hentry->ar_relid = form->oid;
+ hentry->ar_hasrelopts = true;
+ memcpy(&hentry->ar_reloptions, avopts, sizeof(AutoVacOpts));
+
+ pfree(avopts);
+ }
+ table_endscan(scan);
+
+ /* now scan pg_class again to compute the scores */
+ scan = table_beginscan_catalog(rel, 0, NULL);
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
+ AutoVacOpts *avopts;
+ bool free_avopts = false;
bool dovacuum;
bool doanalyze;
bool wraparound;
@@ -3682,13 +3735,30 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
if (form->relpersistence == RELPERSISTENCE_TEMP)
continue;
+ /*
+ * fetch reloptions -- if this toast table does not have them, try the
+ * main rel
+ */
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts)
+ free_avopts = true;
+ else if (form->relkind == RELKIND_TOASTVALUE)
+ {
+ av_relation *hentry;
+ bool found;
+
+ hentry = hash_search(table_toast_map, &form->oid, HASH_FIND, &found);
+ if (found && hentry->ar_hasrelopts)
+ avopts = &hentry->ar_reloptions;
+ }
+
avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
relation_needs_vacanalyze(form->oid, avopts, form,
effective_multixact_freeze_max_age,
LOG_NEVER,
&dovacuum, &doanalyze, &wraparound,
&scores);
- if (avopts)
+ if (free_avopts)
pfree(avopts);
vals[0] = ObjectIdGetDatum(form->oid);
@@ -3706,6 +3776,7 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
}
table_endscan(scan);
table_close(rel, AccessShareLock);
+ hash_destroy(table_toast_map);
return (Datum) 0;
}
--
2.55.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 15:16 Nathan Bossart <nathandbossart@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2026-08-28 15:16 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers
On Fri, Aug 28, 2026 at 10:02:42AM -0500, Nathan Bossart wrote:
> Here is a patch.
Sorry for the noise. I noticed some silly mistakes in v1, so here's a v2
with those fixed.
--
nathan
From 704573f8367e8065f9e7d02d3af02f21a4870ace Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 28 Aug 2026 09:54:06 -0500
Subject: [PATCH v2 1/1] Fix pg_stat_autovacuum_scores for TOAST tables.
In v19, pg_stat_autovacuum_scores computes a TOAST table's scores
from its own storage parameters alone. Autovacuum instead falls
back to the main table's parameters when the TOAST table has none
of its own, so the view may report scores that don't match what
autovacuum would calculate. This contradicts the documented
promise that the view generates its results the same way autovacuum
workers do. To fix, teach the view to do the same fallback. As in
do_autovacuum(), we cannot know a TOAST table's parameters until we
have seen its main relation, so the view now makes a preliminary
pass over pg_class to collect the main relations' parameters.
Commit fad70a09ff for v20 improved autovacuum's handling of TOAST
storage parameters and adjusted the view to match, but it was
deemed too intrusive to back-patch. This fix is for v19 only.
Oversight in commit 87f61f0c82.
Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/CAD21AoB1CJRVfCDh8qYuD3eueiygXxk7F3nybgjN0RZXSD-QUw%40mail.gmail.com
Backpatch-through: 19 only
---
src/backend/postmaster/autovacuum.c | 75 ++++++++++++++++++++++++++++-
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0c975d0eda6..badd545ba0e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3652,6 +3652,8 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
TableScanDesc scan;
HeapTuple tup;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HTAB *table_toast_map;
+ HASHCTL ctl;
InitMaterializedSRF(fcinfo, 0);
@@ -3660,13 +3662,64 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
recentXid = ReadNextTransactionId();
recentMulti = ReadNextMultiXactId();
- /* scan pg_class */
+ /* create hash table for toast <-> main relid mapping */
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(av_relation);
+ ctl.hcxt = CurrentMemoryContext;
+ table_toast_map = hash_create("TOAST to main relid map",
+ 100,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
rel = table_open(RelationRelationId, AccessShareLock);
+
+ /*
+ * Do an initial pass over pg_class to collect the main relations'
+ * autovacuum parameters, which a TOAST table with none of its own
+ * inherits. We cannot gather these as we go, since a TOAST table may
+ * precede its main relation in the scan.
+ */
+ scan = table_beginscan_catalog(rel, 0, NULL);
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
+ AutoVacOpts *avopts;
+ av_relation *hentry;
+ bool found;
+
+ /* skip ineligible entries */
+ if (form->relkind != RELKIND_RELATION &&
+ form->relkind != RELKIND_MATVIEW)
+ continue;
+ if (form->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+ if (!OidIsValid(form->reltoastrelid))
+ continue;
+
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts == NULL)
+ continue;
+
+ hentry = hash_search(table_toast_map, &form->reltoastrelid,
+ HASH_ENTER, &found);
+ Assert(!found); /* rels cannot share a TOAST table */
+
+ /* hash_search already filled in the key */
+ hentry->ar_relid = form->oid;
+ hentry->ar_hasrelopts = true;
+ memcpy(&hentry->ar_reloptions, avopts, sizeof(AutoVacOpts));
+
+ pfree(avopts);
+ }
+ table_endscan(scan);
+
+ /* now scan pg_class again to compute the scores */
scan = table_beginscan_catalog(rel, 0, NULL);
while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
AutoVacOpts *avopts;
+ bool free_avopts = false;
bool dovacuum;
bool doanalyze;
bool wraparound;
@@ -3682,13 +3735,30 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
if (form->relpersistence == RELPERSISTENCE_TEMP)
continue;
+ /*
+ * fetch reloptions -- if this toast table does not have them, try the
+ * main rel
+ */
avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts)
+ free_avopts = true;
+ else if (form->relkind == RELKIND_TOASTVALUE)
+ {
+ av_relation *hentry;
+ bool found;
+
+ hentry = hash_search(table_toast_map, &form->oid,
+ HASH_FIND, &found);
+ if (found && hentry->ar_hasrelopts)
+ avopts = &hentry->ar_reloptions;
+ }
+
relation_needs_vacanalyze(form->oid, avopts, form,
effective_multixact_freeze_max_age,
LOG_NEVER,
&dovacuum, &doanalyze, &wraparound,
&scores);
- if (avopts)
+ if (free_avopts)
pfree(avopts);
vals[0] = ObjectIdGetDatum(form->oid);
@@ -3706,6 +3776,7 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
}
table_endscan(scan);
table_close(rel, AccessShareLock);
+ hash_destroy(table_toast_map);
return (Datum) 0;
}
--
2.55.0
Attachments:
[text/plain] v2-0001-Fix-pg_stat_autovacuum_scores-for-TOAST-tables.patch (5.0K, ../../apGmOZeIlyW-Y1f6@nathan/2-v2-0001-Fix-pg_stat_autovacuum_scores-for-TOAST-tables.patch)
download | inline diff:
From 704573f8367e8065f9e7d02d3af02f21a4870ace Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Fri, 28 Aug 2026 09:54:06 -0500
Subject: [PATCH v2 1/1] Fix pg_stat_autovacuum_scores for TOAST tables.
In v19, pg_stat_autovacuum_scores computes a TOAST table's scores
from its own storage parameters alone. Autovacuum instead falls
back to the main table's parameters when the TOAST table has none
of its own, so the view may report scores that don't match what
autovacuum would calculate. This contradicts the documented
promise that the view generates its results the same way autovacuum
workers do. To fix, teach the view to do the same fallback. As in
do_autovacuum(), we cannot know a TOAST table's parameters until we
have seen its main relation, so the view now makes a preliminary
pass over pg_class to collect the main relations' parameters.
Commit fad70a09ff for v20 improved autovacuum's handling of TOAST
storage parameters and adjusted the view to match, but it was
deemed too intrusive to back-patch. This fix is for v19 only.
Oversight in commit 87f61f0c82.
Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>
Discussion: https://postgr.es/m/CAD21AoB1CJRVfCDh8qYuD3eueiygXxk7F3nybgjN0RZXSD-QUw%40mail.gmail.com
Backpatch-through: 19 only
---
src/backend/postmaster/autovacuum.c | 75 ++++++++++++++++++++++++++++-
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index 0c975d0eda6..badd545ba0e 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -3652,6 +3652,8 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
TableScanDesc scan;
HeapTuple tup;
ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+ HTAB *table_toast_map;
+ HASHCTL ctl;
InitMaterializedSRF(fcinfo, 0);
@@ -3660,13 +3662,64 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
recentXid = ReadNextTransactionId();
recentMulti = ReadNextMultiXactId();
- /* scan pg_class */
+ /* create hash table for toast <-> main relid mapping */
+ ctl.keysize = sizeof(Oid);
+ ctl.entrysize = sizeof(av_relation);
+ ctl.hcxt = CurrentMemoryContext;
+ table_toast_map = hash_create("TOAST to main relid map",
+ 100,
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
rel = table_open(RelationRelationId, AccessShareLock);
+
+ /*
+ * Do an initial pass over pg_class to collect the main relations'
+ * autovacuum parameters, which a TOAST table with none of its own
+ * inherits. We cannot gather these as we go, since a TOAST table may
+ * precede its main relation in the scan.
+ */
+ scan = table_beginscan_catalog(rel, 0, NULL);
+ while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
+ {
+ Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
+ AutoVacOpts *avopts;
+ av_relation *hentry;
+ bool found;
+
+ /* skip ineligible entries */
+ if (form->relkind != RELKIND_RELATION &&
+ form->relkind != RELKIND_MATVIEW)
+ continue;
+ if (form->relpersistence == RELPERSISTENCE_TEMP)
+ continue;
+ if (!OidIsValid(form->reltoastrelid))
+ continue;
+
+ avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts == NULL)
+ continue;
+
+ hentry = hash_search(table_toast_map, &form->reltoastrelid,
+ HASH_ENTER, &found);
+ Assert(!found); /* rels cannot share a TOAST table */
+
+ /* hash_search already filled in the key */
+ hentry->ar_relid = form->oid;
+ hentry->ar_hasrelopts = true;
+ memcpy(&hentry->ar_reloptions, avopts, sizeof(AutoVacOpts));
+
+ pfree(avopts);
+ }
+ table_endscan(scan);
+
+ /* now scan pg_class again to compute the scores */
scan = table_beginscan_catalog(rel, 0, NULL);
while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL)
{
Form_pg_class form = (Form_pg_class) GETSTRUCT(tup);
AutoVacOpts *avopts;
+ bool free_avopts = false;
bool dovacuum;
bool doanalyze;
bool wraparound;
@@ -3682,13 +3735,30 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
if (form->relpersistence == RELPERSISTENCE_TEMP)
continue;
+ /*
+ * fetch reloptions -- if this toast table does not have them, try the
+ * main rel
+ */
avopts = extract_autovac_opts(tup, RelationGetDescr(rel));
+ if (avopts)
+ free_avopts = true;
+ else if (form->relkind == RELKIND_TOASTVALUE)
+ {
+ av_relation *hentry;
+ bool found;
+
+ hentry = hash_search(table_toast_map, &form->oid,
+ HASH_FIND, &found);
+ if (found && hentry->ar_hasrelopts)
+ avopts = &hentry->ar_reloptions;
+ }
+
relation_needs_vacanalyze(form->oid, avopts, form,
effective_multixact_freeze_max_age,
LOG_NEVER,
&dovacuum, &doanalyze, &wraparound,
&scores);
- if (avopts)
+ if (free_avopts)
pfree(avopts);
vals[0] = ObjectIdGetDatum(form->oid);
@@ -3706,6 +3776,7 @@ pg_stat_get_autovacuum_scores(PG_FUNCTION_ARGS)
}
table_endscan(scan);
table_close(rel, AccessShareLock);
+ hash_destroy(table_toast_map);
return (Datum) 0;
}
--
2.55.0
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 18:12 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Masahiko Sawada @ 2026-08-28 18:12 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers
On Fri, Aug 28, 2026 at 8:16 AM Nathan Bossart <nathandbossart@gmail.com> wrote:
>
> On Fri, Aug 28, 2026 at 10:02:42AM -0500, Nathan Bossart wrote:
> > Here is a patch.
>
> Sorry for the noise. I noticed some silly mistakes in v1, so here's a v2
> with those fixed.
Thank you for making the patch quickly! The patch looks good to me. A nitpick:
+ if (found && hentry->ar_hasrelopts)
+ avopts = &hentry->ar_reloptions;
ar_hasrelopts is always true here, since entries are only created when
extract_autovac_opts() returns non-NULL, so the second conjunct is
redundant actually. Having said that, it seems safer for future
changes and keeping it for symmetry with do_autovacuum() seems fine to
me.
Do we want to have regression tests for it? FWIW no test exercises
pg_stat_get_autovacuum_scores(). The only reference in the tree is the
view definition in rules.out. That's presumably why this went
unnoticed.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 18:50 Nathan Bossart <nathandbossart@gmail.com>
parent: Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Nathan Bossart @ 2026-08-28 18:50 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers
On Fri, Aug 28, 2026 at 11:12:34AM -0700, Masahiko Sawada wrote:
> Thank you for making the patch quickly! The patch looks good to me. A nitpick:
Thanks for reviewing.
> + if (found && hentry->ar_hasrelopts)
> + avopts = &hentry->ar_reloptions;
>
> ar_hasrelopts is always true here, since entries are only created when
> extract_autovac_opts() returns non-NULL, so the second conjunct is
> redundant actually. Having said that, it seems safer for future
> changes and keeping it for symmetry with do_autovacuum() seems fine to
> me.
Yeah, this is about what I was thinking.
> Do we want to have regression tests for it? FWIW no test exercises
> pg_stat_get_autovacuum_scores(). The only reference in the tree is the
> view definition in rules.out. That's presumably why this went
> unnoticed.
It might be worth adding a test or two for this view, but I doubt it
would've caught this issue. IIRC I held off adding tests originally
because I was worried about test stability.
--
nathan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 19:15 Masahiko Sawada <sawada.mshk@gmail.com>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Masahiko Sawada @ 2026-08-28 19:15 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: pgsql-hackers
On Fri, Aug 28, 2026 at 11:50 AM Nathan Bossart
<nathandbossart@gmail.com> wrote:
>
> On Fri, Aug 28, 2026 at 11:12:34AM -0700, Masahiko Sawada wrote:
> > Thank you for making the patch quickly! The patch looks good to me. A nitpick:
>
> Thanks for reviewing.
>
> > + if (found && hentry->ar_hasrelopts)
> > + avopts = &hentry->ar_reloptions;
> >
> > ar_hasrelopts is always true here, since entries are only created when
> > extract_autovac_opts() returns non-NULL, so the second conjunct is
> > redundant actually. Having said that, it seems safer for future
> > changes and keeping it for symmetry with do_autovacuum() seems fine to
> > me.
>
> Yeah, this is about what I was thinking.
>
> > Do we want to have regression tests for it? FWIW no test exercises
> > pg_stat_get_autovacuum_scores(). The only reference in the tree is the
> > view definition in rules.out. That's presumably why this went
> > unnoticed.
>
> It might be worth adding a test or two for this view, but I doubt it
> would've caught this issue. IIRC I held off adding tests originally
> because I was worried about test stability.
Setting autovacuum_enabled = off to the tables while checking
pg_stat_get_autovacuum_scores() would help the test stability. Adding
regression tests to the view would be a separate topic so I think we
can fix the issue by your patch separately from the regression tests.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables
@ 2026-08-28 20:13 Nathan Bossart <nathandbossart@gmail.com>
parent: Masahiko Sawada <sawada.mshk@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Nathan Bossart @ 2026-08-28 20:13 UTC (permalink / raw)
To: Masahiko Sawada <sawada.mshk@gmail.com>; +Cc: pgsql-hackers
Committed.
--
nathan
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-08-28 20:13 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-08-27 22:42 pg_stat_get_autovacuum_scores ignores the main table's reloptions for TOAST tables Masahiko Sawada <sawada.mshk@gmail.com>
2026-08-28 13:14 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-28 15:02 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-28 15:16 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-28 18:12 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-08-28 18:50 ` Nathan Bossart <nathandbossart@gmail.com>
2026-08-28 19:15 ` Masahiko Sawada <sawada.mshk@gmail.com>
2026-08-28 20:13 ` Nathan Bossart <nathandbossart@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox