Antipatterns PostgreSQL : calcul des conditions en SQL

SQL n'est ni C++, ni JavaScript. Par conséquent, le calcul des expressions logiques se fait différemment, et ceci n'est pas du tout la même chose :

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Dans le processus d'optimisation du plan d'exécution de la requête PostgreSQL peut « réorganiser » arbitrairement les conditions équivalentes, ne pas calculer certaines d'entre elles pour des enregistrements individuels, les rattacher à la condition de l'index utilisé… En gros, il est plus simple de considérer que vous ne pouvez pas contrôler l'ordre dans lequel elles seront (et seront-elles même) calculées sur un pied d'égalité les conditions.

Donc, si vous voulez quand même gérer la priorité, il faut structurer rendre ces conditions inégales à l'aide des expressions et opérateurs.

Les données et leur manipulation sont le fondement de notre ensemble SBIT, il est donc très important pour nous que les opérations les concernant soient effectuées non seulement de manière correcte, mais aussi efficace. Voyons quelques exemples concrets où des erreurs de calcul des expressions peuvent se produire et où il convient d'améliorer leur efficacité.

#0: RTFM

Départ exemple de la documentation:

Lorsque l'ordre de calcul est important, il peut être fixé à l'aide de la construction CASE. Par exemple, cette méthode d'éviter la division par zéro dans la clause OÙ n'est pas fiable :

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

Option sûre :

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

Cette construction appliquée CASE protège l'expression de l'optimisation, donc son utilisation doit se faire uniquement en cas de nécessité.

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

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

Tout semble bien, mais… Personne ne garantit que le sous-requête SELECT ne sera pas exécuté si la première condition est fausse. Corrigeons cela avec des IF imbriqués BEGIN IF cond(NEW.fld) THEN IF EXISTS(SELECT ...) THEN ... END IF; END IF; RETURN NEW; END;:

Regardons de plus près — tout le corps de la fonction déclencheur a été « enveloppé » dans

. Cela signifie qu'il ne nous empêche pas de sortir cette condition de la procédure à l'aide de BEGIN IF cond(NEW.fld) THEN IF EXISTS(SELECT ...) THEN ... END IF; END IF; RETURN NEW; END;WHEN -conditionBEGIN IF EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; ... CREATE TRIGGER ... WHEN cond(NEW.fld);:

Cette approche permet de garantir des économies de ressources serveur lorsque la condition est fausse.

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

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

Dans le cas désagréable, on peut obtenir que les deux

EXISTS soient « vrais », mais les deux s'exécuteront Mais si nous savons pertinemment que l'un d'eux est « vrai » beaucoup plus souvent (ou « faux » — pour.

AND AND-chaînes) — ne peut-on pas d'une manière ou d'une autre « augmenter sa priorité » afin que le second ne s'exécute pas inutilement ?

Apparemment, c'est possible — l'approche algorithmique est proche du sujet de l'article PostgreSQL Antipatterns : une écriture rare atteindra le milieu du JOIN.

Mettons simplement « sous CASE » ces deux conditions :

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

Dans ce cas, nous n'avons pas défini SINON-valeur, donc en cas de fausseté des deux conditions CASE renverra NULL, ce qui est interprété comme FAUX dans OÙ-condition.

Cet exemple peut aussi être combiné différemment — selon les goûts :

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

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

Nous avons passé deux jours à analyser les raisons du fonctionnement « étrange » de ce déclencheur — voyons pourquoi.

Source :

IF( NEW."Document_" is null or NEW."Document_" = (select '"Ensemble"'::regclass::oid) or NEW."Document_" = (select to_regclass('"DocumentParSalaire"')::oid)
     AND (   OLD."DocumentNotreOrganisation" <> NEW."DocumentNotreOrganisation"
          OR OLD."Supprimé" <> NEW."Supprimé"
          OR OLD."Date" <> NEW."Date"
          OR OLD."Heure" <> NEW."Heure"
          OR OLD."PersonneCréé" <> NEW."PersonneCréé" ) ) THEN ...

Problème n°1 : l'inégalité ne tient pas compte de NULL

Imaginons que tous OLD-champs aient une valeur NULL. Que va-t-il se passer ?

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

Et en termes d'évaluation de condition NULL équivaut à FAUX, comme mentionné ci-dessus.

Solution: utilisez l'opérateur IS DISTINCT FROM à partir de LIGNE-de l'opérateur, en comparant immédiatement des enregistrements entiers :

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

Problème n°2 : mise en œuvre différente d'une fonctionnalité identique

Comparons :

NEW."Document_" = (select '"Ensemble"'::regclass::oid)
NEW."Document_" = (select to_regclass('"DocumentParSalaire"')::oid)

Pourquoi ces imbrications inutiles SELECT? А функция to_regclass? А по-разному-то почему?..

Corrigeons :

NEW."Document_" = '"Ensemble"'::regclass::oid
NEW."Document_" = '"DocumentParSalaire"'::regclass::oid

Problème n°3 : priorité des opérations booléennes

Formatons le code source :

{... IS NULL} OR
{... Ensemble} OR
{... DocumentParSalaire} AND
( {... inégalités} )

Oups... En fait, il s'est avéré que si l'un des deux premières conditions est vraie, alors toute la condition devient VRAI, sans tenir compte des inégalités. Et ce n'est pas du tout ce que nous voulions.

Corrigeons :

(
  {... IS NULL} OR
  {... Ensemble} OR
  {... DocumentParSalaire}
) AND
( {... inégalités} )

Problème n°4 (petit) : condition OR complexe pour un seul champ

En fait, les problèmes du n°3 nous sont apparus précisément parce qu'il y avait trois conditions. Mais nous pouvons nous en passer avec un seul, grâce au mécanisme coalesce ... IN:

coalesce(NEW."Документ_"::text, '') IN ('', '"Кompte"', '"DocumentSurLeSalaire"')

Alors nous NULL «attraperons», et complexes OU sans avoir à jouer avec des parenthèses.

Au total

Fixons ce que nous avons obtenu :

SI (
  coalesce(NEW."Документ_"::text, '') IN ('', '"Кompte"', '"DocumentSurLeSalaire"') ET
  (
    OLD."DocumentNotreOrganisation"
  , OLD."Supprimé"
  , OLD."Date"
  , OLD."Heure"
  , OLD."CrééPar"
  ) EST DISTINCT DE (
    NEW."DocumentNotreOrganisation"
  , NEW."Supprimé"
  , NEW."Date"
  , NEW."Heure"
  , NEW."CrééPar"
  )
) ALORS ...

Et si l'on considère que cette fonction de déclenchement ne peut être appliquée que dans UPDATE-le déclencheur en raison de la présence de OLD/NEW dans la condition de premier niveau, alors cette condition peut être complètement déplacée dans -condition-la condition, comme montré en #1…

Source : habr.com