Re: Caveats from reloption toast_tuple_target

On Tue, 16 Apr 2019 at 23:30, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> wrote:
> I've attached a patch which increases the lower limit up to
> TOAST_TUPLE_TARGET. Unfortunately, reloptions don't have an
> assign_hook like GUCs do. Unless we add those we've no way to still
> accept lower values without an error. Does anyone think that's worth
> adding for this? Without it, it's possible that pg_restore /
> pg_upgrade could fail if someone happened to have toast_tuple_target
> set lower than 2032 bytes.

Hi Michael,

I'm just wondering if you've had any thoughts on this?

To recap, Pavan and I think it's a problem to allow the
toast_tuple_target to be reduced as the relation in question may not
have a toast table defined. It does not seem very possible to call
create_toast_table() when the toast_tuple_target is changed since we
only obtain a ShareUpdateExclusiveLock when doing that.

The options seem to be:
1. Make the lower limit of toast_tuple_target the same as
TOAST_TUPLE_THRESHOLD; or
2. Require an AccessExclusiveLock when setting toast_tuple_target and
call create_toast_table() to ensure we get a toast table when the
setting is reduced to a level that means the target table may require
a toast relation.

I sent a patch for #1, but maybe someone thinks we should do #2? The
reason I've not explored #2 more is that after reading the original
discussion when this patch was being developed, the main interest
seemed to be keeping the values inline longer. Moving them out of
line sooner seems to make less sense since smaller values are less
likely to compress as well as larger values.

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