🥇PostgreSQL Antipatterns: Condition Evaluation mu SQL

SQL si C++, komanso JavaScript. Chifukwa chake, kuwunika kwa mawu omveka ndi kosiyana, ndipo izi sizofanana konse:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Pomwe mukukhathamiritsa dongosolo lamachitidwe a PostgreSQL funso akhoza "kukonzanso" zinthu zofanana, musawerengere chilichonse mwazolemba pawokha, tchulani chikhalidwe cha index yomwe imagwiritsidwa ntchito ... Mwachidule, njira yosavuta ndiyo kuganiza kuti inu sindingathe kukwanitsa momwe iwo adzakhalire (ndipo ngati adzawerengedwa konse) ofanana mikhalidwe.

Chifukwa chake, ngati mukufunabe kuyang'anira zoyambira, muyenera kumangoganiza kupanga zinthu izi kukhala zosafanana ndi zovomerezeka mawu и ogwira ntchito.

Deta ndikugwira nawo ntchito ndiye maziko za VLSI complex yathu, kotero ndikofunika kwambiri kwa ife kuti ntchito pa iwo singochitika molondola, komanso mogwira mtima. Tiyeni tiwone zitsanzo zowoneka bwino zomwe zolakwika pakuwunika kwa mawu zitha kupangidwa, komanso komwe kuli koyenera kuwongolera luso lawo.

#0: RTFM

Kuyambira chitsanzo kuchokera zolembedwa:

Pamene dongosolo la kuwunika ndilofunika, likhoza kukhazikitsidwa ndi kumanga CASE. Mwachitsanzo, njira iyi kupewa kugawikana ndi ziro mu chiganizo WHERE osadalirika:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Njira yotetezeka:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Ntchito yomanga CASE imateteza mawuwo kuti asakwaniritsidwe, choncho amayenera kugwiritsidwa ntchito pokhapokha pakufunika.

#1: chikhalidwe choyambitsa

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

Chilichonse chikuwoneka bwino, koma ... Palibe amene amalonjeza kuti ndalamazo SELECT sichidzaphedwa ngati chikhalidwe choyamba chiri chabodza. Konzani ndi nested IF:

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

Tsopano tiyeni tiyang'ane mosamala - thupi lonse la choyambitsa ntchito linasanduka "lokulungidwa" mkati IF. Ndipo izi zikutanthauza kuti palibe chomwe chingatilepheretse kuchotsa vutoli kuchokera mu ndondomeko yogwiritsira ntchito WHEN-mikhalidwe:

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

Njirayi imakulolani kuti musunge zida za seva ndi chitsimikizo ngati mkhalidwewo ndi wabodza.

#2: KAPENA/NDI unyolo

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

Apo ayi, zikhoza kupezedwa kuti onse EXISTS zidzakhala zoona, koma onse awiri adzaphedwa.

Koma ngati tikudziwa motsimikiza kuti mmodzi wa iwo ndi "woona" nthawi zambiri (kapena "zabodza" - chifukwa AND-maketani) - ndizotheka mwanjira ina "kuwonjezera kufunikira kwake" kuti yachiwiriyo isaphedwenso?

Zikutheka kuti ndizotheka - njira ya algorithmically ili pafupi ndi mutu wa nkhaniyi PostgreSQL Antipatterns: Kulowa kosawerengeka kumafika pakati pa JOIN.

Tiyeni "tikankhire pansi pa CASE" zonsezi:

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

Pankhaniyi, sitinafotokoze ELSE-mtengo, ndiko kuti, ngati zonse zili zabodza CASE adzabwerera NULL, lomwe limamasuliridwa kuti FALSE в WHERE- zikhalidwe.

Chitsanzochi chikhoza kuphatikizidwa mwanjira ina - kulawa ndi mtundu:

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

#3: momwe [osalemba] zikhalidwe

Tidakhala masiku awiri ndikusanthula zifukwa "zachilendo" zoyambitsa izi - tiyeni tiwone chifukwa chake.

Gwero:

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

Vuto #1: Kusafanana sikuwerengera NULL

Tiyerekeze kuti zonse OLD-minda zofunika NULL. Kodi chidzachitike n'chiyani?

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

Ndipo kuchokera kumalingaliro akuchita zinthu NULL ofanana FALSE, monga tafotokozera pamwambapa.

chisankho: ogwiritsa ntchito IS DISTINCT FROM от ROW-othandizira, kufananiza zolemba zonse nthawi imodzi:

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

Vuto nambala 2: kukhazikitsa kosiyana kwa magwiridwe antchito omwewo

Yerekezerani:

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

Chifukwa chiyani pali ndalama zowonjezera SELECT? Ntchito to_regclass? Chifukwa chiyani zili zosiyana...

Tiyeni tikonze:

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

Vuto #3: kutsogola kwa bool

Tiyeni tipange gwero:

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

Oops ... M'malo mwake, zidapezeka kuti ngati chowonadi chilichonse mwamikhalidwe iwiri yoyambirira, mkhalidwe wonsewo umasanduka. TRUE, kunyalanyaza kusagwirizana. Ndipo izi sizomwe tinkafuna.

Tiyeni tikonze:

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

Vuto #4 (laling'ono): zovuta OR chikhalidwe cha gawo limodzi

Kwenikweni, tinali ndi mavuto mu No. 3 ndendende chifukwa panali zikhalidwe zitatu. Koma m'malo mwa iwo, mutha kudutsa ndi imodzi, pogwiritsa ntchito makinawo coalesce ... IN:

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

Momwemonso ife NULL "kugwira", ndi zovuta OR Simuyenera kukangana ndi mabatani.

Chiwerengero

Tiyeni tikonze zomwe tili nazo:

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

Ndipo popeza kuti choyambitsa ichi chitha kugwiritsidwa ntchito mkati UPDATEkulira chifukwa cha kukhalapo OLD/NEW mu chikhalidwe chapamwamba, ndiye kuti vutoli likhoza kuchitidwa WHEN-condition monga zikuwonetsedwa mu # 1 ...

Source: www.habr.com

PostgreSQL Antipatterns: Condition Evaluation mu SQL