agora inbox for pgsql-hackers@postgresql.org  

help / color / mirror / Atom feed

From: Nathan Bossart <nathandbossart@gmail.com>
To: pgsql-hackers@postgresql.org
Subject: problems with toast.* reloptions
Date: Thu, 19 Jun 2025 15:20:27 -0500
Message-ID: <aFRxC1W_kZU9OjJ9@nathan> (raw)

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

view thread (45+ messages)  latest in thread

Message-ID: <aFRxC1W_kZU9OjJ9@nathan>
Permalink:  ../aFRxC1W_kZU9OjJ9@nathan/
Also on:    postgresql.org/message-id/aFRxC1W_kZU9OjJ9@nathan

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: nathandbossart@gmail.com
  Subject: Re: problems with toast.* reloptions
  In-Reply-To: <aFRxC1W_kZU9OjJ9@nathan>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox