PostgreSQL Antipatterns: Iṣiro Awọn ipo ni SQL

SQL kii ṣe C ++, kii ṣe JavaScript. Nitorinaa, iṣiro ti awọn ọrọ ọgbọn waye ni oriṣiriṣi, ati pe kii ṣe ohun kanna rara:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Ninu ilana ti iṣapeye ero ipaniyan ibeere PostgreSQL le lainidii “atunto” awọn ipo deede, maṣe ṣe iṣiro diẹ ninu wọn fun awọn igbasilẹ kọọkan, ṣe alaye wọn si awọn ipo ti itọka ti a lo ... Ni kukuru, ọna ti o rọrun julọ ni lati ro pe o ko le sakoso ni aṣẹ wo ni wọn yoo (ati boya wọn yoo ṣe iṣiro rara) dogba awọn ipo.

Nitorinaa, ti o ba tun fẹ ṣakoso pataki, o nilo lati ṣeto rẹ ṣe awọn ipo ko dọgba lilo majemu awọn ikosile и awọn oniṣẹ.

Data ati ṣiṣẹ pẹlu wọn ni ipilẹ eka VLSI wa, Nitorina o ṣe pataki pupọ fun wa pe awọn iṣẹ-ṣiṣe lori wọn ko ṣe deede nikan, ṣugbọn tun daradara. Jẹ ki a wo awọn apẹẹrẹ kan pato nibiti awọn aṣiṣe ni iṣiro awọn ọrọ le ṣee ṣe, ati nibiti o tọ lati mu ilọsiwaju wọn ṣiṣẹ.

#0: RTFM

Bibẹrẹ apẹẹrẹ lati iwe:

Nigba ti aṣẹ ti igbelewọn jẹ pataki, o le wa ni sile nipa lilo awọn ikole CASE. Fun apẹẹrẹ, eyi jẹ ọna lati yago fun pipin nipasẹ odo ninu gbolohun ọrọ kan WHERE ti ko le gbẹkẹle:

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

Aṣayan ailewu:

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

Apẹrẹ ti a lo ni ọna yii CASE ṣe aabo ikosile lati iṣapeye, nitorinaa o yẹ ki o lo nikan nigbati o jẹ dandan.

# 1: okunfa majemu

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

Ohun gbogbo dabi pe o dara, ṣugbọn ... Ko si ẹniti o ṣe ileri pe idoko-owo naa SELECT kii yoo ṣiṣẹ ti ipo akọkọ ba jẹ eke. Jẹ ki a ṣe atunṣe pẹlu iteeye IF:

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

Bayi jẹ ki a wo ni pẹkipẹki - gbogbo ara ti iṣẹ okunfa “ti a we” sinu IF. Eyi tumọ si pe ko si ohun ti o ṣe idiwọ fun wa lati yọ ipo yii kuro ninu ilana lilo WHEN-awọn ipo:

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

Ọna yii jẹ iṣeduro lati ṣafipamọ awọn orisun olupin nigbati ipo naa jẹ eke.

# 2: OR / AND pq

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

Bibẹẹkọ, o le pari pẹlu awọn mejeeji EXISTS yoo jẹ "otitọ", ṣugbọn mejeeji yoo ṣẹ.

Ṣugbọn ti a ba mọ daju pe ọkan ninu wọn jẹ “otitọ” pupọ diẹ sii nigbagbogbo (tabi “eke” - fun AND-awọn ẹwọn) - ṣe o ṣee ṣe lati bakan “pọ si pataki rẹ” ki ekeji ko ba ṣiṣẹ lekan si?

O wa ni jade pe o ṣee ṣe - ọna algorithmic ti sunmọ koko ọrọ naa PostgreSQL Antipatterns: igbasilẹ toje yoo de aarin JOIN kan.

Jẹ ki a kan “sọ” awọn ipo mejeeji labẹ CASE:

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

Ni idi eyi a ko setumo ELSE-iye, iyẹn ni, ti awọn ipo mejeeji ba jẹ eke CASE yoo pada NULL, eyi ti o tumọ bi FALSE в WHERE-awọn ipo.

Apeere yii le ni idapo ni awọn ọna miiran - da lori itọwo ati awọ:

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

# 3: bawo ni [kii ṣe] kọ awọn ipo

A lo ọjọ meji ni itupalẹ awọn idi fun iṣẹ “ajeji” ti okunfa yii - jẹ ki a wo idi.

Orisun:

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

Isoro #1: aidogba ko bọwọ fun NULL

Jẹ ká fojuinu wipe ohun gbogbo OLD-awọn aaye ní itumo NULL. Kini yoo ṣẹlẹ?

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

Ati lati awọn ojuami ti wo ti sise jade awọn ipo NULL deede FALSE, bi darukọ loke.

Ipinnu: lo onišẹ IS DISTINCT FROM lati ROW-Oṣiṣẹ, ṣe afiwe gbogbo awọn igbasilẹ ni ẹẹkan:

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

Isoro #2: awọn imuse oriṣiriṣi ti iṣẹ-ṣiṣe kanna

Jẹ ki a ṣe afiwe:

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

Kini idi ti afikun idoko-owo wa nibi? SELECT? Iṣẹ kan to_regclass? Kini idi ti o yatọ? ..

Jẹ ki a ṣe atunṣe:

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

Isoro #3: ayo awọn iṣẹ bool

Jẹ ki a ṣe agbekalẹ orisun naa:

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

Oops... Ni otitọ, o wa ni pe ti eyikeyi ninu awọn ipo akọkọ meji ba jẹ otitọ, gbogbo ipo naa yipada si TRUE, lai mu sinu iroyin awọn aidọgba. Ati pe eyi kii ṣe ohun ti a fẹ rara.

Jẹ ki a ṣe atunṣe:

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

Isoro #4 (kekere): eka OR majemu fun aaye kan

Ni otitọ, a ni awọn iṣoro ni No.. 3 gangan nitori awọn ipo mẹta wa. Ṣugbọn dipo wọn o le gba nipasẹ ọkan, lilo ẹrọ naa coalesce ... IN:

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

Nitorina awa NULL "a yoo mu", ati ki o soro OR Ko si ye lati faramọ pẹlu awọn akọmọ.

Lapapọ

Jẹ ki a ṣe igbasilẹ ohun ti a ni:

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

Ati pe ti o ba ro pe iṣẹ okunfa yii le ṣee lo ninu UPDATE-nfa nitori wiwa OLD/NEW ni ipo ipele oke, lẹhinna ipo yii le ni gbogbo igba gbe sinu WHEN-condition, bi han ni #1...

orisun: www.habr.com