🥇PostgreSQL antipatterns: állapotértékelés SQL-ben

Az SQL nem C++, és nem is JavaScript. Ezért a logikai kifejezések értékelése más, és ez egyáltalán nem ugyanaz:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Egy PostgreSQL lekérdezés végrehajtási tervének optimalizálása közben tetszőlegesen "átrendezheti" az egyenértékű feltételeket, ne számítsa ki egyiket sem az egyes rekordokra, hivatkozzon az alkalmazott index feltételére ... Röviden, a legegyszerűbb, ha feltételezi, hogy nem tudja kezelni milyen sorrendben lesznek (és hogy egyáltalán kiszámolják-e) egyenlő körülmények.

Ezért, ha továbbra is szeretné kezelni a prioritást, szerkezetileg kell egyenlőtlenné tenni ezeket a feltételeket feltételes kifejezéseket и szereplők.

Az adatok és a velük való munka az alap VLSI komplexumunkból, ezért nagyon fontos számunkra, hogy a rajtuk végzett műveletek ne csak helyesen, hanem hatékonyan is történjenek. Nézzünk konkrét példákat, ahol a kifejezéskiértékelésben hibázhatnak, és hol érdemes ezek hatékonyságát javítani.

#0: RTFM

Indulás példa a dokumentációból:

Ha fontos az értékelés sorrendje, azt a konstrukcióval rögzíthetjük CASE. Például így elkerülhető a nullával való osztás egy mondatban WHERE megbízhatatlan:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Biztonságos lehetőség:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Az alkalmazott konstrukció CASE megvédi a kifejezést az optimalizálástól, ezért csak szükség esetén szabad használni.

#1: trigger feltétel

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

Úgy tűnik, minden jól néz ki, de... Senki nem ígéri, hogy a befektetett SELECT nem hajtódik végre, ha az első feltétel hamis. Javítsa meg azzal beágyazott IF:

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

Most nézzük meg alaposan - kiderült, hogy a trigger funkció egész teste „be van csomagolva”. IF. Ez pedig azt jelenti, hogy semmi sem akadályoz meg bennünket abban, hogy ezt a feltételt eltávolítsuk az eljárásból WHEN-körülmények:

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

Ez a megközelítés lehetővé teszi a kiszolgáló erőforrásainak garanciális megtakarítását, ha a feltétel hamis.

#2: VAGY/ÉS lánc

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

Ellenkező esetben meg lehet szerezni, hogy mindkettő EXISTS igaz lesz, de mindkettőt kivégzik.

De ha biztosan tudjuk, hogy valamelyik közülük sokkal gyakrabban „igaz” (vagy „hamis” – mert AND-chains) - lehet valahogy "növelni a prioritását", hogy a második ne kerüljön végrehajtásra még egyszer?

Kiderült, hogy lehetséges - az algoritmikus megközelítés közel áll a cikk témájához PostgreSQL antipatterns: A ritka bejegyzés eléri a JOIN közepét.

Tegyük a CASE alá mindkét feltételt:

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

Ebben az esetben nem határoztuk meg ELSE-érték, vagyis ha mindkét feltétel hamis CASE vissza fog térni NULL, amelyet úgy értelmezünk FALSE в WHERE- feltételek.

Ez a példa más módon is kombinálható - ízlés és színezés szerint:

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

#3: hogyan [ne] írjunk feltételeket

Két napot töltöttünk azzal, hogy elemezzük ennek a triggernek a „furcsa” kioldásának okait – lássuk, miért.

Forrás:

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

1. probléma: Az egyenlőtlenség nem a NULL-t jelenti

Tegyük fel, hogy minden OLD- a mezők számítottak NULL. Mi fog történni?

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

És a feltételek kidolgozása szempontjából NULL egyenértékű FALSE, fent említett.

döntés: operátor használata IS DISTINCT FROM -tól ROW-operátor, a teljes rekordok egyidejű összehasonlítása:

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

2. számú probléma: ugyanazon funkció eltérő megvalósítása

Összehasonlítsuk:

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

Miért vannak extra beruházások? SELECT? Egy funkció to_regclass? Miért más...

Javítsuk ki:

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

3. probléma: logikai elsőbbség

Formázzuk a forrást:

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

Hoppá... Valójában kiderült, hogy az első két feltétel bármelyikének igaza esetén a teljes feltétel TRUE, figyelmen kívül hagyva az egyenlőtlenségeket. És egyáltalán nem ezt akartuk.

Javítsuk ki:

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

4. probléma (kicsi): összetett VAGY feltétel egy mezőre

Igazából a 3-asnál pont azért voltak problémáink, mert három feltétel volt. De helyettük meg lehet boldogulni eggyel, a mechanizmus segítségével coalesce ... IN:

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

Így vagyunk mi is NULL "fogás", és összetett OR Nem kell a zárójelekkel babrálni.

Összességében

Javítsuk ki, amit kaptunk:

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

És mivel ez a trigger funkció csak akkor használható UPDATEjelenléte miatt kiváltó OLD/NEW felső szintű állapotban, akkor ez az állapot általában kivehető WHEN- az 1-es állapotú...

Forrás: will.com

PostgreSQL antipatterns: állapotértékelés SQL-ben