Thread: Re: problems with toast.* reloptions

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