Re: Caveats from reloption toast_tuple_target

On Fri, 5 Apr 2019 at 17:31, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> 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.

FWIW I independently stumbled upon this problem today and I concluded
the same thing, we can only make the lower limit for the
toast_tuple_threshold reloption the same as TOAST_TUPLE_THRESHOLD. (I
was unaware of this thread, so I reported in [1])

I only quickly looked at Michael's patch and it does not seem to do
anything for the case that if no toast table exists and the user
lowers the reloption, then nothing seems to be there to build a new
toast table.

I mentioned over in [1] that:
> It does not seem possible to add/remote the toast table when the
> reloption is changed either as we're only obtaining a
> ShareUpdateExclusiveLock to set it. We'd likely need to upgrade that
> to an AccessExclusiveLock to do that.

Reading over the original discussion in [2], Simon seemed more
interested in delaying the toasting for tuples larger than 2040 bytes,
not making it happen sooner. This makes sense since smaller datums are
increasingly less likely to compress the smaller they are.

The question is, can we increase the lower limit. We don't want
pg_upgrade or pg_dump reloads failing from older versions. Perhaps we
can just silently set the reloption to TOAST_TUPLE_THRESHOLD when the
user gives us some lower value. At least then lower values would
disappear over time.

[1] https://www.postgresql.org/message-id/CAKJS1f9vrJ55oYe7un+rakTzwaGh3my5MA0RBfyNngAXu7eVeQ@mail.gmail.com
[2] https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com

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