ProHoster > Ibhulogi > Ulawulo > I-PostgreSQL Antipatterns: Uvavanyo lwemeko kwi-SQL

I-PostgreSQL Antipatterns: Uvavanyo lwemeko kwi-SQL

I-SQL ayiyo C++, kwaye ayiyoJavaScript. Ke ngoko, ukubalwa kwamazwi anengqiqo kwenzeka ngokwahlukileyo, kwaye oku akunjalo konke konke:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Kwinkqubo yokuphucula isicwangciso sokwenza umbuzo wePostgreSQL "ingakwazi ukulungisa kwakhona" iimeko ezifanayo, musa ukubala ezinye zazo kwiirekhodi ezizimeleyo, zinxulumanise nemiqathango yesalathiso esisetyenzisiweyo... Ngamafutshane, eyona ndlela ilula kukucinga ukuba ayikwazi ukulawula ziya kuba njani na (kwaye nokuba ziya kubalwa kwaphela) ngokulinganayo iimeko.

Ke ngoko, ukuba usafuna ukulawula okuphambili, kufuneka uyicwangcise yenza ezi meko zingalingani usebenzisa iimeko iintetho и abaqhubi.

I-PostgreSQL Antipatterns: Uvavanyo lwemeko kwi-SQL
Idatha kunye nokusebenza nabo kusisiseko yethu VLSI complex, ngoko ke kubaluleke kakhulu kuthi ukuba ukusebenza kuzo kungenziwa ngokuchanekileyo kuphela, kodwa nangokufanelekileyo. Makhe sijonge imizekelo ethile apho iimpazamo ekubaleni intetho zinokwenziwa, kwaye apho kufanelekile ukuphucula ukusebenza kwazo.

#0: RTFM

Ukuqala umzekelo ovela kumaxwebhu:

Xa umyalelo wovavanyo ubalulekile, unokubanjwa kusetyenziswa ulwakhiwo CASE. Umzekelo, le yindlela yokuphepha ukwahlukana ngo-zero kwisivakalisi WHERE ungathembekanga:

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

Indlela ekhuselekileyo:

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

Uyilo olusetyenziswe ngale ndlela CASE ikhusela intetho kulungiselelo, ngoko ke kufuneka isetyenziswe kuphela xa kukho imfuneko.

#1: imeko yokuqalisa

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

Yonke into ibonakala ibonakala ilungile, kodwa ... Akukho mntu uthembisa ukuba utyalo-mali SELECT ayizukwenziwa ukuba imeko yokuqala ayiyonyani. Masiyilungise nge inendlwane IF:

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

Ngoku makhe sijonge ngononophelo - wonke umzimba wesixhobo sokuqhumisela "usongelwe" ngaphakathi IF. Oku kuthetha ukuba akukho nto isithintela ekususeni le meko kwinkqubo esetyenziswayo WHEN-imiqathango:

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

Le ndlela iqinisekisiwe ukugcina izixhobo zeseva xa imeko ibubuxoki.

#2: OKANYE/KUNYE chain

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

Ngaphandle koko, unokugqiba zombini EXISTS iya kuba "yinyani", kodwa zombini ziya kuzaliseka.

Kodwa ukuba siyazi ngokuqinisekileyo ukuba enye yazo "yinyani" kaninzi (okanye "ubuxoki" - kuba AND-amatyathanga) - ngaba kunokwenzeka ukuba ngandlela-thile "ukwandisa ukubaluleka kwayo" ukuze okwesibini kungaphinda kuqhutywe kwakhona?

Kuvela ukuba kunokwenzeka - indlela ye-algorithmic isondele kwisihloko senqaku I-PostgreSQL Antipatterns: irekhodi elinqabileyo liya kufikelela embindini we-JOIN.

Masivele “sityhale” zombini ezi meko phantsi kwe-CASE:

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

Kule meko khange sichaze ELSE-ixabiso, oko kukuthi, ukuba zombini iimeko zibubuxoki CASE uya kubuya NULL, etolikwa ngokuthi FALSE в WHERE-imiqathango.

Lo mzekelo unokudibaniswa ngezinye iindlela - kuxhomekeke kwincasa kunye nombala:

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

#3: njani [hayi] ukubhala iimeko

Sichithe iintsuku ezimbini sihlalutya izizathu zokusebenza "okungaqhelekanga" kwesi siqhushumbisi - masibone ukuba kutheni.

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

Ingxaki #1: ukungalingani akuhloniphi i-NULL

Makhe sicinge ukuba yonke into OLD-amabala anentsingiselo NULL. Kuya kwenzeka ntoni?

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

Kwaye ukusuka kwindawo yokujonga ukusebenza ngaphandle kweemeko NULL ngokulinganayo FALSE, njengoko kukhankanyiwe ngasentla.

Isisombululo: sebenzisa umsebenzisi IS DISTINCT FROM ukusuka ROW-umqhubi, uthelekisa iirekhodi zonke ngaxeshanye:

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

Ingxaki #2: ukuphunyezwa okwahlukileyo kokusebenza okufanayo

Masithelekise:

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

Kutheni kukho utyalo-mali olongezelelweyo apha? SELECT? Umsebenzi to_regclass? Kutheni yahlukile?..

Masilungise:

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

Ingxaki #3: umba ophambili wemisebenzi yebhool

Masifomethe imvelaphi:

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

Oops ... Enyanisweni, kwavela ukuba nayiphi na imiqathango emibini yokuqala iyinyani, yonke imeko iyajika ibe TRUE, ngaphandle kokuqwalasela ukungalingani. Kwaye oku ayisiyiyo kwaphela into ebesiyifuna.

Masilungise:

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

Ingxaki #4 (incinci): intsonkothile OKANYE imeko yendawo enye

Enyanisweni, saba neengxaki kwiNombolo yesi-3 ngokuchanekileyo ngenxa yokuba kwakukho iimeko ezintathu. Kodwa endaweni yabo ungadlula ngenye, usebenzisa umatshini coalesce ... IN:

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

Ngoko ke NULL "Siza kubamba", kwaye kunzima OR Akukho mfuneko yokubiyela ngezibiyeli.

Iyonke

Masibhale into esinayo:

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

Kwaye ukuba ucinga ukuba lo msebenzi wokuqalisa unokusetyenziswa kuphela UPDATE-ukuvusa ngenxa yokubakho OLD/NEW kwimeko yomgangatho ophezulu, ke le meko ingafakwa ngokubanzi kuyo WHEN-imeko, njengoko kubonisiwe kwi-#1...

umthombo: www.habr.com

Yongeza izimvo