🥇PostgreSQL Antipadrões: Avaliação de Condição em SQL

SQL não é C++, nem JavaScript. Portanto, a avaliação de expressões lógicas é diferente, e isso não é a mesma coisa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ao otimizar o plano de execução de uma consulta PostgreSQL pode "reorganizar" arbitrariamente as condições equivalentes, não calcule nenhum deles para registros individuais, consulte a condição do índice aplicado ... Resumindo, a maneira mais fácil é supor que você não consigo a ordem em que serão (e se serão calculados) igual condições

Portanto, se você ainda deseja gerenciar a prioridade, precisa estruturar tornar essas condições desiguais com condicional expressões и operadores.

Dados e trabalhar com eles é a base do nosso complexo VLSI, por isso é muito importante para nós que as operações sejam realizadas não apenas corretamente, mas também com eficiência. Vejamos exemplos concretos onde erros na avaliação de expressões podem ser cometidos e onde vale a pena melhorar sua eficiência.

#0: RTFM

Iniciando exemplo da documentação:

Quando a ordem de avaliação é importante, ela pode ser corrigida com a construção CASE. Por exemplo, desta forma para evitar a divisão por zero em uma frase WHERE não confiável:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Opção segura:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
A construção usada CASE protege a expressão da otimização, por isso só deve ser usada quando necessário.

#1: condição de gatilho

BEGIN
  IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;

Tudo parece bem, mas... Ninguém promete que o investido SELECT não será executado se a primeira condição for falsa. Conserte com aninhado IF:

BEGIN
  IF cond(NEW.fld) THEN
    IF EXISTS(SELECT ...) THEN
      ...
    END IF;
  END IF;
  RETURN NEW;
END;

Agora vamos olhar com cuidado - todo o corpo da função de gatilho acabou sendo "envolto" em IF. E isso significa que nada nos impede de remover essa condição do procedimento usando WHEN-condições:

BEGIN
  IF EXISTS(SELECT ...) THEN
    ...
  END IF;
  RETURN NEW;
END;
...
CREATE TRIGGER ...
  WHEN cond(NEW.fld);

Essa abordagem permite economizar recursos do servidor com garantia se a condição for falsa.

#2: Cadeia OU/E

SELECT ... WHERE EXISTS(... A) OR EXISTS(... B)

Caso contrário, pode-se obter que ambos EXISTS será verdade, mas ambos serão executados.

Mas se tivermos certeza de que um deles é "verdadeiro" com muito mais frequência (ou "falso" - por AND-chains) - é possível de alguma forma "aumentar sua prioridade" para que o segundo não seja executado novamente?

Acontece que é possível - a abordagem algorítmica está próxima do tópico do artigo Antipadrões do PostgreSQL: entrada rara atinge o meio de um JOIN.

Vamos apenas "enfiar sob CASE" ambas as condições:

SELECT ...
WHERE
  CASE
    WHEN EXISTS(... A) THEN TRUE
    WHEN EXISTS(... B) THEN TRUE
  END

Neste caso, não definimos ELSE-value, ou seja, se ambas as condições forem falsas CASE retornará NULL, que é interpretado como FALSE в WHERE- condições.

Este exemplo pode ser combinado de outra maneira - a gosto e cor:

SELECT ...
WHERE
  CASE
    WHEN NOT EXISTS(... A) THEN EXISTS(... B)
    ELSE TRUE
  END

#3: como [não] escrever condições

Passamos dois dias analisando os motivos do acionamento “estranho” desse gatilho - vamos ver por quê.

Fonte:

IF( NEW."Документ_" is null or NEW."Документ_" = (select '"Комплект"'::regclass::oid) or NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)
     AND (   OLD."ДокументНашаОрганизация" <> NEW."ДокументНашаОрганизация"
          OR OLD."Удален" <> NEW."Удален"
          OR OLD."Дата" <> NEW."Дата"
          OR OLD."Время" <> NEW."Время"
          OR OLD."ЛицоСоздал" <> NEW."ЛицоСоздал" ) ) THEN ...

Problema #1: Desigualdade não leva em conta NULL

Vamos supor que tudo OLD-campos importavam NULL. O que vai acontecer?

SELECT NULL <> 1 OR NULL <> 2;
-- NULL

E do ponto de vista de trabalhar as condições NULL equivalente FALSE, como acima mencionado.

Solução: usar operador IS DISTINCT FROM de ROW-operator, comparando registros inteiros de uma só vez:

SELECT (NULL, NULL) IS DISTINCT FROM (1, 2);
-- TRUE

Problema número 2: implementação diferente da mesma funcionalidade

Compare:

NEW."Документ_" = (select '"Комплект"'::regclass::oid)
NEW."Документ_" = (select to_regclass('"ДокументПоЗарплате"')::oid)

Por que existem investimentos extras SELECT? Uma função to_regclass? Por que é diferente...

Vamos corrigir:

NEW."Документ_" = '"Комплект"'::regclass::oid
NEW."Документ_" = '"ДокументПоЗарплате"'::regclass::oid

Problema nº 3: precedência de bool

Vamos formatar a fonte:

{... IS NULL} OR
{... Комплект} OR
{... ДокументПоЗарплате} AND
( {... неравенства} )

Ops ... Na verdade, descobriu-se que, no caso da verdade de qualquer uma das duas primeiras condições, toda a condição se transforma em TRUE, desconsiderando as desigualdades. E isso não é nada do que queríamos.

Vamos corrigir:

(
  {... IS NULL} OR
  {... Комплект} OR
  {... ДокументПоЗарплате}
) AND
( {... неравенства} )

Problema nº 4 (pequeno): condição OU complexa para um campo

Na verdade, tivemos problemas no nº 3 justamente porque havia três condições. Mas em vez deles, você pode conviver com um, usando o mecanismo coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"')

Nós também NULL "pegar" e complexo OR Você não precisa se preocupar com parênteses.

No total

Vamos corrigir o que temos:

IF (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Комплект"', '"ДокументПоЗарплате"') AND
  (
    OLD."ДокументНашаОрганизация"
  , OLD."Удален"
  , OLD."Дата"
  , OLD."Время"
  , OLD."ЛицоСоздал"
  ) IS DISTINCT FROM (
    NEW."ДокументНашаОрганизация"
  , NEW."Удален"
  , NEW."Дата"
  , NEW."Время"
  , NEW."ЛицоСоздал"
  )
) THEN ...

E dado que esta função de gatilho só pode ser usada em UPDATEgatilho devido à presença OLD/NEW na condição de nível superior, então esta condição geralmente pode ser retirada em WHEN-condição conforme mostrado em #1...

Fonte: habr.com

PostgreSQL Antipadrões: avaliação de condição em SQL