Início / dba / Perguntas / 126895
Accepted
Eric Ly
Asked: 2016-01-22 09:57:47 +0800 CST

Problemas de PL/pgSQL quando a função é usada duas vezes (problema de cache?)

  • 772

Estou enfrentando um problema absolutamente estranho que parece mais um bug do Postgres do que um problema de algoritmo.

Eu tenho esta função:

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;
$$;

O problema que estou enfrentando é quando uso essa função para fazer login com um usuário Role1 e, quando a uso com um usuário Role2, recebo esta mensagem de erro:

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

O que é... bem, eu só não entendo de onde isso vem. Se você limpar o banco de dados e alterar a ordem de login (ou seja, Role2 e depois Role1), desta vez, Role1 receberá o erro.

Problema estranho, soluções estranhas ... Se eu apenas usar, ALTER FUNCTION sp_connectmas sem modificar nada dentro da função, então magicamente, as duas funções podem fazer login sem nenhum problema. Eu também tentei esta solução:

  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;

E adicionando um IFe ELSEisso é absolutamente inútil e usa a mesma cláusula RETURN, isso não aciona nenhum erro.

Eu sei que o DBA StackExchange não é para desenvolvedores, mas esse tipo de problema parece ser mais um problema de cache ou algo assim. Alguém pode me dizer se estou fazendo algo errado com as funções do PostgreSQL? Ou onde posso obter ajuda com esse problema estranho?

postgresql postgresql-9.4

1 respostas

  1. Best Answer

    Explicação:

    Você declara user_infocomo record. O manual:

    As variáveis ​​de registro são semelhantes às variáveis ​​de tipo de linha, mas não possuem estrutura predefinida. Eles assumem a estrutura de linha real da linha que lhes é atribuída durante um comando SELECTor . A subestrutura de uma variável de registro pode mudar cada vez que é atribuída a .FOR

    Ênfase em negrito minha.

    Você atribui o registro user_infoe, em seguida, deriva um tipo de linha (na verdade, chamado rowem seu código) dele na RETURNinstrução. Tudo isso é bom e elegante, até que você atribua colunas de diferentes tipos de dados ao registro na próxima chamada na mesma sessão. Isso é incompatível com o plano de consulta em cache da instrução preparada e gera uma exceção.

    A PL/pgSQL trata todas as instruções SQL no corpo da função como instruções preparadas . O plano de consulta para instruções preparadas é armazenado em cache durante a sessão, a menos que quaisquer objetos envolvidos sejam alterados (incluindo a própria função), o que desaloca todas as instruções preparadas dependentes. Isso explica porque a próxima invocação depois ALTER FUNCTIONsempre funcionou. Mais explicações:

    Solução

    Existem várias maneiras de contornar isso. Você mesmo já encontrou alguns. Apenas evite alimentar parâmetros de diferentes tipos de dados para a mesma instrução (preparada)

    A solução simples seria converter para o mesmo tipo de dados . Você não forneceu definições de tabela, presumo users.id e history.idsão integere correspondem perfeitamente. A julgar pela mensagem de erro, presumo que roles.nameseja varchar, então lancei a string literal 'Role1'também varchare tudo deve funcionar. (Você pode realmente querer dizer 'Role2', mas isso é ortogonal ao problema.)

    Uma string literal sem tipo é forçada para um tipo de dados correspondente em alguns tipos de instruções SQL em que um tipo de dados pode ser derivado do contexto. Mas esse não é o caso aqui. Sem conversão explícita, a string literal é type unknown, que não é o mesmo que varchar(ou text). Isso também aparece na sua mensagem de erro.

    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;

    A função pode ser STABLE, essa é a volatilidade correta.

    Também adicionei RAISE NOTICEinstruções para mostrar os tipos de dados, o que deve ajudá-lo a depurar. Observe que o plano para a RAISEinstrução em si também é armazenado em cache, portanto, um único RAISEdepois END IFestaria sujeito ao mesmo problema.

    • 13

relate perguntas