From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Matthijs Bomhoff <matthijs(at)quarantainenet(dot)nl> |
Cc: | pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Bug with plpgsql, temp tables and TOAST? |
Date: | 2011-06-29 16:03:20 |
Message-ID: | 1309363131-sup-6268@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2011-06-29 17:38:38 | Re: BUG #6082: server segfault - pg_stat_reset_shared(null) |
Previous Message | Olivier LEVESQUE | 2011-06-29 15:00:40 | BUG #6085: pg_upgrade fails when unix_socket_directory != /tmp |