🥇Antipadrões do PostgreSQL: cálculo de condições em SQL

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

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Durante a otimização do plano de execução da consulta PostgreSQL pode 'reordenar' condições equivalentes de maneira arbitrária, não calcular algumas delas para registros individuais, atribuir a condição ao índice aplicável... Em resumo, é mais fácil considerar que você não pode controlar a ordem em que serão (ou se serão) avaliadas as condições equivalentes. Portanto, se você quiser controlar a prioridade, deve estruturalmente tornar essas condições desiguais

usando expressões condicionais. Os dados e o trabalho com eles são a base do nosso sistema SBIS e operadores.

, portanto, é muito importante para nós que as operações realizadas sejam executadas não apenas corretamente, mas também de forma eficiente. Vamos ver exemplos concretos, onde podem ocorrer erros de avaliação de expressões e onde devemos melhorar sua eficiência. InícioQuando a ordem de avaliação é importante, pode ser fixada usando a construção

#0: RTFM

CASE exemplo da documentação:

. Por exemplo, essa maneira de evitar a divisão por zero na cláusula não é confiável:SELECT ... WHERE x > 0 AND y/x > 1.5; WHERE Uma opção segura:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
A construção usada assim
protege a expressão da otimização, portanto deve ser usada apenas quando necessário.
BEGIN IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; não é confiável: Tudo parece bom, mas... Ninguém garante que o aninhado

#1: условие в триггере

não será executado se a primeira condição for falsa. Vamos corrigir isso com

IF SELECT BEGIN IF cond(NEW.fld) THEN IF EXISTS(SELECT ...) THEN ... END IF; END IF; RETURN NEW; END; Agora, vamos olhar atentamente — todo o corpo da função do gatilho está 'embrulhado' em . E isso significa que nada nos impede de extrair essa condição da procedure usando:

WHEN

-condicional . E isso significa que nada nos impede de extrair essa condição da procedure usandoBEGIN IF EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; ... CREATE TRIGGER ... WHEN cond(NEW.fld); Essa abordagem permite garantir a economia de recursos do servidor em caso de condição falsa.SELECT ... WHERE EXISTS(... A) OR EXISTS(... B):

No caso infeliz, pode-se chegar à situação em que ambos

serão 'verdadeiros', mas

#2: OR/AND-цепочка

ambos serão executados.

Mas se sabemos que um deles é 'verdadeiro' muito mais frequentemente (ou 'falso' — para EXISTS -cadeias) — não podemos de alguma forma 'aumentar sua prioridade' para que o segundo não seja executado desnecessariamente? ambos serão executados.

Mas se sabemos exatamente que um deles é 'verdadeiro' com muito mais frequência (ou 'falso' — para E-cadeias) — não seria possível de alguma forma 'aumentar sua prioridade', para que o segundo não fosse executado desnecessariamente?

Aparentemente, é possível — a abordagem algorítmica está próxima do tema do artigo PostgreSQL Antipatterns: uma gravação rara chegará ao meio do JOIN.

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

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

Neste caso, não definimos ELSE-valor, ou seja, no caso de falsidade de ambas as condições não é confiável: retornará NULL, que é interpretado como FALSE em WHERE-condições.

Este exemplo pode ser combinado de outra forma — ao gosto e cor:

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

#3: как [не] надо писать условия

Levamos dois dias para analisar as causas da "estranha" ativação deste gatilho — vamos ver por quê.

Código fonte:

IF( NEW."Documento_" is null or NEW."Documento_" = (select '"Conjunto"'::regclass::oid) or NEW."Documento_" = (select to_regclass('"DocumentoPorSalario"')::oid)
     AND (   OLD."DocumentoNossaOrganizacao"  NEW."DocumentoNossaOrganizacao"
          OR OLD."Excluido"  NEW."Excluido"
          OR OLD."Data"  NEW."Data"
          OR OLD."Hora"  NEW."Hora"
          OR OLD."Criador"  NEW."Criador" ) ) THEN ...

Problema nº 1: a desigualdade não considera NULL

Vamos imaginar que todos OLD-campos tiveram valor NULL. O que vai acontecer?

SELECT NULL  1 OR NULL  2;
-- NULL

E do ponto de vista da execução da condição NULL equivale a FALSE, como foi mencionado acima.

Solução: use o operador IS DISTINCT FROM de ROW-operador, comparando registros inteiros:

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

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

Comparando:

NEW."Documento_" = (select '"Conjunto"'::regclass::oid)
NEW."Documento_" = (select to_regclass('"DocumentoPorSalario"')::oid)

Por que ter вложенные SELECT? А функция to_regclass? А по-разному-то почему?..

Corrigindo:

NEW."Documento_" = '"Conjunto"'::regclass::oid
NEW."Documento_" = '"DocumentoPorSalario"'::regclass::oid

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

Formatando o código fonte:

{... IS NULL} OR
{... Conjunto} OR
{... DocumentoPorSalario} AND
( {... desigualdades} )

Ops… Na verdade, aconteceu que, se qualquer uma das duas primeiras condições for verdadeira, toda a condição se torna TRUE, desconsiderando as desigualdades. E isso não é de forma alguma o que queríamos.

Corrigindo:

(
  {... IS NULL} OR
  {... Conjunto} OR
  {... DocumentoPorSalario}
) AND
( {... desigualdades} )

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

Na verdade, os problemas mencionados no nº 3 surgiram exatamente porque havia três condições. Mas podemos simplificar para uma, usando o mecanismo coalesce ... IN:

coalesce(NEW."Documento_"::text, '') IN ('', '"Conjunto"', '"DocumentoPorSalario"')

Assim, conseguiremos NULL "capturar", e não precisaremos fazer coisas complicadas OR com parênteses.

Total

Vamos resumir o que obtivemos:

IF (
  coalesce(NEW."Documento_"::text, '') IN ('', '"Conjunto"', '"DocumentoDeSalário"') AND
  (
    OLD."DocumentoNossaOrganização"
  , OLD."Deletado"
  , OLD."Data"
  , OLD."Hora"
  , OLD."CriadoPor"
  ) IS DISTINCT FROM (
    NEW."DocumentoNossaOrganização"
  , NEW."Deletado"
  , NEW."Data"
  , NEW."Hora"
  , NEW."CriadoPor"
  )
) THEN ...

E se considerarmos que esta função de gatilho só pode ser aplicada em ATUALIZAÇÃO-gatilhos devido à presença de OLD/NEW na condição de nível superior, então essa condição pode ser totalmente extraída para um Essa abordagem permite garantir a economia de recursos do servidor em caso de condição falsa.-condição, como mostrado em #1…

Fonte: habr.com