PostgreSQL Antipatterns: izračunavanje uslova u SQL-u

SQL nije C++ niti JavaScript. Stoga se izračun logičkih izraza odvija drugačije, a ovo nije isto:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Tijekom optimizacije plana izvršenja upita, PostgreSQL može proizvoljno "premještati" ekvivalentne uvjete, ne izračunavati neke od njih za pojedinačne zapise, pripisivati ih uvjetu korištenog indeksa... Ukratko, najlakše je smatrati da unaprijed ne možete upravljati redoslijedom u kojem će se (i hoće li uopće) izračunavati ravnopravni uvjeti.

Stoga, ako želite upravljati prioritetom, trebate strukturalno učiniti te uvjete nejednakima pomoću uvjetnih izraza i operatora..

PostgreSQL Antipatterns: izračunavanje uslova u SQL-u
Podaci i rad s njima — temelj su našeg kompleksa SBIS,stoga nam je vrlo važno da operacije nad njima budu izvršene ne samo ispravno, već i učinkovito. Pogledajmo konkretne primjere gdje mogu nastati greške u izračunavanju izraza, a gdje bi trebali poboljšati njihovu učinkovitost.

#0: RTFM

Početni primer iz dokumentacije:

Kada je redoslijed izračunavanja važan, može se fiksirati pomoću konstrukcije CASE. Na primjer, ovakav način izbjegavanja dijeljenja s nulom u uvjetu GDJE nije pouzdan:

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

Sigurna opcija:

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

Konstrukcija primijenjena ovako CASE štiti izraz od optimizacije, stoga je treba koristiti samo kada je potrebno.

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

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

Sve izgleda dobro, ali... Nitko ne jamči da će ugniježđeni SELECT biti izvršen kada je prvi uvjet neistinit. Ispravimo to pomoću ugnježdenih IF:

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

Sada pažljivo pogledajmo — cijelo tijelo trigera je "zamotano" u IF. A to znači da nas ništa ne sprječava da iznesemo ovaj uvjet iz procedure pomoću WHEN-uvjeta:

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

Ovakav pristup jamči uštedu resursa servera kada je uvjet neistinit.

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

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

U neugodnom slučaju može se dogoditi da oba EXISTS budu "istinita", ali oba i izvrše.

No, ako točno znamo da je jedan od njih "istinito" mnogo češće (ili "neistinit" — za I-lanci) — može li se nekako "povećati njegov prioritet", da drugi ne bude izvršen viškovito?

Ispostavilo se da može — algoritamski pristup je blizak temi ovog članka PostgreSQL Antipatterns: retka zapis će stići do sredine JOIN.

Hajde da jednostavno "stavimo pod CASE" oba ova uslova:

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

U ovom slučaju nismo definisali ELSE-vrednost, dakle u slučaju neistinitosti oba uslova CASE vratiće NULL, što se tumači kao FALSE u GDJE-uslovima.

Ovaj primer se može kombinovati i drugačije — po ukusu i boji:

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

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

Na analizu uzroka "neobičnog" delovanja ovog okidača potrošili smo dva dana — hajde da vidimo zašto.

Izvorni kod:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplati"')::oid)
     AND (   OLD."DokumentNašaOrganizacija" <> NEW."DokumentNašaOrganizacija"
          OR OLD."Obrisan" <> NEW."Obrisan"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Vreme" <> NEW."Vreme"
          OR OLD."OsobaStvorila" <> NEW."OsobaStvorila" ) ) THEN ...

Problem br. 1: nejednakost ne uzima u obzir NULL

Pretpostavimo da su svi OLD-polja imala vrednost NULL. Šta će se desiti?

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

A sa stanovišta izvršavanja uslova NULL jednako je FALSE, kao što je pomenuto iznad.

Rešenje: koristite operator IS DISTINCT FROM od ROW-operatera, poredite odmah cele zapise:

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

Problem br. 2: različita implementacija istog funkcionalnosti

Uporedimo:

NEW."Dokument_" = (select '"Komplet"'::regclass::oid)
NEW."Dokument_" = (select to_regclass('"DokumentPoZarplati"')::oid)

Zašto su tu suvišni ugnježdeni SELECT? А функция to_regclass? А по-разному-то почему?..

Ispravimo:

NEW."Dokument_" = '"Komplet"'::regclass::oid
NEW."Dokument_" = '"DokumentPoZarplati"'::regclass::oid

Problem br. 3: prioritet bool-operacija

Formatirajmo izvorni kod:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarplati} AND
( {... nejednakosti} )

Ups… U stvari, ispostavilo se da u slučaju istinitosti bilo kojeg od prva dva uslova, čitav uslov se pretvara u TRUE, bez uzimanja u obzir nejednakosti. A to nije ono što smo želeli.

Ispravimo:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarplati}
) AND
( {... nejednakosti} )

Problem br. 4 (mali): složeni OR-uslov za jedno polje

Zapravo, problemi u br. 3 su nastali upravo zato što je bilo tri uslova. Ali umesto njih možemo se poslužiti jednim, koristeći mehanizam coalesce ... IN:

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

Tako da ćemo NULL «uhvatiti» i složene ILI neće biti potrebno praviti sa zagrada.

U ukupnom zbiru

Zabilježimo to što imamo:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplate"') AND
  (
    OLD."DokumentNaszaOrganizacija"
  , OLD."Obrisano"
  , OLD."Datum"
  , OLD."Vrijeme"
  , OLD."OsobaKreirala"
  ) IS DISTINCT FROM (
    NEW."DokumentNaszaOrganizacija"
  , NEW."Obrisano"
  , NEW."Datum"
  , NEW."Vrijeme"
  , NEW."OsobaKreirala"
  )
) THEN ...

A ako uzmemo u obzir da se ova okidačka funkcija može primjenjivati samo u UPDATE-okidaču zbog postojanja OLD/NEW u uslovu gornjeg nivoa, ovo stanje se može uopšte prenijeti u WHEN-uslov, kao što je prikazano u #1…

Izvor: habr.com

Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere 🔥 Kupite pouzdan hosting za sajtove uz zaštitu od DDoS, VPS VDS servere | ProHoster