Getting A "Could Not Open Relation" Error On Simple Query-CodePudding

I have a function that creates a set of INSERT INTO ... VALUES scripts. If I uncomment the dvp.content line, the function fails with an "ERROR: could not open relation with OID ###", which refers to the temp table. The content column is a jsonb type. Not sure where to begin?

CREATE OR REPLACE FUNCTION export_docs_as_sql(doc_list uuid[], to_org_id uuid)
RETURNS table(id integer, sql text)
AS $$
BEGIN
    ...

    -- use a temp table to gather all INSERT statements
    CREATE TEMP TABLE IF NOT EXISTS doc_data_export(
        id serial PRIMARY KEY,
        sql text
    );
    
    ...
    
    -- get doc_version_pages
    INSERT INTO doc_data_export(sql)
    SELECT 'INSERT INTO doc_version_pages(id, doc_version_id, persona_id, care_category_id, patient_group_id, title, content, created_at, updated_at, is_guide, is_root) VALUES (' ||
    quote_literal(dvp.id::TEXT) || ', ' || 
    quote_literal(dvp.doc_version_id::TEXT) || ', ' || 
    CASE WHEN p.name IS NOT NULL THEN '(SELECT px.id FROM personas px WHERE px.org_id = ' || quote_literal(dv.id::TEXT) || ' AND px.name = ' || quote_literal(p.name) || '), ' ELSE 'NULL, ' END ||
    CASE WHEN c.name IS NOT NULL THEN '(SELECT cx.id FROM care_categories cx WHERE cx.org_id = ' || quote_literal(to_org_id) || ' AND cx.name = ' || quote_literal(c.name) || '), ' ELSE 'NULL, ' END ||
    CASE WHEN g.name IS NOT NULL THEN '(SELECT gx.id FROM patient_groups gx WHERE gx.org_id = ' || quote_literal(to_org_id) || ' AND gx.name = ' || quote_literal(g.name) || '), ' ELSE 'NULL, ' END ||
    quote_literal(dvp.title::TEXT) || ', ' || 
    --dvp.content || ', ' || 
    quote_literal(dvp.created_at::TEXT) || ', ' ||
    quote_literal(now()::timestamp) || ', ' ||
    quote_literal(dvp.is_guide::TEXT) || ', ' ||
    quote_literal(dvp.is_root::TEXT) || ');' 
    FROM unnest(doc_list) l
    INNER JOIN doc_versions dv ON l = dv.doc_id
    INNER JOIN doc_version_pages dvp ON dv.id = dvp.doc_version_id
    LEFT JOIN personas p ON dvp.persona_id = p.id
    LEFT JOIN care_categories c ON dvp.care_category_id = c.id
    LEFT JOIN patient_groups g ON dvp.patient_group_id = g.id;
    
    ...

    -- output all inserts
    RETURN QUERY SELECT * FROM doc_data_export;

    -- drop temp table
    DROP TABLE doc_data_export;
END;
$$ LANGUAGE plpgsql;

CodePudding user response：

The "Could Not Open Relation" problem is occurring due to the bug described here, which remains an issue as of Postgres 14.0:

What seems to be happening is that if the strings are large enough to be toasted, then the data returned out of the function with RETURN QUERY contains toast pointers referencing the temp table's toast table. If you drop the temp table then those pointers will fail upon use.

To explain further, when a column value is greater than the TOAST_TUPLE_THRESHOLD configuration parameter (usually 2KB) and cannot be compressed or when the column is configured with a storage parameter of EXTERNAL, the value will be broken down into chunks and stored in a special secondary table called a TOAST table. This table will be stored in the pg_toast schema and will be named like pg_toast.pg_toast_<table OID>.

So when you add dvp.content to the sql statement you insert that into doc_data_export, some of these values are larger than the aforementioned constraints and are thus TOASTed. Your RETURN QUERY is only sending the pointers to the values in the toast table. After the return is done, the temporary table and its corresponding TOAST table is dropped. Thus when the outer query attempts to materialize the results, it can't find the TOAST table that these pointers reference - hence the cryptic error message you see.

You can avoid sending TOAST pointers for the temporary table -and thus safely DROP it after the RETURN QUERY -by performing an operation on the sql column that returns the same value:

RETURN QUERY SELECT id, sql || '' FROM doc_data_export;

The simple function below will reproduce a minimal example of the TOAST bug when you set fail to true and demonstrate the successful workaround when you set fail to false.

DROP FUNCTION IF EXISTS buttered_toast(boolean);
CREATE OR REPLACE FUNCTION buttered_toast(fail boolean)
    RETURNS table(id integer, enormous_data text)
AS $$
BEGIN
    CREATE TEMPORARY TABLE tbl_with_toasts (
        id integer PRIMARY KEY,
        enormous_data text
    ) ON COMMIT DROP;

    --generate a giant string that is sure to generate a TOAST table.
    INSERT INTO tbl_with_toasts(id,enormous_data) SELECT 1, string_agg(gen_random_uuid()::text,'-') FROM generate_series(1,10000) as ints(int);

    IF buttered_toast.fail THEN
        -- will return pointers to tbl_with_toast's TOAST table for the "enormous_data" column.
        RETURN QUERY SELECT tbl_with_toasts.id, tbl_with_toasts.enormous_data FROM tbl_with_toasts ;
    ELSE
        -- will generate and return new values for the "enormous_data" column 
        RETURN QUERY SELECT tbl_with_toasts.id, tbl_with_toasts.enormous_data || '' FROM tbl_with_toasts ;
    END IF;

    DROP TABLE tbl_with_toasts;
END;
$$ LANGUAGE plpgsql;

-- fails with "Could Not Open Relation"
select * from buttered_toast(true)

--succeeds
select * from buttered_toast(false);