函式使用兩次時的 PL/pgSQL 問題（記憶體問題？）

我面臨一個絕對奇怪的問題，感覺更像是 Postgres 錯誤而不是算法問題。

我有這個功能：

CREATE FUNCTION sp_connect(mail character varying, passwd character varying, role character varying)
 RETURNS json LANGUAGE plpgsql STABLE AS
$$
DECLARE
   user_info record;
BEGIN
 IF role = 'Role1' THEN
   SELECT u.id, r.name INTO user_info
   FROM users u
   INNER JOIN users_roles ur ON ur.user_id = u.id
   INNER JOIN roles r ON ur.role_id = r.id
   WHERE u.email = mail
     AND u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
     AND r.name = 'Role1';
 ELSIF role = 'Role2' THEN
   SELECT h.id, 'Role1' AS name INTO user_info
   FROM history h
   WHERE h.email = mail
     AND h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');
 ELSE
   RAISE 'USER_NOT_FOUND';
 END IF;

 IF NOT FOUND THEN
     RAISE 'USER_NOT_FOUND';
 ELSE
     RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
 END IF;
END;
$$;

我面臨的問題是，當我使用此功能以 Role1 使用者登錄時，當我將其與 Role2 使用者一起使用時，我收到以下錯誤消息：

type of parameter 7 (character varying) does not match that when preparing the plan (unknown)

這是……好吧，我只是不明白它來自哪裡。如果您擦除數據庫並更改登錄順序（即 Role2 然後 Role1），這一次，Role1 得到錯誤。

奇怪的問題，奇怪的解決方案……如果我只是使用ALTER FUNCTION sp_connect但沒有修改函式內部的任何內容，那麼神奇地，兩個角色可以毫無問題地登錄。我也試過這個解決方案：

 IF NOT FOUND THEN
     RAISE 'USER_NOT_FOUND';
 ELSE
     IF role = 'Seeker'
     THEN
         RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
     ELSE
         RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
 END IF;

IF並且通過添加一個ELSE絕對沒用的 and 並使用相同的 RETURN 子句，這不會觸發任何錯誤。

我知道 DBA StackExchange 不適合開發人員，但這種問題似乎更像是記憶體問題或其他問題。有人可以告訴我我是否對 PostgreSQL 函式做錯了什麼嗎？或者我可以在哪裡獲得解決這個奇怪問題的幫助？

解釋：

您聲明user_info為record. 手冊：

記錄變數類似於行類型變數，但它們沒有預定義的結構。它們採用在SELECTorFOR命令期間分配的行的實際行結構。每次將記錄變數分配給 時，它的子結構都會發生變化。

大膽強調我的。

您分配記錄user_info，然後在語句中從中派生一個行類型（實際上row在您的程式碼中呼叫） 。RETURN這一切都很好而且很花哨，直到您在同一會話中的下一次呼叫中將*不同數據類型的列分配給記錄。*這與準備好的語句的記憶體查詢計劃不兼容並引發異常。

PL/pgSQL 將函式體中的所有 SQL 語句視為預處理語句。準備語句的查詢計劃在會話期間被記憶體，除非任何涉及的對像被更改（包括函式本身），這會釋放所有依賴的準備語句。這解釋了為什麼下一次呼叫ALTER FUNCTION總是有效。更多解釋：

• PostgreSQL 儲存過程性能

解決方案

有多種解決方法。你自己已經找到了一些。只是避免將不同數據類型的參數提供給相同（準備好的）語句

簡單的解決方案是強制轉換為相同的數據類型。你沒有提供表定義，我假設 users.id並且history.id匹配integer得很好。從我假設的錯誤消息來看roles.name，varchar所以我也將字元串文字'Role1'轉換varchar為，一切都應該工作。（您可能實際上是指'Role2'，但這與問題正交。）

在某些類型的 SQL 語句中，可以從上下文中派生數據類型，將無類型字元串文字強制轉換為匹配的數據類型。但這裡不是這樣。如果沒有顯式轉換，字元串文字是 type unknown，它與( varcharor text) 不同。這也顯示在您的錯誤消息中。

CREATE FUNCTION sp_connect(mail varchar, passwd varchar, role varchar)
 RETURNS json AS
$func$
DECLARE
  user_info record;
BEGIN
 IF role = 'Role1' THEN
   SELECT u.id, r.name INTO user_info
   FROM   users u
   JOIN   users_roles ur ON ur.user_id = u.id
   JOIN   roles r ON ur.role_id = r.id
   WHERE  u.email = mail
   AND    u.password = encode(digest(CONCAT(passwd, u.password_salt), 'sha512'), 'hex')
   AND    r.name = 'Role1';

   RAISE NOTICE 'Role1: user_info.big_id: %; user_info.name: %'
               , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
 ELSIF role = 'Role2' THEN
   SELECT h.id, 'Role1'**::varchar** AS name INTO user_info  -- Cast! And did you mean 'Role2'?
   FROM   history h
   WHERE  h.email = mail
   AND    h.password = encode(digest(CONCAT(passwd, h.password_salt), 'sha512'), 'hex');

   RAISE NOTICE 'Role2: %: user_info.big_id: %; user_info.name: %'
               , pg_typeof(user_info.big_id), pg_typeof(user_info.name);  -- see data types
 END IF;

 IF NOT FOUND THEN
     RAISE 'USER_NOT_FOUND';
 ELSE
     RETURN row_to_json(row) FROM (SELECT user_info.id AS id, user_info.name AS role) row;
 END IF;
END
$func$  LANGUAGE plpgsql STABLE;

函式可以是STABLE，這是正確的波動率。

我還添加RAISE NOTICE了顯示數據類型的語句，這應該可以幫助您進行調試。請注意，RAISE語句本身的計劃也被記憶體，因此單個RAISEafterEND IF將遇到同樣的問題。

引用自：https://dba.stackexchange.com/questions/126895