🥇PostgreSQL Antipatterns: Condition Evaluation muSQL

SQL haisi C++, uye haisi JavaScript. Naizvozvo, kuongororwa kwemazwi ane musoro kwakasiyana, uye ichi hachisi chinhu chimwe chete zvachose:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ndichiri kugadzirisa chirongwa chekuuraya chePostgreSQL query inogona nechisimba "kurongazve" mamiriro akaenzana, usaverengere chero ipi zvayo yezvinyorwa zvega, tarisa kune mamiriro eiyo inoshandiswa index ... Muchidimbu, nzira iri nyore ndeyekufungidzira kuti iwe haigone kubata kurongeka kwavanenge vari (uye kana vachizoverengerwa zvachose) kuenzana mamiriro.

Naizvozvo, kana iwe uchiri kuda kubata pamberi, iwe unofanirwa kurongeka ita kuti zvinhu izvi zvisaenzana with conditional kutaura и vashandisi.

Data uye kushanda navo ndiyo hwaro yeVLSI yedu yakaoma, saka zvakakosha kwatiri kuti maoparesheni pavari aitwe kwete nemazvo chete, asiwo nemazvo. Ngatitarisei pamienzaniso yekongiri uko zvikanganiso mukuongororwa kwekutaura zvinogona kuitwa, uye pazvinokodzera kuvandudza kugona kwavo.

#0: RTFM

Kutanga muenzaniso kubva pazvinyorwa:

Kana kurongeka kwekuongorora kwakakosha, kunogona kugadziriswa nekuvaka CASE. Semuenzaniso, iyi nzira yekudzivisa kupatsanurwa ne zero mumutsara WHERE kusavimbika:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Safe sarudzo:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Chigadzirwa chinoshandiswa CASE inodzivirira kutaura kubva pakugadzirisa, saka inofanirwa kushandiswa chete kana zvichidikanwa.

#1: trigger mamiriro

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

Zvose zvinoita sezvinoratidzika zvakanaka, asi ... Hapana anovimbisa kuti akaisa mari SELECT haizourawi kana mamiriro ekutanga ari emanyepo. Gadzirisa nayo nested IF:

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

Zvino ngatitarisei zvakanyatsonaka - muviri wese weiyo trigger basa rakazove "rakaputirwa" mukati IF. Uye izvi zvinoreva kuti hapana chinotitadzisa kubvisa mamiriro aya kubva pamaitiro ekushandisa WHEN-conditions:

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

Iyi nzira inokubvumira kuchengetedza server zviwanikwa nevimbiso kana mamiriro acho ari enhema.

#2: KANA/UYE cheni

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

Zvikasadaro, inogona kuwanikwa kuti ese ari maviri EXISTS zvichava zvechokwadi, asi vose vari vaviri vachaurayiwa.

Asi kana tichiziva zvechokwadi kuti mumwe wavo "wechokwadi" kakawanda (kana "nhema" - nokuti AND-cheni) - zvinogoneka here neimwe nzira "kuwedzera kukosha kwayo" kuitira kuti yechipiri irege kuurayiwa zvakare?

Zvinoitika kuti zvinogoneka - iyo algorithmically nzira iri padyo nemusoro wechinyorwa PostgreSQL Antipatterns: Kashoma kupinda kunosvika pakati peJOIN.

Ngatingo "sundidzira pasi peCASE" ese ari maviri aya mamiriro:

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

Panyaya iyi, hatina kutsanangura ELSE-kukosha, kureva, kana zvose zviri zviviri zviri nhema CASE achadzoka NULL, iyo inodudzirwa ichinzi FALSE в WHERE- mamiriro.

Uyu muenzaniso unogona kusanganiswa neimwe nzira - kuravira uye ruvara:

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

#3: sei [kusa] kunyora mamiriro

Takapedza mazuva maviri tichiongorora zvikonzero zve "zvinoshamisa" kukonzeresa kweichi chinokonzeresa - ngatione chikonzero.

Kwakabva:

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

Dambudziko #1: Kusaenzana hakuverengere NULL

Ngatitorei kuti zvose OLD-minda zvine basa NULL. Chii chichaitika?

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

Uye kubva pakuona kwekushanda kunze kwemamiriro ezvinhu NULL yakaenzana FALSE, sezvataurwa pamusoro apa.

chisarudzo: shandisa mushandisi IS DISTINCT FROM от ROW-operator, achienzanisa marekodhi ese kamwechete:

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

Dambudziko nhamba 2: kushandiswa kwakasiyana kwekuita kwakafanana

Enzanisa:

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

Sei kune mamwe mari SELECT? A function to_regclass? Sei zvakasiyana...

Ngatigadzirise:

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

Dambudziko #3: bool precedence

Ngatifomatei source:

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

Oops ... Muchokwadi, zvakazoitika kuti mune chokwadi chechero mamiriro maviri ekutanga, mamiriro ese anoshanduka kuita. TRUE, kusakoshesa kusaenzana. Uye izvi handizvo zvataida.

Ngatigadzirise:

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

Dambudziko #4 (diki): yakaoma OR mamiriro emunda mumwe

Chaizvoizvo, takava nematambudziko muNo. 3 chaizvo nekuti pakanga paine mamiriro matatu. Asi pachinzvimbo chavo, unogona kupfuura nemumwe, uchishandisa michina coalesce ... IN:

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

Ndizvo zvatiriwo NULL "kubata", uye yakaoma OR Haufanire kuita nharo nemaparentheses.

Total

Ngatigadzirise zvatawana:

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

Uye nekupihwa kuti iyi trigger basa inogona kushandiswa chete mukati UPDATEkukanganisa nekuda kwekuvapo OLD/NEW mune yepamusoro-level mamiriro, saka mamiriro aya anogona kazhinji kutorwa kunze WHEN-condition sezvakaratidzwa mu #1 ...

Source: www.habr.com

PostgreSQL Antipatterns: Mamiriro Ekuongorora muSQL