Date: Thu, 19 Jun 2025 15:20:27 -0500
From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: problems with toast.* reloptions
Message-ID: <aFRxC1W_kZU9OjJ9@nathan>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Archived-At: <https://www.postgresql.org/message-id/aFRxC1W_kZU9OjJ9%40nathan>
Precedence: bulk

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