获得“无法开放的关系”简单查询的错误

Question

我的函数可以将一组插入到...值脚本中。如果我取消dvp.content行，则该函数会以“错误：无法与oid ###”打开关系，该功能是指临时表。 content 列是JSONB类型。不确定从哪里开始？

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;

Answer 1

由于描述的错误这里，这仍然是Postgres 14.0的问题：

似乎正在发生的事情是，如果字符串足够大，可以
敬酒，然后数据返回函数，并带有返回查询
包含敬酒指针引用temp表的 toast table 。
如果您放下临时表，那么这些指针会在使用时失败。

为了进一步解释，当列值大于 toast_tuple_threshold 配置参数（通常是2KB）时，无法压缩或使用列配置外部，的存储参数时该值将分解成块，并存储在一个称为吐司表的特殊辅助表中。该表将存储在 pg_toast 架构中，并将命名为 pg_toast.pg_toast_＆lt; table oid＆gt; 。

因此，当您将 dvp.content 添加到SQL语句中时，您将其插入 doc_data_export 中时，其中一些值大于上述约束，因此被敬酒。您的返回查询仅将指针发送到吐司表中的值。返回完成后，临时表及其相应的吐司表被删除。因此，当外部查询试图实现结果时，它找不到这些指针参考的吐司表 - 因此，您看到的隐秘错误消息。

您可以避免为临时表发送吐司指针 - 因此，在返回查询 - 通过在 sql 列上执行一个返回相同值的操作后，将其放置

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

在下面的简单功能将在将设置为 true 时，将重现吐司错误的最小示例代码> 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);