ProHoster > Blog > Amministrazione > Antipatterns PostgreSQL: Evaluazione di Condizioni in SQL

Antipatterns PostgreSQL: Evaluazione di Condizioni in SQL

SQL ùn hè micca C++, nè JavaScript. Dunque, a valutazione di l'espressioni lògichi hè diversu, è questu ùn hè micca listessa cosa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Mentre ottimisate u pianu di esecuzione di una query PostgreSQL pò arbitrariamente "riordinà" cundizioni equivalenti, Ùn calculate alcunu di elli per i registri individuali, riferite à a cundizione di l'indici applicatu ... In corta, a manera più faciule hè di assume chì tù ùn pò cuntrullà l'ordine in quale saranu (è s'ellu seranu calculati in tuttu) uguali cundizioni.

Dunque, sè vo vulete ancu gestisce a priorità, avete bisognu di strutturalmente rende queste cundizioni ineguali cù cundizzioni sprissioni и uperatori.

Antipatterns PostgreSQL: Evaluazione di Condizioni in SQL
Dati è travaglià cun elli hè a basa di u nostru cumplessu VLSI, cusì hè assai impurtante per noi chì l'operazioni nantu à elli sò realizati micca solu currettamente, ma ancu in modu efficiente. Fighjemu l'esempii specifichi induve l'errori in u calculu di l'espressioni ponu esse fatti, è induve vale a pena di migliurà a so efficienza.

#0: RTFM

Partendu esempiu da a documentazione:

Quandu l'ordine di valutazione hè impurtante, pò esse riparatu cù a custruzzione CASE. Per esempiu, questu modu per evità a divisione per zero in una frase WHERE inaffidabile:

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

Opzione sicura:

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

A custruzzione utilizata CASE prutege l'espressione da l'ottimisazione, cusì deve esse usatu solu quandu hè necessariu.

#1: cundizione di trigger

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

Tuttu pare vede bè, ma... Nimu prumetti chì l'investitu SELECT ùn serà micca eseguitu se a prima cundizione hè falsa. Fix lu cun nidificatu IF:

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

Avà fighjulemu cun cura - tuttu u corpu di a funzione di scatula hè diventatu "imballatu". IF. È questu significa chì nunda ùn ci impedisce di sguassà sta cundizione da a prucedura cù l'usu WHEN- cundizioni:

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

Stu approcciu permette di salvà risorse di u servitore cù una guaranzia se a cundizione hè falsa.

#2: catena OR/AND

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

Altrimenti, pò esse ottenutu chì i dui EXISTS serà "veru", ma tutti dui seranu eseguiti.

Ma se sapemu di sicuru chì unu di elli hè "veru" assai più spessu (o "falsu" - per AND-chains) - hè pussibule di qualchì manera "aumentà a so priorità" per chì u sicondu ùn hè micca eseguitu una volta?

Ci hè chì hè pussibule - l'approcciu algoritmicu hè vicinu à u tema di l'articulu PostgreSQL Antipatterns: Un record raru ghjunghje à a mità di un JOIN.

Fighjemu solu "shove under CASE" e duie cundizioni:

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

In questu casu ùn avemu micca definitu ELSE-value, vale à dì, se i dui cundizioni sò falsi CASE tornerà NULL, chì hè interpretatu cum'è FALSE в WHERE- cundizioni.

Questu esempiu pò esse cumminatu in un altru modu - à u gustu è u culore:

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

# 3: cumu [micca] scrive e cundizioni

Avemu passatu dui ghjorni per analizà i motivi di l'attivazione "strana" di stu trigger - vedemu perchè.

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 ...

Prublemu #1: A inuguaglianza ùn conta micca per NULL

Assumimu chì tuttu OLD- i campi importavanu NULL. Chì succede ?

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

È da u puntu di vista di travaglià e cundizioni NULL equivalente FALSE, cum'è citatu sopra.

dicisioni: usu operatore IS DISTINCT FROM от ROW-operatore, paragunendu registri interi in una volta:

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

Prublemu numeru 2: implementazione differente di a stessa funziunalità

Comparare:

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

Perchè ci hè un investimentu extra quì? SELECT? Una funzione to_regclass? Perchè hè diversu...

Fixemu:

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

Prublemu #3: precedenza bool

Formatemu a fonte:

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

Oops ... In fattu, hè risultatu chì in u casu di a verità di qualsiasi di e prime duie cundizioni, tutta a cundizione diventa TRUE, senza riguardu à l'ineguaglianze. È questu ùn hè micca in tuttu ciò chì vuliamu.

Fixemu:

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

Prublemu #4 (picculu): cundizione OR cumplessa per un campu

In verità, avemu avutu prublemi in u N ° 3 precisamente perchè ci eranu trè cundizioni. Ma invece di elli, pudete piglià cun unu, usendu u mecanismu coalesce ... IN:

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

So noi NULL "avemu catturà", è difficiule OR Ùn ci hè micca bisognu di fussi cù parentesi.

Tuttu

Fixemu ciò chì avemu avutu:

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

È datu chì sta funzione trigger pò esse usata solu in UPDATE-trigger per via di a dispunibilità OLD/NEW in a cundizione di livellu superiore, allura sta cundizione pò generalmente esse eliminata WHEN-condizione cum'è mostra in # 1 ...

Source: www.habr.com

Add a comment