🥇PostgreSQL Antipatterns: Kūlana Kūlana ma SQL

ʻAʻole ʻo SQL ʻo C++, ʻaʻole hoʻi ʻo JavaScript. No laila, ʻokoʻa ka loiloi o nā ʻōlelo loiloi, a ʻaʻole like kēia:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

ʻOiai e hoʻolālā ana i ka hoʻolālā hoʻokō o kahi nīnau PostgreSQL hiki ke "hoʻonohonoho hou" i nā kūlana like, mai helu i kekahi o ia mau mea no na mooolelo hookahi, e nana i ke ano o ka index i noiia ... I ka pōkole, ʻo ke ala maʻalahi ke manaʻo ʻoe hiki ole ke hooponopono ka hoʻonohonoho ʻana o lākou (a inā e helu ʻia lākou) kaulike kūlana.

No laila, inā makemake ʻoe e hoʻokele i ka mea nui, pono ʻoe e structurally hana i keia mau kulana like ole me ke kūlana nā hōʻike и nā mea hoʻohana.

ʻO kaʻikepili a me ka hana pū me lākou ke kumu o kā mākou paʻakikī VLSI, no laila he mea koʻikoʻi iā mākou ke hana ʻia nā hana ma luna o lākou ʻaʻole pono wale nō, akā maikaʻi nō hoʻi. E nānā kākou i nā hiʻohiʻona koʻikoʻi kahi e hiki ai ke hana hewa i ka loiloi ʻōlelo, a ma hea kahi kūpono e hoʻomaikaʻi ai i ko lākou pono.

#0: RTFM

E hoomaka ana laʻana mai ka palapala:

Ke koʻikoʻi ke kauoha o ka loiloi, hiki ke hoʻopaʻa ʻia me ke kūkulu CASE. No ka laʻana, ʻo kēia ala e pale ai i ka mahele ʻana me ka zero i loko o kahi ʻōlelo WHERE hilinaʻi ʻole:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Koho palekana:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
ʻO ke kūkulu ʻana i hoʻohana ʻia CASE pale i ka ʻōlelo mai ka optimization, no laila e hoʻohana wale ʻia inā pono.

#1: kūlana hoʻāla

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

ʻIke maikaʻi nā mea a pau, akā ... ʻAʻohe mea hoʻohiki i ka hoʻopukapuka kālā SELECT ʻaʻole e hoʻokō ʻia inā hewa ka ʻōlelo mua. Hoʻoponopono me pūnana IF:

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

I kēia manawa, e nānā pono kākou - ʻo ke kino holoʻokoʻa o ka hana hoʻoheheʻe i hoʻololi ʻia i "uhi" i loko IF. A ʻo ia hoʻi, ʻaʻohe mea e pale iā mākou mai ka wehe ʻana i kēia kūlana mai ke kaʻina hana WHEN-kūlana:

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

ʻO kēia ala e hiki ai iā ʻoe ke mālama i nā kumuwaiwai kikowaena me kahi hōʻoia inā hewa ke kūlana.

#2: A I ʻole/AND kaulahao

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

A i ʻole, hiki ke loaʻa kēlā ʻelua EXISTS e oiaio no, aka e pepehi ʻia lāua ʻelua.

Akā inā ʻike maopopo mākou he "ʻoiaʻiʻo" kekahi o lākou i ʻoi aku ka pinepine (a i ʻole "hewa" - no ka mea AND-chains) - hiki paha ke "hoʻonui i kāna mea nui" i ʻole e hoʻokō hou ʻia ka lua?

Ua hoʻololiʻia he hiki -ʻo ke ala algorithmically kokoke i ke kumuhana o kaʻatikala Nā Antipatterns PostgreSQL: Hiki ke komo i waena o kahi JOIN.

E "hoʻokuʻu wale ma lalo o CASE" o kēia mau kūlana ʻelua:

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

I kēia hihia, ʻaʻole mākou i wehewehe ELSE-waiwai, ʻo ia hoʻi, inā hewa nā kūlana ʻelua CASE e hoʻi mai NULL, ka mea i hoakakaia FALSE в WHERE- kūlana.

Hiki ke hoʻohui ʻia kēia hiʻohiʻona ma kahi ʻano ʻē aʻe - e ʻono a me ke kala:

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

#3: pehea [ʻaʻole] e kākau i nā kūlana

Ua hoʻohana mākou i ʻelua mau lā ma ka nānā ʻana i nā kumu o ka hoʻomaka ʻana o ka "ʻano" - e ʻike i ke kumu.

Puna:

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

Pilikia #1: ʻAʻole helu ka like ʻole no NULL

E noʻonoʻo kākou i nā mea a pau OLD- mea nui nā māla NULL. He aha ka hopena?

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

A mai ka manaʻo o ka hana ʻana i nā kūlana NULL mea like FALSE, e like me ka mea i oleloia maluna.

olelo hooholo: hoʻohana i ka mea hoʻohana IS DISTINCT FROM от ROW-operator, hoʻohālikelike i nā moʻolelo holoʻokoʻa i ka manawa hoʻokahi:

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

Helu 2 pilikia: hoʻokō like ʻole o ka hana like

Hoʻohālikelike

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

No ke aha i loaʻa ai nā hoʻopukapuka hou SELECT? He hana to_regclass? No ke aha he ʻokoʻa ...

E hoʻoponopono kākou:

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

Pilikia #3: ka mua bool

E hoʻopololei i ke kumu:

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

ʻAe ... ʻO kaʻoiaʻiʻo, uaʻikeʻia ma ka hihia o kaʻoiaʻiʻo o kekahi o nā kūlana muaʻelua, ua lilo ke kūlana holoʻokoʻa i loko. TRUE, me ka malama ole i na like ole. A ʻaʻole kēia ka mea a mākou i makemake ai.

E hoʻoponopono kākou:

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

Pilikia #4 (liʻiliʻi): paʻakikī A i ʻole kūlana no hoʻokahi kahua

ʻOiaʻiʻo, loaʻa iā mākou nā pilikia ma ka helu 3 no ka mea aia ʻekolu mau kūlana. Akā ma kahi o lākou, hiki iā ʻoe ke loaʻa me hoʻokahi, me ka hoʻohana ʻana i ka mīkini coalesce ... IN:

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

Pēlā nō mākou NULL "hopu", a paʻakikī OR ʻAʻole pono ʻoe e hoʻopaʻapaʻa me nā pale.

Hōʻuluʻulu

E hoʻoponopono i ka mea i loaʻa iā mākou:

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

A hāʻawi ʻia hiki ke hoʻohana wale ʻia kēia hana trigger i loko UPDATEhoʻomaka ma muli o ke alo OLD/NEW i ke kūlana kiʻekiʻe, a laila hiki ke lawe ʻia kēia kūlana i waho WHEN-kūlana e like me ka mea i hōʻike ʻia ma ka #1...

Source: www.habr.com

PostgreSQL Antipatterns: Kūlana Kūlana ma SQL