Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Avoid scribbling of VACUUM options
- 661643dedad9 18.0 cited

problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-19T20:20:27Z

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

Re: problems with toast.* reloptions

Michael Paquier <michael@paquier.xyz> — 2025-06-20T02:05:37Z

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-20T19:12:43Z

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

Re: problems with toast.* reloptions

shihao zhong <zhong950419@gmail.com> — 2025-06-22T03:45:25Z

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-23T15:59:51Z

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-23T20:59:56Z

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] 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] https://postgr.es/m/aFl598epAdUrrv0y%40nathan

-- 
nathan

Re: problems with toast.* reloptions

Michael Paquier <michael@paquier.xyz> — 2025-06-24T05:10:55Z

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] https://postgr.es/m/aFl598epAdUrrv0y%40nathan

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-24T16:38:41Z

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2025-06-24T18:21:56Z

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

Re: problems with toast.* reloptions

shihao zhong <zhong950419@gmail.com> — 2025-07-02T14:44:05Z

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

Re: problems with toast.* reloptions

Shayon Mukherjee <shayonj@gmail.com> — 2025-07-15T18:50:01Z

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] 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]. 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]. 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]
https://www.postgresql.org/message-id/CANqtF-pLHBDdNM-DifXQqVq%2BVWA3DTYOx4%2B3m2%2BTFJ1zDOZETg%40mail.gmail.com

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

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2026-05-01T21:41:12Z

On Tue, Jun 24, 2025 at 11:38:41AM -0500, Nathan Bossart wrote:
> 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.

For the sake of discussion, I wrote a patch for this.  I suggested removing
toast.* relopts to some colleagues recently, and nobody was aware of much
(if any) use in the field.  So maybe this isn't totally
out-of-the-question...

-- 
nathan

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2026-05-04T15:41:15Z

On Fri, May 01, 2026 at 04:41:12PM -0500, Nathan Bossart wrote:
> On Tue, Jun 24, 2025 at 11:38:41AM -0500, Nathan Bossart wrote:
>> 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.
> 
> For the sake of discussion, I wrote a patch for this.  I suggested removing
> toast.* relopts to some colleagues recently, and nobody was aware of much
> (if any) use in the field.  So maybe this isn't totally
> out-of-the-question...

Apparently I forgot to test it with injection points enabled.  Here's a new
patch.

-- 
nathan

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2026-05-06T21:31:46Z

On Mon, May 04, 2026 at 10:41:15AM -0500, Nathan Bossart wrote:
> Apparently I forgot to test it with injection points enabled.  Here's a new
> patch.

v4 fixes a small bug in pg_upgrade's new check query.

-- 
nathan

Re: problems with toast.* reloptions

solai v <solai.cdac@gmail.com> — 2026-05-26T05:55:39Z

Hi,

I tested the TOAST reloptions behavior locally on current HEAD and was
able to reproduce the inheritance and RESET scenarios discussed in the
thread.
>From my testing, explicit toast.reloptions behave independently from
parent reloptions. After setting both parent and TOAST vacuum_truncate
values, RESET(vacuum_truncate) on the parent table cleared only the
parent reloption while the explicit TOAST reloption remained intact.
I also tried applying the v1-0004 patch in a clean worktree, but most
hunks in vacuum.c and autovacuum.c no longer apply cleanly against
current HEAD due to code drift. From inspecting the rejected hunks, it
looks like the patch approach was to dynamically combine parent and
TOAST reloptions during VACUUM/autovacuum execution instead of copying
inherited reloptions directly into the TOAST relation.

Regards,
solai

Re: problems with toast.* reloptions

Nathan Bossart <nathandbossart@gmail.com> — 2026-05-26T14:23:11Z

On Tue, May 26, 2026 at 11:25:39AM +0530, solai v wrote:
> I tested the TOAST reloptions behavior locally on current HEAD and was
> able to reproduce the inheritance and RESET scenarios discussed in the
> thread.
> From my testing, explicit toast.reloptions behave independently from
> parent reloptions. After setting both parent and TOAST vacuum_truncate
> values, RESET(vacuum_truncate) on the parent table cleared only the
> parent reloption while the explicit TOAST reloption remained intact.
> I also tried applying the v1-0004 patch in a clean worktree, but most
> hunks in vacuum.c and autovacuum.c no longer apply cleanly against
> current HEAD due to code drift. From inspecting the rejected hunks, it
> looks like the patch approach was to dynamically combine parent and
> TOAST reloptions during VACUUM/autovacuum execution instead of copying
> inherited reloptions directly into the TOAST relation.

I humbly encourage you to read the rest of the thread.  In particular, I'm
curious whether anyone would object to removing the TOAST reloptions.

-- 
nathan