ProHoster > Блог > Whakahaerenga > PostgreSQL Antipatterns: Aromātai Tikanga i roto i te SQL

PostgreSQL Antipatterns: Aromātai Tikanga i roto i te SQL

Ko SQL ehara i te C++, ehara i te JavaScript. Na reira, he rereke te ahua o te tatauranga o nga korero arorau, a ehara tenei i te mea kotahi:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

I roto i te tukanga o te arotau i te mahere mahi uiui PostgreSQL ka taea te "whakarite" tikanga rite, kaua e tatau etahi o aua rekoata mo nga rekoata takitahi, hono atu ki nga tikanga o te taurangi tono... I roto i te poto, ko te huarahi ngawari ki te whakaaro ko koe e kore e taea te whakahaere he aha te raupapa ka mahia (mehemea ka tatauhia) riterite tikanga.

No reira, ki te hiahia tonu koe ki te whakahaere kaupapa matua, me hanga e koe kia taurite enei tikanga te whakamahi herenga kīanga и kaiwhakahaere.

PostgreSQL Antipatterns: Aromātai Tikanga i roto i te SQL
Ko nga raraunga me te mahi tahi me ratou ko te turanga to tatou matatini VLSI, no reira he mea tino nui ki a maatau kia mahia nga mahi ki runga i a raatau kaore i te tika anake, engari he pai hoki. Kia titiro tatou ki nga tauira motuhake ka taea te hapa i te tatau ki nga korero, me nga waahi ka pai ake te whakapai ake i to raatau mahi.

#0: RTFM

Ka timata tauira mai i nga tuhinga:

Ina he mea nui te raupapa o te arotake, ka taea te hopu ma te whakamahi i te hanga CASE. Hei tauira, he huarahi tenei hei karo i te wehenga ma te kore i roto i te rerenga korero WHERE kore pono:

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

Kōwhiringa haumaru:

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

Ko te hoahoa i whakamahia penei CASE ka tiaki i te korero mai i te arotautanga, na me whakamahi ina tika.

#1: huru keu

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

Ko nga mea katoa he ahua pai, engari ... Kaore tetahi e kii ana ko te haumi SELECT e kore e mahia ki te he te tikanga tuatahi. Kia whakatika tatou ki kohanga IF:

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

Inaianei kia ata titiro - kua "takai" te tinana katoa o te mahi keu IF. Ko te tikanga kaore he mea hei aukati i a maatau ki te tango i tenei ahuatanga mai i te tikanga whakamahi WHEN-nga tikanga:

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

Ka whakamanahia tenei huarahi ki te tiaki i nga rauemi a te tūmau ina he teka te ahuatanga.

#2: RĀNEI/ME te mekameka

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

Ki te kore, ka taea e koe te mutunga ki nga mea e rua EXISTS ka "pono", engari ka tutuki nga mea e rua.

Engari ki te tino mohio tatou ko tetahi o ratou he "pono" he maha ake (he "he" ranei - mo AND-chains) - ka taea pea te "whakanui ake i tana kaupapa matua" kia kore ai te tuarua e mate ano?

Ka puta mai ka taea - ko te huarahi algorithmic e tata ana ki te kaupapa o te tuhinga PostgreSQL Antipatterns: he rekoata onge ka tae ki waenganui o te HOI.

Me "pupuhi" enei tikanga e rua i raro i te CASE:

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

I tenei keehi kaore matou i tautuhi ELSE-uara, ara, ki te hee nga tikanga e rua CASE ka hoki mai NULL, e whakamaoritia ana hei FALSE в WHERE-nga tikanga.

Ka taea te whakakotahi i tenei tauira ki etahi atu huarahi - i runga i te reka me te tae:

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

#3: me pehea te tuhi tikanga

E rua nga ra i noho taatau ki te tarai i nga take mo te mahi "ahurei" o tenei keu - me kite he aha.

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

Raruraru #1: karekau te riterite e aro ki te NULL

Whakaarohia nga mea katoa OLD-he tikanga nga mara NULL. Ka ahatia?

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

A mai i te tirohanga o te mahi i nga tikanga NULL ōrite FALSE, pera me te korero i runga ake nei.

whakatau: whakamahi kaiwhakahaere IS DISTINCT FROM i ROW-kaiwhakahaere, whakataurite i nga rekoata katoa i te wa kotahi:

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

Raruraru #2: rereke nga whakatinanatanga o te mahi kotahi

Whakatauritehia:

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

He aha te mea he moni taapiri kei konei? SELECT? He mahi to_regclass? He aha i rereke ai?..

Kia whakatika tatou:

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

Raruraru #3: te kaupapa matua o nga mahi bool

Me whakahōputu te pūtake:

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

Aue... Inaa, i puta mai mena he pono tetahi o nga tikanga tuatahi e rua, ka huri te ahuatanga katoa TRUE, me te kore e whakaaro ki nga rerekee. A ehara tenei i ta matou i hiahia ai.

Kia whakatika tatou:

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

Raruraru #4 (iti): matatini RĀNEI āhua mō te āpure kotahi

Inaa, i raru matou i te Nama 3 na te mea e toru nga tikanga. Engari hei utu mo ratou ka taea e koe te mahi me tetahi, ma te whakamahi i te miihini coalesce ... IN:

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

Na matou NULL "ka mau tatou", me te uaua OR Kare he take ki te taiapa me nga taiapa.

Te tapeke

Me tuhi ta tatou i whiwhi:

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

A, ki te whakaaro koe ka taea anake te whakamahi i tenei mahi keu UPDATE-keu na te waatea OLD/NEW i roto i nga ahuatanga o runga, katahi ka taea te whakauru i tenei ahuatanga ki roto WHEN-ahua, pera me te whakaatu i te #1...

Source: will.com

Tāpiri i te kōrero