problems with toast.* reloptions

From: Nathan Bossart <nathandbossart(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: problems with toast.* reloptions
Date: 2025-06-19 20:20:27
Message-ID: aFRxC1W_kZU9OjJ9@nathan
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

While investigating problems caused by vacuum_rel() scribbling on its
VacuumParams argument [0], I noticed some other interesting bugs with the
toast.* reloption code. Note that the documentation for the reloptions has
the following line:

If a table parameter value is set and the equivalent toast. parameter
is not, the TOAST table will use the table's parameter value.

The problems I found are as follows:

* vacuum_rel() does not look up the main relation's reloptions when
processing a TOAST table, which is a problem for manual VACUUMs. The
aforementioned bug [0] causes you to sometimes get the expected behavior
(because the parameters are overridden before recursing to TOAST), but
fixing that bug makes that accidental behavior go away.

* For autovacuum, the main table's reloptions are only used if the TOAST
table has no reloptions set. So, if your relation has
autovacuum_vacuum_threshold and toast.vacuum_index_cleanup set, the main
relation's autovacuum_vacuum_threshold setting won't be used for the
TOAST table.

* Even when the preceding point doesn't apply, autovacuum doesn't use the
main relation's setting for some parameters (e.g., vacuum_truncate).
Instead, it leaves them uninitialized and expects vacuum_rel() to fill
them in. This is a problem because, as mentioned earlier, vacuum_rel()
doesn't consult the main relation's reloptions either.

I think we need to do something like the following to fix this:

* Teach autovacuum to combine the TOAST reloptions with the main relation's
when processing TOAST tables (with the toast.* ones winning if both are
set).

* Teach autovacuum to resolve reloptions for parameters like
vacuum_truncate instead of relying on vacuum_rel() to fill it in.

* Have vacuum_rel() send the main relation's reloptions when recursing to
the TOAST table so that we can combine them there, too.

This doesn't fix VACUUM against a TOAST table directly (e.g., VACUUM
pg_toast.pg_toast_5432), but that might not be too important because
(PROCESS_TOAST TRUE) is the main supported way to vacuum a TOAST table. If
we did want to fix that, though, I think we'd have to teach vacuum_rel() or
the relcache to look up the reloptions for the main relation.

Thoughts?

[0] https://postgr.es/m/flat/CAGRkXqTo%2BaK%3DGTy5pSc-9cy8H2F2TJvcrZ-zXEiNJj93np1UUw%40mail.gmail.com

--
nathan

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nathan Bossart 2025-06-19 20:30:26 Re: Fixes inconsistent behavior in vacuum when it processes multiple relations
Previous Message Bruce Momjian 2025-06-19 20:09:04 Re: libxml2 author overwhelmed with security requests