PostgreSQL Antipatterns: výpočty podmienok v SQL

SQL nie je C++ a ani JavaScript. Preto sa výpočty logických výrazov vykonávajú inak a toto rozhodne nie je to isté:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Počas optimalizácie plánu vykonávania dotazu PostgreSQL môže ľubovoľne "premiestňovať" ekvivalentné podmienky, nevypočítavať niektoré z nich pre jednotlivé záznamy, priraďovať k podmienke používaného indexu… Skrátka, najlepšie je považovať to tak, že vopred nemôžete riadiť poradie, v akom budú (a či vôbec) vypočítané rovnocenné podmienky.

Preto ak chcete riadiť prioritu, musíte štrukturálne urobiť tieto podmienky nerovnocenné s pomocou podmienených výrazov. a operátorov.

Dáta a práca s nimi sú základom nášho komplexu SBIS, preto je pre nás veľmi dôležité, aby operácie nad nimi prebiehali nielen správne, ale aj efektívne. Pozrime sa na konkrétne príklady, kde môže dôjsť k chybám vo výpočtoch výrazov a kde je potrebné zlepšiť ich efektívnosť.

#0: RTFM

Začiatok príklad z dokumentácie:

Keď je poradie výpočtu dôležité, môžete ho určiť pomocou konštrukcie CASE. Napríklad, tento spôsob, ako sa vyhnúť deleniu nulou v podmienke WHERE nie je spoľahlivý:

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

Bezpečná varianta:

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

Konštrukcia používaná takto CASE chránia výraz pred optimalizáciou, preto by sa mala používať len v prípade potreby.

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

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

Zdá sa, že všetko vyzerá dobre, ale… Nikto nezaručuje, že vnorený SELECT nevybaví podmienku, ak prvá podmienka je nepravdivá. Opravené pomocou vnorených IF:

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

Teraz si pozrime pozorne — celé telo spúšťacej funkcie sa ukázalo byť "zabalené" v IF. A to znamená, že nám nič nebráni vyňať túto podmienku z postupu pomocou WHEN-podmienky:

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

Takýto prístup zaručuje úsporu zdrojov servera pri nepravdivosti podmienky.

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

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

V nepríjemnom prípade môžeme získať, že obidve EXISTS budú "pravdivé", ale obidve sa vykonajú..

Ale ak presne vieme, že jedna z nich býva "pravdivá" oveľa častejšie (alebo "nepravdivá" — pre A-reťazce) — nemôžeme nejako "zvýšiť jej prioritu", aby sa druhá nevykonávala zbytočne?

Ukazuje sa, že to je možné — algoritmicky prístup je blízky téme článku PostgreSQL antipatterny: zriedkavý zápis sa dostane do stredu JOIN.

Poďme jednoducho „schovať pod CASE“ obe tieto podmienky:

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

V tomto prípade sme nedefinovali ELSE-hodnotu, teda v prípade nepravdivosti oboch podmienok CASE vrátí NULL, čo sa chápe ako FALSE v WHERE-podmienka.

Tento príklad možno skombinovať aj inak — podľa chuti:

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

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

Na analýzu príčin „podivného“ spustenia tohto spúšťača sme strávili dva dni — poďme sa pozrieť, prečo.

Zdrojový kód:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplate"')::oid)
     AND (   OLD."DokumentNasaOrganizacia" <> NEW."DokumentNasaOrganizacia"
          OR OLD."Odstraneny" <> NEW."Odstraneny"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Cas" <> NEW."Cas"
          OR OLD."OsobaVytvorila" <> NEW."OsobaVytvorila" ) ) THEN ...

Problém č. 1: nerovnosť nezohľadňuje NULL

Predpokladajme, že všetky OLD-polia mali hodnotu NULL. Čo sa stane?

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

A z pohľadu spracovania podmienky NULL je ekvivalentný FALSE, ako bolo uvedené vyššie.

Riešenie: použite operátor IS DISTINCT FROM od ROW-operátora, porovnávajúc ihneď celé záznamy:

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

Problém č. 2: rôzna realizácia rovnakého funkčného pokrytia

Porovnajme:

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

Prečo sú tu zbytočné vnorené SELECT? А функция to_regclass? А по-разному-то почему?..

Oprávame:

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

Problém č. 3: priorita bool-Operácií

Naformátujme zdrojový kód:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarplate} AND
( {... nerovnosti} )

Ups… V skutočnosti sa ukázalo, že v prípade pravdivosti ktorejkoľvek z dvoch prvých podmienok, celá podmienka sa transformuje na TRUE, bez zohľadnenia nerovností. A to nie je vôbec to, čo sme chceli.

Oprávame:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarplate}
) AND
( {... nerovnosti} )

Problém č. 4 (malý): zložitá OR-podmienka pre jedno pole

Vlastne problémy v č. 3 vznikli práve preto, že podmienok bolo tri. Ale namiesto nich sa dá obísť s jednou, prostredníctvom mechanizmu coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Komplet"', '"DokumentPoZarplate"')

Takto sa nám NULL »chytí«, a zložitých OR s závorkami nebude treba robiť.

Spolu

Zaznamenajme, čo sme dosiahli:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Súprava"', '"DokumentMzdy"') AND
  (
    OLD."DokumentNašaOrganizácia"
  , OLD."Odstránený"
  , OLD."Dátum"
  , OLD."Čas"
  , OLD."OsobaVytvorila"
  ) IS DISTINCT FROM (
    NEW."DokumentNašaOrganizácia"
  , NEW."Odstránený"
  , NEW."Dátum"
  , NEW."Čas"
  , NEW."OsobaVytvorila"
  )
) THEN ...

A ak zohľadníme, že táto spúšťacia funkcia sa môže aplikovať iba v UPDATE-spúšťači kvôli prítomnosti OLD/NEW v podmienke najvyššej úrovne, tak túto podmienku môžeme úplne presunúť do WHEN-podmienky, ako bolo ukázané v #1…

Zdroj: habr.com