problems with toast.* reloptions

On Thu, Jun 19, 2025 at 03:20:27PM -0500, Nathan Bossart wrote:

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.

Are you referring to the case of a VACUUM pg_toast.pg_toast_NNN? I'm
not sure that we really need to care about looking up at the parent
relation in this case. It sounds to me that the intention of this
paragraph is for the case where the TOAST table is treated as a
secondary table, not when the TOAST table is directly vacuumed.
Perhaps the wording of the docs should be improved that this does not
happen if vacuuming directly a TOAST table.

* 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.

[.. /me double-checks the code .. ]

So we combine the options in do_autovacuum() with the two-pass logic
to gather the relation OIDs, then apply relation_needs_vacanalyze().
That looks like an old issue, that cannot be solved as long as we rely
on the relopts to be an all-or-nothing thing when assigning the
individual values for the TOAST relation. Oops.

* 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.

Relying on vacuum_rel() sounds like a bad idea if we can avoid that,
still perhaps that's OK as long as we don't use a pointer to the
VacuumParams and keep the updates to the value of vacuum_rel() local
inside the routine.

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.

These two points make sense here, yes.

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

For the case of a manual VACUUM on the main table, where the TOAST
table is treated as a secondary citizen, that makes sense as well,
yes.

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.

This one does not sound that important to me for the case of manual
VACUUM case directly done on a TOAST table. If you do that, the code
kind of assumes that a TOAST table is actually a "main" relation that
has no TOAST table. That should keep the code simpler, because we
would not need to look at what the parent relation holds when deciding
which options to use in ExecVacuum(). The autovacuum case is
different, as TOAST relations are worked on as their own items rather
than being secondary relations of the main tables.
--
Michael

On Fri, Jun 20, 2025 at 11:05:37AM +0900, Michael Paquier wrote:

On Thu, Jun 19, 2025 at 03:20:27PM -0500, Nathan Bossart wrote:

* 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.

Are you referring to the case of a VACUUM pg_toast.pg_toast_NNN? I'm
not sure that we really need to care about looking up at the parent
relation in this case. It sounds to me that the intention of this
paragraph is for the case where the TOAST table is treated as a
secondary table, not when the TOAST table is directly vacuumed.
Perhaps the wording of the docs should be improved that this does not
happen if vacuuming directly a TOAST table.

Yeah, I was mainly thinking of a VACUUM command that recurses to the TOAST
table. Of course, it'd be nice to fix VACUUM pg_toast.pg_toast_NNN, too,
but I'm personally not too worried about that use-case.

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.

This one does not sound that important to me for the case of manual
VACUUM case directly done on a TOAST table. If you do that, the code
kind of assumes that a TOAST table is actually a "main" relation that
has no TOAST table. That should keep the code simpler, because we
would not need to look at what the parent relation holds when deciding
which options to use in ExecVacuum(). The autovacuum case is
different, as TOAST relations are worked on as their own items rather
than being secondary relations of the main tables.

+1

--
nathan

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.

These two points make sense here, yes.

I investigated that this afternoon and identified two potential
implementation approaches:

1) Create functions like resolve_toast_vac_opts() and
resolve_toast_rel_opts(). These would then be used in
table_recheck_autovac(), NeedsAutoVacTableForXidWraparound(), and
do_autovacuum() after the toast table check.
2) When updating a table's relopt, also update the relopt of its
associated TOAST table if it's not already set. Similarly, when
creating a new TOAST table, it would inherit the parent's relopt.

Option 2 seems more reasonable to me, as it avoids requiring customers
to manually resolve these options, when they have different settings
for the parent and TOAST tables."

On Sat, Jun 21, 2025 at 11:45:25PM -0400, shihao zhong wrote:

2) When updating a table's relopt, also update the relopt of its
associated TOAST table if it's not already set. Similarly, when
creating a new TOAST table, it would inherit the parent's relopt.

Option 2 seems more reasonable to me, as it avoids requiring customers
to manually resolve these options, when they have different settings
for the parent and TOAST tables."

I like this one, but since it won't fix existing clusters, it might only be
workable for v19.

--
nathan

On Fri, Jun 20, 2025 at 11:05:37AM +0900, Michael Paquier wrote:

On Thu, Jun 19, 2025 at 03:20:27PM -0500, Nathan Bossart wrote:

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.

