Re: Counterintuitive behavior when toast_tuple_target < TOAST_TUPLE_THRESHOLD

Hi!

As it is seen from the code (toasting.c and further) Toast tables are
created immediately
when a new relation with the TOASTable column is created. Practically,
there could occur
the case when Toast table does not exist and we should of course check for
that.

TOAST_TUPLE_THRESHOLD is not only one which decides should be value stored
externally, this is slightly more complex and less obvious logic:
(see heapam.c, heap_prepare_insert())
else if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD)

as you can see here is another condition - HeapTupleHasExternal, which is
set in
heap_fill_tuple and lower in fill_val, where the infomask bit
HEAP_HASEXTERNAL is set.

So when I experimented with the TOAST I'd to add a new flag which forced
the value to be
TOASTed regardless of its size.

Also, TOAST_TUPLE_THRESHOLD sets overall tuple size over which it would be
considered
to be toasted, and has its minimum value that could not be decreased
further.

In [1] (the Pluggable TOAST) we suggest making this an ontion for Toaster.

[1]
https://www.postgresql.org/message-id/flat/224711f9-83b7-a307-b17f-4457ab73aa0a(at)sigaev(dot)ru

On Thu, Sep 15, 2022 at 3:05 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Thu, 15 Sept 2022 at 04:04, Aleksander Alekseev
> <aleksander(at)timescale(dot)com> wrote:
> > 1. Forbid setting toast_tuple_target < TOAST_TUPLE_THRESHOLD
> > 2. Consider using something like RelationGetToastTupleTarget(rel,
> > TOAST_TUPLE_THRESHOLD) in heapam.c:2250, heapam.c:3625 and
> > rewriteheap.c:636 and modify the documentation accordingly.
> > 3. Add a separate user-defined table setting toast_tuple_threshold
> > similar to toast_tuple_target.
> >
> > Thoughts?
>
> There was some discussion on this problem in [1].
>
> The problem with #2 is that if you look at
> heapam_relation_needs_toast_table(), it only decides if the toast
> table should be created based on (tuple_length >
> TOAST_TUPLE_THRESHOLD). So if you were to change the logic as you
> describe for #2 then there might not be a toast table during an
> INSERT/UPDATE.
>
> The only way to fix that would be to ensure that we reconsider if we
> should create a toast table or not when someone changes the
> toast_tuple_target reloption. That can't be done under
> ShareUpdateExclusiveLock, so we'd need to obtain an
> AccessExclusiveLock instead when changing the toast_tuple_target
> reloption. That might upset some people.
>
> The general direction of [1] was to just increase the minimum setting
> to TOAST_TUPLE_THRESHOLD, but there were some concerns about breaking
> pg_dump as we'd have to error if someone does ALTER TABLE to set the
> toast_tuple_target reloption lower than the newly defined minimum
> value.
>
> I don't quite follow you on #3. If there's no toast table we can't toast.
>
> David
>
> [1]
> https://www.postgresql.org/message-id/20190403063759.GF3298@paquier.xyz
>
>
>

--
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/