Re: lazy detoasting

On 5/1/18 19:56, Andrew Gierth wrote:
> Peter> insert into test1 values (1, repeat('foo', 2000));
>
> That value is no good because it's too compressible; it'll be left
> inline in the main table rather than being externalized, so the value of
> 'x' in the DO-block is still self-contained (though it's still toasted
> in the sense of being VARATT_IS_EXTENDED).

Right. I added

alter table test1 alter column b set storage external;

then I can see the error.

The attached test fixes this issue by flattening the toast values before
storing them into PL/pgSQL variables. It could use another check to see
if there are other code paths that need similar adjustments, but I think
it's the right idea in general.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services