🍉 👨🏽‍🤝‍👨🏻 🚣 Antipadrões do PostgreSQL: calculando condições no SQL 🤴🏽 💎 🤘🏻

SQL não é C ++, nem JavaScript. Portanto, o cálculo das expressões lógicas é diferente, e isso não é a mesma coisa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

No processo de otimizar o plano de execução da consulta, o PostgreSQL pode "reorganizar" arbitrariamente as condições equivalentes , não calcular nenhuma delas para registros individuais, atribuí-las à condição do índice aplicável ... Em suma, é mais fácil supor que você não pode controlar em que ordem As condições dos pares serão calculadas (e, se for o caso) .

Portanto, se você ainda deseja controlar a prioridade, precisa estruturalmente tornar essas condições desiguais usando expressões e operadores condicionais .

Os dados e o trabalho com eles são a base do nosso complexo VLSI , por isso é muito importante para nós que as operações neles sejam executadas não apenas corretamente, mas também com eficiência. Vejamos exemplos específicos em que erros de computação de expressão podem ser cometidos e onde vale a pena melhorar sua eficiência.

# 0: RTFM

Iniciando o exemplo da documentação :

Quando a ordem de cálculo é importante, ela pode ser corrigida usando a construção CASE. Por exemplo, essa maneira de evitar dividir por zero em uma frase WHEREnã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 dessa maneira CASEprotege a expressão da otimização, portanto, ela só deve ser usada se necessário.

# 1: condição no gatilho

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

Parece que tudo parece bom, mas ... Ninguém promete que o anexo SELECTnão será executado se a primeira condição for falsa. Corrija usando aninhadoIF :

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 por estar "envolvido" IF. E isso significa que nada nos impede de remover essa condição do procedimento usando a WHENcondição- :

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

Essa abordagem permite economia garantida de recursos do servidor em condições falsas.

# 2: cadeia OR / AND

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

Caso contrário, você pode entender que ambos EXISTSserão "verdadeiros", mas ambos serão cumpridos .

Mas se tivermos certeza de que um deles é “verdadeiro” com muito mais frequência (ou “falso” para a ANDcadeia), é possível de alguma forma “aumentar sua prioridade” para que o segundo não seja executado novamente?

Acontece que é possível - a abordagem do algoritmo está próxima ao tópico do artigo Antipatterns do PostgreSQL: um registro raro chegará ao meio do JOIN .

Vamos "pop sob CASE" nessas duas condições:

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

Nesse caso, não determinamos o ELSEvalor-, ou seja, se ambas as condições forem falsas , ele CASEretornará NULL, o que é interpretado como FALSEna WHEREcondição-.

Este exemplo pode ser combinado de outra maneira - para provar e colorir:

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 gatilho "estranho" desse gatilho - vamos ver o porquê.

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: a desigualdade não considera NULL

Imagine que todos os OLDcampos são importantes NULL. O que vai acontecer?

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

E do ponto de vista de elaborar as condições é NULLequivalente FALSE, como mencionado acima.

Solução : use o operador IS DISTINCT FROMdo operador ROW, 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

Comparar:

NEW."_" = (select '""'::regclass::oid)
NEW."_" = (select to_regclass('""')::oid)

Por que existem aninhamentos extras SELECT? E a função to_regclass? E de diferentes maneiras, por que ..?

Fix:

NEW."_" = '""'::regclass::oid
NEW."_" = '""'::regclass::oid

Problema nº 3: prioridade das operações booleanas

Formate a fonte:

{... IS NULL} OR
{... } OR
{... } AND
( {... } )

Opa ... De fato, verificou-se que, no caso da verdade de qualquer uma das duas primeiras condições, toda a condição se transforma TRUE, sem levar em conta as desigualdades. E isso não é o que queríamos.

Consertar:

(
  {... IS NULL} OR
  {... } OR
  {... }
) AND
( {... } )

Problema 4 (pequeno): condição OR complexa para um campo

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

coalesce(NEW."_"::text, '') IN ('', '""', '""')

Então, nós NULL“pegamos”, e o complexo ORcom colchetes não precisa cercar.

Total

Corrigimos o que conseguimos:

IF (
  coalesce(NEW."_"::text, '') IN ('', '""', '""') AND
  (
    OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  , OLD.""
  ) IS DISTINCT FROM (
    NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  , NEW.""
  )
) THEN ...

E se levarmos em conta que essa função de gatilho pode ser usada apenas no UPDATEgatilho devido à presença de OLD/NEWum nível superior na condição, essa condição geralmente pode ser colocada na WHENcondição-, como foi mostrado no item 1 ...

Antipadrões do PostgreSQL: calculando condições no SQL