pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jehan-Guillaume de Rorthais <jgdr@dalibo.com>
To: pgsql-bugs@lists.postgresql.org
Subject: [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
Date: Fri, 28 Aug 2020 17:43:42 +0200
Message-ID: <20200828174342.2ac34e27@firost> (raw)

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,





view thread (3+ messages)  latest in thread

Message-ID: <20200828174342.2ac34e27@firost>
Permalink:  ../20200828174342.2ac34e27@firost/
Also on:    postgresql.org/message-id/20200828174342.2ac34e27@firost

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: jgdr@dalibo.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: [BUG] plpgsql RETURN QUERY with toasted fields -vs- DROP/TRUNCATE
  In-Reply-To: <20200828174342.2ac34e27@firost>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox