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
