Re: ERROR: missing chunk number 0 for toast value

On 1/6/14, 2:21 PM, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
>> On 2014-01-06 12:40:25 -0500, Robert Haas wrote:
>>> Is "forcibly detoast everything" a complete no-go? I realize there
>>> are performance concerns with that approach, but I'm not sure how
>>> realistic a worry it actually is.
>
>> The scenario I am primarily worried about is turning a record assignment
>> which previously took up to BLOCK_SIZE + slop amount of memory into
>> something taking up to a gigabyte. That's a pretty damn hefty
>> change.
>> And there's no good way of preventing it short of using a variable for
>> each actually desired column which imnsho isn't really a solution.
>
> Dunno ... if you have a table that contains a gigabyte-width column,
> should you be all that surprised if "SELECT * INTO r FROM table"
> results in "r" occupying about a gigabyte? And I can't count the
> number of times I've heard people deprecate using "SELECT *" at all
> in production code, so I don't agree with the claim that listing the
> columns you want is an unacceptable solution.

I see your logic, but the problem is a good developer would have actually tested that case and said "Oh look, plpgsql isn't blindly copying the entire record." Now we're changing that case underneath them. That's a pretty significant change that could affect a LOT of code on the user's side. And if they've got conditional code down-stream that sometimes hits the TOASTed value and sometimes doesn't then they're in for even more fun...

The deferred access pattern of detoasting is a very powerful performance improvement and I'd hate to see us limiting it in plpgsql.
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net