Re: Counterintuitive behavior when toast_tuple_target < TOAST_TUPLE_THRESHOLD

Hi David,

> There was some discussion on this problem in [1].
> [1] https://www.postgresql.org/message-id/20190403063759.GF3298@paquier.xyz

Thanks for sharing this discussion. I missed it.

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

Personally, if I would choose between (A) a feature that is
potentially expensive but useful to many and (B) a feature that in
practice is pretty much useless to most of the users, I would choose
(A). Maybe we will be able to make it a little less expensive if we
optimistically take a shared lock first and then, if necessary, take
an exclusive lock.

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

Yep, this doesn't seem to be an option.

> I don't quite follow you on #3. If there's no toast table we can't toast.

The case I had in mind was the one when we already have a TOAST table
and then change toast_tuple_target.

In this scenario TOAST_TUPLE_THRESHOLD is used to decide whether TOAST
should be triggered for a given tuple. For how long TOAST will keep
compressing the tuple is controlled by toast_tuple_target, not by
TOAST_TUPLE_THRESHOLD. So the user has control of "target" but there
is no control of "threshold". If the user could set both "threshold"
and "target" low this would solve the problem the user originally had
(the one described in the first email).

Adding a separate "threshold" option doesn't solve the problem when we
change it and there is no TOAST table yet.

I wonder though if we really need two entities - a "threshold" and a
"target". It seems to me that it should actually be one value, no?

--
Best regards,
Aleksander Alekseev