Thread: [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE

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,

Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> 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.

This isn't particularly RETURN QUERY's fault; there are any number of ways
to produce the same sort of error.  I reproduced it with

        declare r record;
        ...
        for r in SELECT str FROM temp_rel loop
          return next r.str;
        end loop;

as well as

        declare r record;
        ...
        SELECT str INTO r FROM temp_rel LIMIT 1;
    
        DROP TABLE temp_rel;
        --TRUNCATE TABLE temp_rel;

        return r.str;

I guess we could forcibly detoast values in enough places to close all the
gaps, but the performance costs might be annoying.  I think a case can
definitely be made for saying "don't do that".

(Another idea, perhaps, might be to detoast only in volatile functions,
reasoning that a nonvolatile one can't drop the table.)

            regards, tom lane

On Sat, 29 Aug 2020 13:47:19 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jehan-Guillaume de Rorthais <jgdr@dalibo.com> writes:
> > 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.  
> 
> This isn't particularly RETURN QUERY's fault; there are any number of ways
> to produce the same sort of error.  I reproduced it with
> [...]

Indeed. Thanks.

> I guess we could forcibly detoast values in enough places to close all the
> gaps, but the performance costs might be annoying.  I think a case can
> definitely be made for saying "don't do that".

What do you mean? Writing in configuration to not drop a relation in a function
where some past computed results depend on it?

> (Another idea, perhaps, might be to detoast only in volatile functions,
> reasoning that a nonvolatile one can't drop the table.)

Would it be possible to detoast values iif the related relation are dropped
later?

Regards,