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=E9 jun 29 07:40:07 -0400 2=
011:
>=20
>> CREATE OR REPLACE FUNCTION foo(size_ INTEGER) RETURNS TEXT AS $EOF$
>> DECLARE
>>  acc_ TEXT :=3D '';
>>  cur_rec_ RECORD;
>> BEGIN
>>  EXECUTE 'CREATE TEMPORARY TABLE foo_tab(blob TEXT NOT NULL)';
>>=20
>>  -- Construct a string with random characters to prevent compression (wi=
th high probability)
>>  LOOP
>>    EXIT WHEN length(acc_) >=3D size_;
>>    acc_ :=3D acc_ || chr(ceil(random()*64)::integer + 32);
>>  END LOOP;
>>=20
>>  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;
>=20
> 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 t=
o trigger with that. Just selecting * triggers it of course, but caused a b=
it too much clutter in my psql for the required long random strings, so I h=
ad to find a function that would actually need the value itself. Hence the =
md5().

Regards,

Matthijs