Re: Bug with plpgsql, temp tables and TOAST?

On Jun 29, 2011, at 6:03 PM, Alvaro Herrera wrote:

> Excerpts from Matthijs Bomhoff's message of mié jun 29 07:40:07 -0400 2011:
>
>> CREATE OR REPLACE FUNCTION foo(size_ INTEGER) RETURNS TEXT AS $EOF$
>> DECLARE
>> acc_ TEXT := '';
>> cur_rec_ RECORD;
>> BEGIN
>> EXECUTE 'CREATE TEMPORARY TABLE foo_tab(blob TEXT NOT NULL)';
>>
>> -- Construct a string with random characters to prevent compression (with high probability)
>> LOOP
>> EXIT WHEN length(acc_) >= size_;
>> acc_ := acc_ || chr(ceil(random()*64)::integer + 32);
>> END LOOP;
>>
>> EXECUTE 'INSERT INTO foo_tab(blob) values (' || quote_literal(acc_) || ')';
>> EXECUTE 'SELECT * FROM foo_tab LIMIT 1' INTO cur_rec_;
>> EXECUTE 'DROP TABLE foo_tab';
>> RETURN cur_rec_.blob;
>> END
>> $EOF$ LANGUAGE plpgsql;
>
> Hmm, so what's happening here, I think, is that the value is getting
> assigned to the record variable without detoasting. I guess we should
> detoast the value prior to assigning it, but it seems to me that that
> would have a large performance penalty for other cases in which the
> toast table is not dropped; in fact, you can even imagine some cases in
> which the toasted value is not even accessed, so getting to the point of
> detoasting it would be a severe penalization.

Possibly related: In some earlier attempts at reproducing this, I actually tried to use length() instead of md5(). It seemed that I could not get it to trigger with that. Just selecting * triggers it of course, but caused a bit too much clutter in my psql for the required long random strings, so I had to find a function that would actually need the value itself. Hence the md5().

Regards,

Matthijs