ProHoster > Blog > Administración > Antipatróns de PostgreSQL: avaliación de condicións en SQL

Antipatróns de PostgreSQL: avaliación de condicións en SQL

SQL non é C++, nin tampouco JavaScript. Polo tanto, a avaliación das expresións lóxicas é diferente, e isto non é o mesmo:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Mentres optimiza o plan de execución dunha consulta PostgreSQL pode "reorganizar" arbitrariamente as condicións equivalentes, non calcule ningún deles para rexistros individuais, consulte a condición do índice aplicado... En resumo, o xeito máis sinxelo é asumir que non pode xestionar a orde na que estarán (e se se calcularán) iguais condicións.

Polo tanto, se aínda queres xestionar a prioridade, cómpre estruturalmente facer estas condicións desiguais con condicional expresións и operadores.

Antipatróns de PostgreSQL: avaliación de condicións en SQL
Os datos e traballar con eles son a base do noso complexo VLSI, polo que é moi importante para nós que as operacións sobre eles se realicen non só correctamente, senón tamén de forma eficiente. Vexamos exemplos concretos nos que se poden cometer erros na avaliación da expresión, e onde paga a pena mellorar a súa eficiencia.

#0: RTFM

Comezando exemplo da documentación:

Cando a orde de avaliación é importante, pódese arranxar co constructo CASE. Por exemplo, deste xeito evita a división por cero nunha oración WHERE pouco fiable:

SELECT ... WHERE x > 0 AND y/x > 1.5;

Opción segura:

SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

A construción empregada CASE protexe a expresión da optimización, polo que só debe usarse cando sexa necesario.

#1: condición de activación

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

Todo parece estar ben, pero... Ninguén promete que o investiu SELECT non se executará se a primeira condición é falsa. Resolve-lo con aniñado IF:

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

Agora miremos con atención: todo o corpo da función de disparo resultou estar "envolto". IF. E isto significa que nada nos impide eliminar esta condición do procedemento de uso WHEN-condicións:

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

Este enfoque permítelle gardar os recursos do servidor cunha garantía se a condición é falsa.

#2: cadea OU/E

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

En caso contrario, pódese conseguir que ambos EXISTS será certo, pero ambos serán executados.

Pero se sabemos con certeza que un deles é "verdadeiro" moito máis a miúdo (ou "falso" - para AND-cadeas) - é posible dalgún xeito "aumentar a súa prioridade" para que a segunda non se execute unha vez máis?

Resulta que é posible: o enfoque algorítmicamente está próximo ao tema do artigo Antipatróns de PostgreSQL: a entrada rara chega á metade dun JOIN.

Imos simplemente "empurrar baixo CASE" estas dúas condicións:

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

Neste caso, non definimos ELSE-valor, é dicir, se ambas condicións son falsas CASE volverá NULL, que se interpreta como FALSE в WHERE- condicións.

Este exemplo pódese combinar doutro xeito: para saborear e colorear:

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

#3: como [non] escribir condicións

Pasamos dous días analizando os motivos do "estraño" desencadeamento deste disparador; vexamos por que.

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 número 1: a desigualdade non ten en conta NULL

Supoñamos que todo OLD-os campos importaban NULL. Que pasará?

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

E dende o punto de vista de traballar as condicións NULL equivalente FALSE, como se mencionou anteriormente.

decisión: usar operador IS DISTINCT FROM de ROW-operador, comparando rexistros enteiros á vez:

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

Problema número 2: implementación diferente dunha mesma funcionalidade

Comparar:

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

Por que hai investimentos adicionais SELECT? Unha función to_regclass? Por que é diferente...

Imos arranxar:

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

Problema #3: precedencia bool

Formateamos a fonte:

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

Vaia... De feito, resultou que no caso da verdade de calquera das dúas primeiras condicións, toda a condición convértese en TRUE, sen ter en conta as desigualdades. E isto non é para nada o que queriamos.

Imos arranxar:

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

Problema #4 (pequeno): condición OR complexa para un campo

En realidade, tivemos problemas no número 3 precisamente porque había tres condicións. Pero no canto deles, podes conseguir con un, usando o mecanismo coalesce ... IN:

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

Tamén nós NULL "captura", e complexo OR Non tes que rebuscar cos parénteses.

En total

Imos arranxar 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 función de disparo só se pode usar en UPDATEdisparador debido á presenza OLD/NEW na condición de nivel superior, entón esta condición pódese eliminar xeralmente WHEN-condición como se mostra no número 1...

Fonte: www.habr.com

Engadir un comentario