Re: Caveats from reloption toast_tuple_target

On Thu, Apr 4, 2019 at 11:36 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

>
>
> I mean that toast_tuple_target is broken as-is, because it should be
> used on the new tuples of a relation as a threshold to decide if this
> tuple should be toasted or not, but we don't actually use the
> reloption value for that decision-making: the default threshold
> TOAST_TUPLE_THRESHOLD gets used instead all the time! The code does
> not even create a toast table in some cases.
>

I think the problem with the existing code is that while it allows users to
set toast_tuple_target to be less than TOAST_TUPLE_THRESHOLD, the same is
not honoured while deciding whether to toast a row or not. AFAICS it works
ok when toast_tuple_target is greater than or equal to
TOAST_TUPLE_THRESHOLD i.e. it won't toast the rows unless they are larger
than toast_tuple_target.

IMV it makes sense to simply cap the lower limit of toast_tuple_target to
the compile time default and update docs to reflect that. Otherwise, we
need to deal with the possibility of dynamically creating the toast table
if the relation option is lowered after creating the table. Your proposed
patch handles the case when the toast_tuple_target is specified at CREATE
TABLE, but we would still have problem with ALTER TABLE, no? But there
might be side effects of changing the lower limit for pg_dump/pg_restore.
So we would need to think about that too.

Thanks,
Pavan

--
Pavan Deolasee http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services