Skip site navigation (1) Skip section navigation (2)

Re: Bug with plpgsql, temp tables and TOAST?

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 (view raw or flat)
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

In response to

Responses

pgsql-bugs by date

Next:From: Magnus HaganderDate: 2011-06-29 17:38:38
Subject: Re: BUG #6082: server segfault - pg_stat_reset_shared(null)
Previous:From: Olivier LEVESQUEDate: 2011-06-29 15:00:40
Subject: BUG #6085: pg_upgrade fails when unix_socket_directory != /tmp

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group