🥇PostgreSQL Antipatterns: Iloiloga Tulaga ile SQL

SQL e le o le C++, e le o le JavaScript. O le mea lea, o le iloiloga o faʻamatalaga talafeagai e ese, ma e le o le mea lava lea e tasi:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

A'o fa'amalieina le fuafuaga fa'atino o se fesili PostgreSQL e mafai ona "toe fetuunai" tulaga tutusa, aua neʻi faʻatatauina se tasi oi latou mo faʻamaumauga taʻitasi, faʻasino i le tulaga o le faasino igoa faʻatatau ... I se faapuupuuga, o le auala pito sili ona faigofie o le faʻapea o oe. le mafai ona pulea o le faasologa o le a i ai (ma pe o le a fuafuaina uma) tutusa tulaga.

O le mea lea, afai e te manaʻo pea e pulea le faʻamuamua, e tatau ona e faʻatulagaina fai ia tulaga le tutusa ma tuutuuga faʻaaliga и tagata e faafoeina.

Faʻamaumauga ma galulue faʻatasi ma i latou o le faʻavae o le matou VLSI complex, o lea e taua tele mo i tatou le faʻatinoina o latou gaioiga e le gata ina saʻo, ae faʻapea foi ma le lelei. Se'i o tatou va'ava'ai i fa'ata'ita'iga mautu e mafai ona faia ai mea sese i le iloiloga o fa'amatalaga, ma po'o fea e aoga ai le fa'aleleia atili o lo latou lelei.

#0: RTFM

Amata fa'ata'ita'iga mai fa'amaumauga:

A taua le fa'atonuga o le iloiloga, e mafai ona fa'amauina ma le fau CASE. Mo se faʻataʻitaʻiga, o le auala lea e aloese ai mai le vaevaeina i le zero i se fuaiupu WHERE le faatuatuaina:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Filifiliga saogalemu:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Le fausiaina na faʻaaogaina CASE puipuia le fa'amatalaga mai le fa'alelei, o lea e tatau ai ona fa'aaoga pe a mana'omia.

#1: tulaga fa'aoso

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

O mea uma e foliga mai e foliga lelei, ae ... E leai se tasi na folafola mai o le tupe teu SELECT e le fa'ataunu'uina pe a sese le tulaga muamua. Fa'amau i faamoega IF:

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

Sei o tatou vaʻavaʻai totoʻa - o le tino atoa o le gaioiga na faʻaalia ua "afi" i totonu IF. Ma o lona uiga e leai se mea e taofia ai i tatou mai le aveeseina o lenei tulaga mai le faʻaogaina o le faʻaogaina WHEN-tulaga:

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

O lenei faiga e mafai ai e oe ona faʻasaoina punaoa a le server ma se faʻamaoniga pe afai e sese le tulaga.

#2: POO/MA filifili

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

A leai, e mafai ona maua e lua EXISTS o le a moni, ae o le a fasiotia uma.

Ae afai tatou te iloa mautinoa o se tasi oi latou e "moni" e sili atu ona tele (poʻo "sese" - mo AND-chains) - pe mafai ea ona "faʻateleina lona faʻamuamua" ina ia le toe faia le lona lua?

E foliga mai e mafai - o le algorithmically approach e latalata i le autu o le tusiga PostgreSQL Antipatterns: E seasea ulufale e oo i le ogatotonu o se SOI.

Sei o tatou "tuu i lalo o le CASE" o nei tulaga uma e lua:

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

I lenei tulaga, matou te leʻi faʻamatalaina ELSE-taua, o lona uiga, pe afai o tulaga uma e lua e sese CASE o le a toe foi mai NULL, lea e faauigaina faapea FALSE в WHERE- tulaga.

O lenei faʻataʻitaʻiga e mafai ona tuʻufaʻatasia i se isi auala - e tofo ma lanu:

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

#3: faʻafefea ona [le] tusia tulaga

Na matou faʻaaluina aso e lua i le suʻesuʻeina o mafuaʻaga mo le "ese" faʻaosoina o lenei faʻaoso - seʻi o tatou vaʻai pe aisea.

Punavai:

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

Fa'afitauli #1: Le tutusa e le fa'atatau mo NULL

Sei o tatou manatu o mea uma lava OLD- taua fanua NULL. O le a le mea o le a tupu?

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

Ma mai le vaaiga o le galue i tulaga NULL tutusa FALSE, e pei ona taʻua i luga.

faaiuga: fa'aoga fa'aoga IS DISTINCT FROM от ROW-operator, faʻatusatusa faʻamaumauga atoa i le taimi e tasi:

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

Fa'afitauli numera 2: ese'ese le fa'atinoga o le fa'atinoga tutusa

Faatusatusa:

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

Aisea e iai ni tupe fa'aopoopo SELECT? O se galuega to_regclass? Aisea ua ese ai...

Sei o tatou faaleleia:

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

Fa'afitauli #3: fa'amuamua bool

Sei o tatou faavasega le puna:

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

Oi ... O le mea moni, na aliali mai i le tulaga o le moni o soʻo se tasi o tulaga muamua e lua, o le tulaga atoa e liliu i totonu. TRUE, le amanaia o le le tutusa. Ma e le o le mea tonu lea na matou mananao ai.

Sei o tatou faaleleia:

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

Fa'afitauli #4 (la'ititi): lavelave POO tulaga mo le fanua e tasi

O le mea moni, sa i ai a matou faʻafitauli i le Numera 3 ona e tolu tulaga. Ae nai lo latou, e mafai ona e maua i le tasi, faʻaaoga le masini coalesce ... IN:

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

E faapena foi i tatou NULL "pu'e", ma faigata OR E te le tau popolevale i puipui.

Aofaʻi

Sei o tatou faaleleia mea na tatou maua:

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

Ma tuʻuina atu e naʻo le faʻaaogaina o lenei gaioiga e mafai ona faʻaaogaina UPDATEfa'aoso ona o le iai OLD/NEW i le tulaga maualuga, ona mafai lea ona ave i fafo lenei tulaga WHEN-tulaga e pei ona fa'aalia ile #1...

puna: www.habr.com

PostgreSQL Antipatterns: Iloiloga Tulaga ile SQL