These two points make sense here, yes.

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

For the case of a manual VACUUM on the main table, where the TOAST
table is treated as a secondary citizen, that makes sense as well,
yes.

Here is a very rough proof-of-concept patch set for this. AFAICT there are
a few options we cannot fix on the back-branches because there is no way to
tell whether it is set or has just picked up the default. On v18 and
newer, we could use isset_offset, but that doesn't exist on older versions.
(I haven't looked closely, but I'm assuming that back-patching isset_offset
isn't an option.)

I would like to explore the "option 2" from upthread [0]/messages/by-id/aFl598epAdUrrv0y@nathan for v19. I think
that is a better long-term solution, and it may allow us to remove the
table_toast_map in autovacuum.

[0]: /messages/by-id/aFl598epAdUrrv0y@nathan

--
nathan

On Mon, Jun 23, 2025 at 03:59:56PM -0500, Nathan Bossart wrote:

Here is a very rough proof-of-concept patch set for this. AFAICT there are
a few options we cannot fix on the back-branches because there is no way to
tell whether it is set or has just picked up the default. On v18 and
newer, we could use isset_offset, but that doesn't exist on older versions.
(I haven't looked closely, but I'm assuming that back-patching isset_offset
isn't an option.)

Hmm. I am wondering if we need to be aggressive about this set of
changes at all in the back branches. It's been broken for a long time
without anybody really complaining about the fact that reloptions
being set or not influenced the outcome in the context of autovacuum,
so perhaps there is a good argument for keeping all that in v19. My
conservative 2c.

I would like to explore the "option 2" from upthread [0] for v19. I think
that is a better long-term solution, and it may allow us to remove the
table_toast_map in autovacuum.

[0] /messages/by-id/aFl598epAdUrrv0y@nathan

It would be nice to have some tests here to check the state of the
options used? My best guess would be a DEBUG1 entry combined with a
scan of the logs generated and an aggressive autovacuum worker
spawn to check that the options generated are what we expect for the
relations autovacuum picks up.
--
Michael

On Tue, Jun 24, 2025 at 02:10:55PM +0900, Michael Paquier wrote:

On Mon, Jun 23, 2025 at 03:59:56PM -0500, Nathan Bossart wrote:

Here is a very rough proof-of-concept patch set for this. AFAICT there are
a few options we cannot fix on the back-branches because there is no way to
tell whether it is set or has just picked up the default. On v18 and
newer, we could use isset_offset, but that doesn't exist on older versions.
(I haven't looked closely, but I'm assuming that back-patching isset_offset
isn't an option.)

Hmm. I am wondering if we need to be aggressive about this set of
changes at all in the back branches. It's been broken for a long time
without anybody really complaining about the fact that reloptions
being set or not influenced the outcome in the context of autovacuum,
so perhaps there is a good argument for keeping all that in v19. My
conservative 2c.

Yeah, I'm tempted to even ask how folks feel about removing the toast.*
reloptions. Maybe there's some simple cases that work well enough, but
AFAICT any moderately-complicated setup basically doesn't work at all. In
any case, writing out this patch set has got me on the fix-on-HEAD-only
bandwagon.

I would like to explore the "option 2" from upthread [0] for v19. I think
that is a better long-term solution, and it may allow us to remove the
table_toast_map in autovacuum.

It would be nice to have some tests here to check the state of the
options used? My best guess would be a DEBUG1 entry combined with a
scan of the logs generated and an aggressive autovacuum worker
spawn to check that the options generated are what we expect for the
relations autovacuum picks up.

Eh... I agree that's probably how we'd have to test it with the existing
tools, but it sure sounds like a recipe for a flaky test.

--
nathan

On Mon, Jun 23, 2025 at 10:59:51AM -0500, Nathan Bossart wrote:

On Sat, Jun 21, 2025 at 11:45:25PM -0400, shihao zhong wrote:

2) When updating a table's relopt, also update the relopt of its
associated TOAST table if it's not already set. Similarly, when
creating a new TOAST table, it would inherit the parent's relopt.

Option 2 seems more reasonable to me, as it avoids requiring customers
to manually resolve these options, when they have different settings
for the parent and TOAST tables."

I like this one, but since it won't fix existing clusters, it might only be
workable for v19.

Actually, I think there's a problem with this approach. If we set the
reloption for both the main relation and the TOAST table, then we won't
know what to do for RESET. Take the following examples:

ALTER TABLE test SET (vacuum_truncate = false);
ALTER TABLE test RESET (vacuum_truncate);

ALTER TABLE test SET (vacuum_truncate = false);
ALTER TABLE test SET (toast.vacuum_truncate = false);
ALTER TABLE test RESET (vacuum_truncate);

After executing the commands in the first stanza, you'd expect the
vacuum_truncate reloption to be unset for both the main relation and its
TOAST table. After the second one, you'd expect it to be set for only the
TOAST table. But unless there's some way to know the source of the TOAST
table's reloption, we can't know which behavior is correct at RESET time.

--
nathan

Actually, I think there's a problem with this approach...

You're right. I forgot we can reset the table options. While we could
use a placeholder and resolve it on-demand, that seems like too much
work.

On Wed, Jul 2, 2025 at 10:44 AM shihao zhong <zhong950419@gmail.com> wrote:

Actually, I think there's a problem with this approach...

You're right. I forgot we can reset the table options. While we could
use a placeholder and resolve it on-demand, that seems like too much
work.

Hi all,

I started a conversation about TOAST table vacuum truncation parameter
inheritance [1]/messages/by-id/CANqtF-pLHBDdNM-DifXQqVq+VWA3DTYOx4+3m2+TFJ1zDOZETg@mail.gmail.com and was pointed to this thread which I totally missed. I
recently came across the issue where `vacuum_truncate` on TOAST tables
wasn't being inherited even though the parent table had the setting, and
the documentation mentioned that it should work [2]https://www.postgresql.org/docs/current/sql-createtable.html#RELOPTION-VACUUM-TRUNCATE. I see that there are a
lot of good conversations here already.

I'm curious to hear what folks think about the approach where we implement
the inheritance logic directly in `vacuum_rel()` when `params.truncate ==
VACOPTVALUE_UNSPECIFIED`. This should address the point raised upthread
about `vacuum_rel()` not looking up the main relation's reloptions when
processing a TOAST and also what I discovered in [1]/messages/by-id/CANqtF-pLHBDdNM-DifXQqVq+VWA3DTYOx4+3m2+TFJ1zDOZETg@mail.gmail.com. For instance it
could be something like:

1. For TOAST tables: When no explicit `toast.vacuum_truncate` is set, scan
`pg_class.reltoastrelid` to find the parent table and inherit its
`vacuum_truncate` setting.
2. For manual VACUUM: Pass the final truncate decision from main table to
TOAST table in the `toast_vacuum_params`
3. For autovacuum: The inheritance happens naturally since autovacuum calls
`vacuum_rel()` for each relation independently

This basically teaches `vacuum_rel()` to consult the main relation's
reloptions for TOAST tables, which was the core issue identified upthread.
It handles both manual VACUUM and autovacuum scenarios in one place,
without changing function signatures (which helps with potential
backporting).

The execution-layer fix should also help us avoid the parameter
contamination issues that were fixed in commit 661643dedad9 (I believe?),
since we're doing the lookup fresh each time `vacuum_rel()` is called on a
TOAST table.

I realize backporting might not be preferred given this issue has existed
for a long time. However, that is precisely why I feel like it might be
worth patching it because, at least per the docs, my understanding was that
if `vacuum_truncate` is set on the parent table then it applies to the
TOAST as well and I wonder if there are others in the same boat as well.

Would something like this focused approach (just teaching `vacuum_rel()` to
look up parent reloptions for TOAST tables) could be a reasonable fix that
complements the broader reloptions work discussed upthread for v19 and also
backportable to v13 onwards?

I am happy to help with this and split out the changes as it makes sense as
well.

Thanks
Shayon

[1]: /messages/by-id/CANqtF-pLHBDdNM-DifXQqVq+VWA3DTYOx4+3m2+TFJ1zDOZETg@mail.gmail.com
/messages/by-id/CANqtF-pLHBDdNM-DifXQqVq+VWA3DTYOx4+3m2+TFJ1zDOZETg@mail.gmail.com

[2]: https://www.postgresql.org/docs/current/sql-createtable.html#RELOPTION-VACUUM-TRUNCATE
https://www.postgresql.org/docs/current/sql-createtable.html#RELOPTION-VACUUM-TRUNCATE