ProHoster > Blog > Tsamaiso > PostgreSQL Antipatterns: Tlhahlobo ea Boemo ho SQL

PostgreSQL Antipatterns: Tlhahlobo ea Boemo ho SQL

SQL ha se C++, hape ha se JavaScript. Ka hona, tlhahlobo ea lipolelo tse utloahalang e fapane, 'me sena ha se ntho e tšoanang ho hang:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ha o ntse o ntlafatsa moralo oa ts'ebetso oa potso ea PostgreSQL e ka "roala bocha" maemo a tšoanang, u se ke ua bala leha e le efe ea tsona bakeng sa litlaleho tsa motho ka mong, bua ka boemo ba index e sebelisitsoeng ... Ka bokhutšoanyane, tsela e bonolo ka ho fetisisa ke ho nka hore u ha e kgone ho laola tatellano eo li tla ba ka eona (le hore na li tla baloa ho hang) lekana maemo.

Ka hona, haeba u ntse u batla ho laola lintho tse tlang pele, u hloka ho hlophisa etsa hore maemo ana a seke a lekana e nang le maemo dipolelo и basebetsi.

PostgreSQL Antipatterns: Tlhahlobo ea Boemo ho SQL
Lintlha le ho sebetsa le tsona ke motheo ea rona VLSI complex, kahoo ke habohlokoa haholo ho rona hore ts'ebetso ho tsona e se ke ea etsoa ka mokhoa o nepahetseng feela, empa hape ka katleho. Ha re shebeng mehlala e hlakileng moo liphoso tsa tlhahlobo ea polelo li ka etsoang, le moo ho leng bohlokoa ho ntlafatsa ts'ebetso ea tsona.

#0: RTFM

Ho qala mohlala ho tsoa litokomaneng:

Ha taelo ea tlhahlobo e le ea bohlokoa, e ka lokisoa ka moaho CASE. Mohlala, mokhoa ona oa ho qoba karohano ka zero polelong WHERE ke sa tšepahaleng:

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

Khetho e bolokehileng:

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

Kaho e sebelisitsoeng CASE e sireletsa polelo hore e se ke ea ntlafatsoa, ​​kahoo e lokela ho sebelisoa feela ha ho hlokahala.

#1: boemo ba ho qala

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

Ntho e 'ngoe le e' ngoe e bonahala e shebahala e le ntle, empa ... Ha ho motho ea tšepisang hore ba tsetelitsoeng SELECT e ke ke ea etsoa haeba boemo ba pele bo fosahetse. Lokisa ka sehlaha IF:

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

Joale a re shebeng ka hloko - 'mele oohle oa ts'ebetso ea trigger o ile oa "phutheloa" ka hare IF. 'Me sena se bolela hore ha ho letho le re thibelang ho tlosa boemo bona ts'ebetsong e sebelisoang WHEN-maemo:

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

Mokhoa ona o u lumella ho boloka mehloli ea seva ka tiisetso haeba boemo bo le leshano.

#2: KAPA/LE ketane

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

Ho seng joalo, e ka fumanoa ka bobeli EXISTS e tla ba 'nete, empa bobedi di tla bolawa.

Empa haeba re tseba hantle hore e 'ngoe ea tsona ke "'nete" hangata (kapa "leshano" - bakeng sa AND-ketane) - na hoa khoneha ho "eketsa bohlokoa ba eona" ka tsela e itseng e le hore ea bobeli e se ke ea etsoa hape?

Hoa etsahala hore hoa khoneha - mokhoa oa algorithmically o haufi le sehlooho sa sehlooho PostgreSQL Antipatterns: Keno e sa tloaelehang e fihla bohareng ba JOIN.

Ha re "tsubelle tlas'a CASE" maemo ana ka bobeli:

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

Tabeng ena, ha rea ​​ka ra hlalosa ELSE-value, ke hore, haeba maemo a mabeli a fosahetse CASE e tla khutla NULL, e hlalosoang e le FALSE в WHERE- maemo.

Mohlala ona o ka kopanngoa ka tsela e 'ngoe - ho latsoa le ho mebala:

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

#3: mokhoa oa ho [se] ngole maemo

Re qetile matsatsi a mabeli re ntse re hlahloba mabaka a "ho makatsa" ho tsosa moferefere ona - a re boneng hore na ke hobane'ng.

Mohloli:

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

Bothata #1: Ho se lekane ha ho ikarabelle bakeng sa NULL

Ha re nke hore tsohle OLD- masimo a bohlokoa NULL. Ho tla etsahala'ng?

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

Le ho tloha ntlheng ea ho sebetsa maemo NULL lekanang FALSE, joalokaha ho boletsoe ka holimo.

u etsa qeto ea: sebelisa opareitara IS DISTINCT FROM от ROW-opereishene, ho bapisa litlaleho tsohle hang-hang:

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

Palo ea bothata 2: ts'ebetsong e fapaneng ea ts'ebetso e ts'oanang

Bapisa:

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

Hobaneng ho na le matsete a eketsehileng SELECT? Mosebetsi to_regclass? Hobaneng ho fapane...

Ha re lokise:

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

Bothata #3: bool precedence

Ha re hlophise mohloli:

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

Oops ... Ha e le hantle, ho ile ha fumaneha hore tabeng ea 'nete ea leha e le efe ea maemo a mabeli a pele, boemo bohle bo fetoha. TRUE, ho hlokomoloha ho se lekane. Mme sena ha se seo re neng re se batla ho hang.

Ha re lokise:

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

Bothata #4 (bonyane): bo rarahaneng KAPA boemo ba tšimo e le 'ngoe

Haele hantle, re bile le mathata ho No. 3 hantle hobane ho ne ho na le maemo a mararo. Empa ho e-na le bona, u ka khona ho feta ka e le 'ngoe, u sebelisa mochine coalesce ... IN:

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

Ho joalo le ka rōna NULL "tshwara", le ho rarahana OR Ha ua tlameha ho qabana ka masakaneng.

Kakaretso

Ha re lokise seo re nang le sona:

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

'Me ka lebaka la hore ts'ebetso ena ea trigger e ka sebelisoa feela ho UPDATEqhoma ka baka la boteng OLD/NEW boemong ba boemo bo holimo, joale boemo bona bo ka nkoa ka kakaretso WHEN-emo joalo ka ha ho bonts'itsoe ho # 1 ...

Source: www.habr.com

Eketsa ka tlhaloso