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