我有一個創建一組 INSERT INTO ... VALUES 腳本的函式。如果我取消注釋 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;
uj5u.com熱心網友回復：
由于此處描述的錯誤，正在發生“無法打開關系”問題，這在 Postgres 14.0 中仍然是一個問題：
似乎正在發生的事情是，如果字串大到可以烘烤，那么使用 RETURN QUERY 的函式回傳的資料包含參考臨時表的toast 表的 toast 指標。如果您洗掉臨時表，那么這些指標將在使用時失敗。
進一步說明，當列值大于TOAST_TUPLE_THRESHOLD配置引數（通常為 2KB）且無法壓縮時，或者當列配置了存盤引數 時EXTERNAL，該值將被分解為塊并存盤在一個名為一個 TOAST 表。該表將存盤在pg_toast模式中，并將命名為pg_toast.pg_toast_<table OID>.
因此，當您添加dvp.content到將其插入的 sql 陳述句中時doc_data_export，其中一些值大于上述約束，因此會被 TOASTed。您RETURN QUERY只是將指標發送到 toast 表中的值。回傳完成后，將洗掉臨時表及其對應的 TOAST 表。因此，當外部查詢嘗試具體化結果時，它找不到這些指標參考的 TOAST 表 - 因此您會看到神秘的錯誤訊息。
您可以避免為臨時表發送 TOAST 指標 - 從而在- 通過對回傳相同值RETURN QUERY的列執行操作后安全地洗掉它：sql
RETURN QUERY SELECT id, sql || '' FROM doc_data_export;
下面的簡單函式將在您設定為時重現 TOAST 錯誤的最小示例，并在您設定為時fail演示true成功的解決fail方法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);
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/494844.html
標籤：PostgreSQL 例外 jsonb 临时表
上一篇：以太坊交易（呼叫合約函式）是否會因assert()/require()/revert()或gas問題以外的原因而失敗？