主页 / dba / 问题 / 126895
Accepted
Eric Ly
Asked: 2016-01-22 09:57:47 +0800 CST

函数使用两次时的 PL/pgSQL 问题（缓存问题？）

  • 772

我正面临一个绝对奇怪的问题，感觉更像是 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 函数做错了什么吗？或者我可以在哪里获得解决这个奇怪问题的帮助？

postgresql postgresql-9.4

1 个回答

  1. Best Answer

    解释：

    您声明user_info为record. 手册：

    记录变量类似于行类型变量，但它们没有预定义的结构。它们采用在SELECTorFOR命令期间分配的行的实际行结构。每次将记录变量分配给 时，它的子结构都会发生变化。

    大胆强调我的。

    您分配记录user_info，然后在语句中从中派生一个行类型（实际上row在您的代码中调用） 。RETURN这一切都很好而且很花哨，直到您在同一会话中的下一次调用中将不同数据类型的列分配给记录。这与准备好的语句的缓存查询计划不兼容并引发异常。

    PL/pgSQL 将函数体中的所有 SQL 语句视为预处理语句。准备语句的查询计划在会话期间被缓存，除非任何涉及的对象被更改（包括函数本身），这会释放所有依赖的准备语句。这解释了为什么下一次调用ALTER FUNCTION总是有效。更多解释：

    解决方案

    有多种解决方法。你自己已经找到了一些。只是避免将不同数据类型的参数提供给相同（准备好的）语句

    简单的解决方案是强制转换为相同的数据类型。你没有提供表定义，我假设 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将遇到同样的问题。

    • 13

相关问题