Re: ERROR: missing chunk number 0 for toast value

On Fri, Jan 3, 2014 at 12:51 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
> On 01/02/2014 02:24 PM, Rushabh Lathia wrote:
>>
>> Hi All,
>>
>> Test case:
>>
>> drop table if exists t;
>> create table t(c text);
>> insert into t values ('x'), (repeat(md5('abcdefghijklmnop'), 10000));
>> select pg_column_size(c), pg_column_size(c || '') FROM t;
>>
>> CREATE OR REPLACE FUNCTION copy_toast_out() RETURNS VOID AS $$
>> declare
>>          v text;
>> BEGIN
>>          SELECT c INTO v FROM t WHERE c <> 'x';
>>      Select 1/0;
>> Exception
>>          When Others Then
>>          PERFORM pg_sleep(30); -- go run "TRUNCATE t" in a 2nd session
>>
>>
>>          raise notice 'length :%', length(v || ''); -- force detoast
>>
>>
>> END;
>> $$ language plpgsql;
>>
>> postgres=# select copy_toast_out();
>> ERROR:  missing chunk number 0 for toast value 16390 in pg_toast_16384
>> CONTEXT:  PL/pgSQL function copy_toast_out() line 10 at RAISE
>>
>> Analysis:
>>
>> The basic problem here is that if the lock is released on table before
>> extracting toasted value, and in meantime someone truncates the table,
>> this error can occur.  Here error coming with PL block contains an
>> Exception
>> block (as incase there is an exception block, it calls
>> RollbackAndReleaseCurrentSubTransaction).
>
>
> This is another variant of the bug discussed here:
> http://www.postgresql.org/message-id/0C41674C-FA02-4768-9E1B-548E568875BD@quarantainenet.nl.
>
>
>> Do you think we should detoast the local variable before
>>   RollbackAndReleaseCurrentSubTransaction ? Or any other options ?
>
>
> Hmm, that would fix this particular test case, but not the other case where
> you DROP or TRUNCATE the table in the same transaction.
>
> The simplest fix would be to just detoast everything on assignment but that
> was rejected on performance grounds in that previous thread. I don't see any
> other realistic way to fix this, however, so maybe we should just bite the
> bullet and do it anyway. For simple variables like, in your test case, it's
> a good bet to detoast the value immediately; it'll be detoasted as soon as
> you try to do anything with it anyway. But it's not a good bet for record or
> row variables, because you often fetch the whole row into a variable but
> only access a field or two.

Yeah, this is exactly what came to my mind as well the first time I saw this
problem that for row and record variables it can be penalty which user might
not expect as he might not be using toasted values.

However is it possible that we do detoasting on assignment when the
variable of function is declared with some specific construct.
For example, we do detoasting at commit time for holdable portals
(referred below code)

/*
* Change the destination to output to the tuplestore. Note we tell
* the tuplestore receiver to detoast all data passed through it.
*/
queryDesc->dest = CreateDestReceiver(DestTuplestore);
SetTuplestoreDestReceiverParams(..);

When the Hold option is specified with cursor, then we perform
detoast on commit, so on similar lines if the specific variable or
function is declared with some particular construct, then we detoast
on assignment.

Another option is that we give more meaningful error with Hint
suggesting the possible reason of error.
This option can be used along with above option in case
variable/function is not declared with particular construct.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com