PostgreSQL Antipatterns: vyhodnocování podmínek v SQL

SQL není C++ ani JavaScript. Proto se vyhodnocování logických výrazů děje jinak, a tohle rozhodně není totéž:

KDE fncondX() A fncondY()

= fncondX() && fncondY()

Během optimalizace realizačního plánu dotazu PostgreSQL může libovolně "přeskupovat" ekvivalentní podmínky, nevyhodnocovat některé z nich pro jednotlivé záznamy, přidělovat k podmínce použitého indexu… Jednoduše řečeno, je nejlepší považovat, že nemáte kontrolu nad tím, v jakém pořadí budou (a zda vůbec) vyhodnoceny rovnocenné podmínky. Proto pokud chcete i nadále řídit prioritu, musíte strukturovaně

udělat tyto podmínky nerovnými pomocí podmínkových výrazů operátorů a Data a práce s nimi jsou základem.

našeho komplexu SBIS , proto je pro nás velmi důležité, aby operace s nimi byly prováděny nejen správně, ale také efektivně. Podívejme se na konkrétní příklady, kde mohou nastat chyby při vyhodnocování výrazů a kde je třeba zlepšit jejich efektivitu.Úvodní

#0: RTFM

příklad z dokumentace Když je pořadí vyhodnocení důležité, lze ho určit pomocí konstrukce:

CASE . Například tento způsob, jak se vyhnout dělení nulou v prohlášeníKDE není spolehlivý: SELECT ... KDE x > 0 A y/x > 1.5;

Bezpečná varianta:

SELECT ... KDE CASE KDYŽ x > 0 THEN y/x > 1.5 ELSE false END;

Tato konstrukce použita

chrání výraz před optimalizací, proto ji musíte používat pouze v případě potřeby. . Například tento způsob, jak se vyhnout dělení nulou v prohlášení ZAČÍT KDYŽ cond(NEW.fld) A EXISTUJE(SELECT ...) POTOM ... KONČIT; VRAŤ NOVÝ; KONEC;

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

Vypadá to, že vše vypadá dobře, ale… Nikdo neslibuje, že vnořený

SELECT nebude prováděn při nepravdivosti první podmínky. Opravy pomocí vnořených IF ZAČÍT KDYŽ cond(NEW.fld) POTOM KDYŽ EXISTUJE(SELECT ...) POTOM ... KONČIT; KONČIT; VRAŤ NOVÝ; KONEC;:

Teď se podívejme pozorně — celé tělo spouštěcí funkce bylo "zabalené" do

. A to znamená, že nás nic nebrání vzít tuto podmínku z procedury pomocí ZAČÍT KDYŽ cond(NEW.fld) POTOM KDYŽ EXISTUJE(SELECT ...) POTOM ... KONČIT; KONČIT; VRAŤ NOVÝ; KONEC;KDYŽ -podmínkyZAČÍT KDYŽ EXISTUJE(SELECT ...) POTOM ... KONČIT; VRAŤ NOVÝ; KONEC; ... VYTVOŘIT TRIGGER ... KDYŽ cond(NEW.fld);:

Takový přístup umožňuje zaručeně ušetřit zdroje serveru při nepravdivosti podmínky.

SELECT ... KDE EXISTUJE(... A) NEBO EXISTUJE(... B)

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

V nepříjemném případě se může stát, že oba

budou "pravdivými", ale EXISTS oba se provádějí Ale pokud přesně víme, že jeden z nich je "pravdivý" mnohem častěji (nebo "nepravdivý" — pro.

A A-řetězce) — nelze nějak „zvýšit jeho prioritu“, aby se druhý vykonával zbytečně jednou?

Očividně to jde — algoritmický přístup se blíží tématu článku. PostgreSQL Anti-příklady: vzácný zápis dosáhne prostředku JOIN..

Prostě vložme obě tato podmínky pod CASE:

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

