🥇PostgreSQL Antipatterns: kajy fepetra ao amin'ny SQL

SQL dia tsy C++, ary tsy JavaScript. Noho izany, ny kajy ny teny lojika dia mitranga amin'ny fomba hafa, ary tsy mitovy izany:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ao anatin'ny dingan'ny fanatsarana ny drafitry ny fanatanterahana ny fangatahana PostgreSQL afaka "manova" toe-javatra mitovy amin'izany, aza kajy ny sasany amin'izy ireo ho an'ny firaketana tsirairay, ampifandraiso amin'ny fepetran'ny tondro ampiharina... Raha fintinina, ny fomba tsotra indrindra dia ny fiheverana fa ianao tsy mahafehy amin'ny filaharan'izy ireo (ary raha hokajiana mihitsy) mitovy fepetra.

Noho izany, raha mbola te-hitantana ny laharam-pahamehana ianao dia mila manamboatra azy io ataovy tsy mitovy ireo fepetra ireo mampiasa fepetra fomba fiteny и mpandraharaha.

Ny angon-drakitra sy ny fiaraha-miasa amin'izy ireo no fototra ny complex VLSI, noho izany dia zava-dehibe ho antsika ny fampandehanana azy ireo tsy amin'ny fomba marina ihany, fa amin'ny fomba mahomby ihany koa. Andeha hojerentsika ny ohatra manokana izay mety hahatonga ny fahadisoana amin'ny kajy fanehoan-kevitra, ary aiza no mendrika hanatsarana ny fahombiazany.

#0: RTFM

manomboka ohatra avy amin'ny antontan-taratasy:

Rehefa zava-dehibe ny filaharan'ny fanombanana dia azo alaina amin'ny alalan'ny fananganana CASE. Ohatra, fomba iray hialana amin'ny fizarana aotra amin'ny fehezanteny iray izany WHERE tsy azo ianteherana:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Safidy azo antoka:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Ny endrika ampiasaina amin'izany fomba izany CASE miaro ny fitenenana amin'ny fanatsarana, noho izany dia tokony hampiasaina rehefa ilaina izany.

#1: fepetran'ny trigger

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

Toa tsara ny zava-drehetra, saingy ... Tsy misy mampanantena fa ny fampiasam-bola SELECT tsy ho vonoina raha diso ny fepetra voalohany. Aleo amboary amin'ny nested IF:

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

Andeha hojerentsika tsara - ny vatana manontolo amin'ny fiasan'ny trigger dia "voafono". IF. Midika izany fa tsy misy manakana antsika tsy hanala io fepetra io amin'ny fomba fampiasana WHEN- fepetra:

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

Ity fomba fiasa ity dia azo antoka fa hamonjy ny loharanon'ny mpizara rehefa diso ny fepetra.

#2: OR/AND rojo

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

Raha tsy izany dia mety hiafara amin'ny roa ianao EXISTS dia ho "marina", fa samy ho tanteraka.

Saingy raha fantatsika tsara fa ny iray amin'izy ireo dia "marina" matetika kokoa (na "diso" - satria AND-chains) - azo atao ve ny "hampitombo ny laharam-pahamehana" mba tsy hovonoina indray ny faharoa?

Hita fa azo atao izany - ny fomba algorithmika dia akaiky ny lohahevitry ny lahatsoratra PostgreSQL Antipatterns: misy rakitsoratra tsy fahita firy ho tonga eo afovoan'ny JOIN.

Andeha hojerentsika fotsiny ireo fepetra roa ireo amin'ny CASE:

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

Amin'ity tranga ity dia tsy nofaritanay ELSE-value, izany hoe raha diso ny fepetra roa CASE hiverina NULL, izay adika hoe FALSE в WHERE- fepetra.

Ity ohatra ity dia azo atambatra amin'ny fomba hafa - miankina amin'ny tsiro sy ny loko:

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

#3: ahoana [tsy] fanoratana fepetra

Nandany roa andro izahay namakafaka ny anton'ny hetsika "hafahafa" an'ity trigger ity - andeha hojerentsika ny antony.

Loharano:

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

Olana #1: tsy manaja ny NULL ny tsy fitoviana

Alao sary an-tsaina fa ny zava-drehetra OLD- nisy dikany ny saha NULL. Inona no hitranga?

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

Ary avy amin'ny fomba fijery ny miasa ny fepetra NULL mitovy FALSE, araka ny voalaza etsy ambony.

fanapahan-kevitra: mampiasa opérateur IS DISTINCT FROM от ROW-operator, mampitaha ny rakitsoratra manontolo indray mandeha:

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

Olana #2: fampiharana samihafa amin'ny fiasa mitovy

Andeha hampitaha:

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

Nahoana no misy fampiasam-bola fanampiny eto? SELECT? Asa iray to_regclass? Nahoana no tsy mitovy?..

Andeha amboary izany:

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

Olana #3: laharam-pahamehana amin'ny asa bool

Andao amboary ny loharano:

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

Oops... Raha ny marina, hita fa raha marina ny iray amin'ireo fepetra roa voalohany dia mivadika ho ny fepetra iray manontolo TRUE, tsy misy fiheverana ny tsy fitoviana. Ary tsy izany mihitsy no tadiavinay.

Andeha amboary izany:

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

Olana #4 (kely): sarotra OR fepetra ho an'ny saha iray

Raha ny marina, nanana olana izahay tamin'ny laharana faha-3 satria nisy fepetra telo. Fa raha tokony ho azy ireo dia azonao atao ny miala amin'ny iray, amin'ny fampiasana ny mekanika coalesce ... IN:

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

Ka isika NULL "hisambotra izahay", ary sarotra OR Tsy ilaina ny mitabataba amin'ny fononteny.

Итого

Andeha horaketina izay azonay:

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

Ary raha heverinao fa io fiasa trigger io dia tsy azo ampiasaina afa-tsy ao UPDATE- trigger noho ny fisiany OLD/NEW amin'ny toe-javatra ambony, dia azo apetraka amin'ny ankapobeny io fepetra io WHEN-condition, araka ny hita ao amin'ny #1...

Source: www.habr.com

Antipatterns PostgreSQL: fanombanana ny fepetra amin'ny SQL