Hi,
We discovered a bug in plpgsql.
When using RETURN QUERY on a relation with some toasted values and when this
relaiton is later dropped or truncated, an error is raised at the end of the
function.
Consider the following reproduction test:
BEGIN;
CREATE TABLE IF NOT EXISTS temp_rel ( str text );
-- feed enough data to toast the field
INSERT INTO temp_rel
SELECT string_agg(chr((33+random()*93)::int),'') AS v
FROM generate_series(1,16000);
CREATE OR REPLACE FUNCTION testcase(OUT res text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY SELECT str FROM temp_rel;
DROP TABLE temp_rel;
--TRUNCATE TABLE temp_rel;
END
$$;
SELECT * FROM testcase();
ROLLBACK;
When using "DROP TABLE", the function finishes with this error:
ERROR: could not open relation with OID xxx
WHEN using "TRUNCATE", it finishes with:
ERROR: XX001: missing chunk number 0 for toast value xxx in yyy
Both have the same stack path leading to pg_detoast_datum_packed (or
pg_detoast_datum with a large array of smaller values). Eg.:
[...]
#2 toast_fetch_datum at detoast.c:352
#3 detoast_attr at detoast.c:122
#4 pg_detoast_datum_packed at fmgr.c:1761
#5 text_to_cstring at varlena.c:208
#6 textout at varlena.c:557
#7 FunctionCall1Coll at fmgr.c:1142
#8 OutputFunctionCall at fmgr.c:1579
#9 printtup at printtup.c:434
#10 ExecutePlan at execMain.c:1677
#11 standard_ExecutorRun at execMain.c:364
#12 ExecutorRun at execMain.c:308
[...]
So I suppose the tuplestore built during the function call is keeping
references to toasted values. As soon as the function is ready to output the
result, it fails fetching the toasted values if the relation has been truncated
or dropped in its body some time after the tuplesort has been built.
Regards,
