Bug with plpgsql, temp tables and TOAST?

Hi,

When I run the SQL below, I get an error on the third call to foo() : "ERRO=
R:  could not open relation with OID 884693". I'm quite sure this OID belon=
gs to the TOAST table corresponding to the temporary table created by foo()=
 during the third call. The fourth call works fine again.

I suspect the third one fails because the data is too large (even when comp=
ressed) to be stored without using toast. I can't reproduce the issue if fo=
r example I use a long string of identical characters instead of "random" o=
nes. My guess would be that the TOAST table is still somehow referenced by =
the result value, even though the table itself has been dropped by the time=
 the result value is used.

I have tested this myself on 8.4.4 and it has also been verified on a 9.1 b=
y someone on #postgresql.

Kind regards,

Matthijs Bomhoff



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)';

  -- Construct a string with random characters to prevent compression (with=
 high probability)
  LOOP
    EXIT WHEN length(acc_) >=3D size_;
    acc_ :=3D 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;

SELECT md5(foo(10));
SELECT md5(foo(20));
SELECT md5(foo(40000)); -- This one breaks on my 8.4.4
SELECT md5(foo(30)); -- And this one works fine again

DROP FUNCTION foo(INTEGER);