🥇PostgreSQL Antipatterns: izimo zokubala ku-SQL

I-SQL akuyona i-C++, futhi akuyona i-JavaScript. Ngakho-ke, ukubalwa kwezinkulumo ezinengqondo kwenzeka ngokuhlukile, futhi lokhu akuyona into efanayo:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Enqubweni yokuthuthukisa uhlelo lokubuza imibuzo ye-PostgreSQL angakwazi "ukuhlela kabusha" izimo ezifanayo ngokungafanele, ungabali ezinye zazo kumarekhodi ngamanye, zihlobanise nezimo zenkomba esetshenzisiwe... Ngamafuphi, indlela elula iwukucabanga ukuthi wena ayikwazi ukulawula ziyoba kanjani ngokulandelana (nokuthi zizobalwa nhlobo) kulingana izimo.

Ngakho-ke, uma usafuna ukuphatha okubalulekile, udinga ukukuhlela yenza lezi zimo zingalingani usebenzisa imibandela izinkulumo и opharetha.

Idatha nokusebenza nabo kuyisisekelo i-VLSI complex yethu, ngakho-ke kubaluleke kakhulu kithi ukuthi ukusebenza kuzo kwenziwa hhayi kuphela ngendlela efanele, kodwa futhi ngokuphumelelayo. Ake sibheke izibonelo ezithile lapho kungenziwa khona amaphutha ekubaleni izinkulumo, nalapho kufaneleka khona ukuthuthukisa ukusebenza kahle kwazo.

#0: I-RTFM

Iyaqala isibonelo esivela kumadokhumenti:

Lapho uhlelo lokuhlola lubalulekile, lungabanjwa kusetshenziswa ukwakhiwa CASE. Isibonelo, lena indlela yokugwema ukuhlukaniswa ngoziro emshweni WHERE athembekile:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Inketho ephephile:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Idizayini esetshenziswa ngale ndlela CASE ivikela isisho ekuthuthukisweni, ngakho kufanele sisetshenziswe kuphela uma kunesidingo.

#1: isimo sokucupha

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

Konke kubonakala kubukeka kukuhle, kodwa ... Akekho othembisa ukuthi utshalomali SELECT ngeke isetshenziswe uma isimo sokuqala singamanga. Masiyilungise nge isidleke IF:

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

Manje ake sibheke ngokucophelela - wonke umzimba we-trigger umsebenzi "usongwe" phakathi IF. Lokhu kusho ukuthi akukho okusivimbelayo ekususeni lesi simo enqubweni esetshenziswayo WHEN-izimo:

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

Le ndlela iqinisekisiwe ukulondoloza izinsiza zeseva uma isimo singamanga.

#2: NOMA/FUTHI iketango

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

Uma kungenjalo, ungagcina usunakho kokubili EXISTS kuzoba "yiqiniso", kodwa zombili zizogcwaliseka.

Kodwa uma sazi ngokuqinisekile ukuthi omunye wabo "uyiqiniso" kaningi (noma "amanga" - ngoba AND-amaketanga) - kungenzeka ngandlela thize "ukwandisa ukubaluleka kwayo" ukuze owesibili angaphinde abulawe?

Kuvela ukuthi kungenzeka - indlela ye-algorithmic iseduze nesihloko se-athikili I-PostgreSQL Antipatterns: irekhodi elingandile lizofika phakathi kokuthi JOYINA.

Masivele “sishoshe” yomibili le mibandela ngaphansi kwe-CASE:

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

Kulokhu asizange sichaze ELSE-value, okungukuthi, uma zombili izimo zingamanga CASE izobuya NULL, okuhunyushwa ngokuthi FALSE в WHERE-izimo.

Lesi sibonelo singahlanganiswa ngezinye izindlela - kuye ngokunambitheka nombala:

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

#3: kanjani [hhayi] ukubhala izimo

Sichithe izinsuku ezimbili sihlaziya izizathu zokusebenza “okuxakile” kwalesi sicupha - ake sibone ukuthi kungani.

Umthombo:

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

Inkinga #1: ukungalingani akuhloniphi NULL

Ake sicabange ukuthi konke OLD-izinkambu zazinencazelo NULL. Kuzokwenzekani?

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

Futhi ngokombono wokusebenzela izimo NULL kulingana FALSE, njengoba kushiwo ngenhla.

Isixazululo: sebenzisa opharetha IS DISTINCT FROM kusukela ROW-opharetha, ukuqhathanisa wonke amarekhodi ngesikhathi esisodwa:

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

Inkinga #2: ukusetshenziswa okuhlukile kokusebenza okufanayo

Qhathanisa:

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

Kungani kunotshalomali olwengeziwe lapha? SELECT? Umsebenzi to_regclass? Kungani kuhlukile?..

Masilungise:

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

Inkinga #3: ukubaluleka kokusebenza kwe-bool

Masifomethe umthombo:

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

Eshu... Eqinisweni, kuvele ukuthi uma noma yiziphi izimo ezimbili zokuqala ziyiqiniso, sonke isimo siphenduka sibe TRUE, ngaphandle kokucabangela ukungalingani. Futhi lokhu akukhona neze ebesikufuna.

Masilungise:

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

Inkinga #4 (encane): inkimbinkimbi NOMA isimo senkambu eyodwa

Empeleni, saba nezinkinga kuNombolo 3 ngokuqondile ngoba kwakunemibandela emithathu. Kodwa esikhundleni sazo ungadlula ngomunye, usebenzisa umshini coalesce ... IN:

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

Ngakho thina NULL "Sizobamba", futhi kunzima OR Asikho isidingo sokubiya ngabakaki.

Inani

Masirekhode esinakho:

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

Futhi uma ucabanga ukuthi lo msebenzi wokucupha ungasetshenziswa kuphela ku UPDATE-cupha ngenxa yokuba khona OLD/NEW esimweni sezinga eliphezulu, khona-ke lesi simo ngokuvamile singafakwa kuso WHEN-isimo, njengoba kukhonjisiwe ku-#1...

Source: www.habr.com

I-PostgreSQL Antipatterns: Ukuhlola Izimo ku-SQL