Re: Bug with plpgsql, temp tables and TOAST?

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.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support