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

Bug with plpgsql, temp tables and TOAST?

From: Matthijs Bomhoff <matthijs(at)quarantainenet(dot)nl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug with plpgsql, temp tables and TOAST?
Date: 2011-06-29 11:40:07
Message-ID: 0C41674C-FA02-4768-9E1B-548E568875BD@quarantainenet.nl (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi,

When I run the SQL below, I get an error on the third call to foo() : "ERROR:  could not open relation with OID 884693". I'm quite sure this OID belongs 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 compressed) to be stored without using toast. I can't reproduce the issue if for example I use a long string of identical characters instead of "random" ones. 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 by someone on #postgresql.

Kind regards,

Matthijs Bomhoff



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;

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




Responses

pgsql-bugs by date

Next:From: Steve HaslamDate: 2011-06-29 13:31:49
Subject: BUG #6082: server segfault - pg_stat_reset_shared(null)
Previous:From: Tom HodderDate: 2011-06-29 06:56:40
Subject: BUG #6081: trigger CRUD log entries, or documentation not clear that triggers do not log

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