V tomto případě jsme nedefinovali ELSE-hodnotu, tedy v případě, že obě podmínky jsou nepravdivé. . Například tento způsob, jak se vyhnout dělení nulou v prohlášení vrátí NULL, což se interpretuje jako FALSE v není spolehlivý:-podmínku.

Tento příklad lze kombinovat i jinak — podle chuti a barvy:

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

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

Na analýzu příčin „podivného“ fungování tohoto spouštěče jsme strávili dva dny — podívejme se, proč.

Původní kód:

IF( NEW."Dokument_" is null or NEW."Dokument_" = (select '"Komplet"'::regclass::oid) or NEW."Dokument_" = (select to_regclass('"DokumentPoZarplatě"')::oid)
     AND (   OLD."DokumentNašeOrganizace" <> NEW."DokumentNašeOrganizace"
          OR OLD."Odstraněno" <> NEW."Odstraněno"
          OR OLD."Datum" <> NEW."Datum"
          OR OLD."Čas" <> NEW."Čas"
          OR OLD."OsobaVytvořila" <> NEW."OsobaVytvořila" ) ) THEN ...

Problém č. 1: nerovnost nezohledňuje NULL.

Představme si, že všechny OLD-pole měly hodnotu NULL. Co se stane?

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

A z pohledu vyhodnocení podmínky NULL je ekvivalentní, FALSE, jak bylo uvedeno výše.

Řešení: použijte operátor IS DISTINCT FROM od ROW-operátoru, porovnávajícího hned celé záznamy:

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

Problém č. 2: různé implementace stejné funkce.

Porovnejme:

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

Proč zde zbytečné vnořené nebude prováděn při nepravdivosti první podmínky. Opravy pomocí? А функция to_regclass? А по-разному-то почему?..

Opravit:

NEW."Dokument_" = '"Komplet"'::regclass::oid
NEW."Dokument_" = '"DokumentPoZarplatě"'::regclass::oid

Problém č. 3: priorita bool-operací.

Naformátujme původní kód:

{... IS NULL} OR
{... Komplet} OR
{... DokumentPoZarplatě} AND
( {... nerovnosti} )

Ups… Ve skutečnosti se stalo to, že v případě pravdivosti jakékoliv ze dvou prvních podmínek, celá podmínka se obrací na TRUE, bez ohledu na nerovnosti. A to není přesně to, co jsme chtěli.

Opravit:

(
  {... IS NULL} OR
  {... Komplet} OR
  {... DokumentPoZarplatě}
) AND
( {... nerovnosti} )

Problém č. 4 (malý): složité OR-podmínky pro jedno pole.

Ve skutečnosti jsme měli problémy č. 3 právě z důvodu, že bylo tři podmínky. Ale místo toho se dá obejít jednou, pomocí mechanismu coalesce ... IN:

coalesce(NEW."Dokument_"::text, '') IN ('', '"Sada"', '"DokumentPodplatou"')

Takže my a NULL „chytíme“, a složitých Nebo se závorkami se nebude nutné pletit.

Celkem

Zafixujeme, co jsme dosáhli:

IF (
  coalesce(NEW."Dokument_"::text, '') IN ('', '"Sada"', '"DokumentPodplatou"') A
  (
    OLD."DokumentNasOrganizace"
  , OLD."Odstraněn"
  , OLD."Datum"
  , OLD."Čas"
  , OLD."OsobaVytvořil"
  ) IS DISTINCT FROM (
    NEW."DokumentNasOrganizace"
  , NEW."Odstraněn"
  , NEW."Datum"
  , NEW."Čas"
  , NEW."OsobaVytvořil"
  )
) THEN ...

A pokud vezmeme v úvahu, že tato triggerová funkce může být aplikována pouze v AKTUALIZACE-triggeru kvůli přítomnosti OLD/NEW v podmínce nejvyšší úrovně, lze tuto podmínku vůbec vyjmout do -podmínky-podmínky, jak bylo ukázáno v #1…

Zdroj: habr.com