🥇PostgreSQL Antipatterns: вылічэнне умоў у SQL

SQL - гэта не C++, і не JavaScript. Таму вылічэнне лагічных выразаў адбываецца інакш, і вось гэта - зусім не адно і тое ж:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

У працэсе аптымізацыі плана выканання запыту PostgreSQL можа адвольным чынам "перастаўляць" эквівалентныя ўмовы, не вылічваць нейкія з іх для асобных запісаў, адносіць да ўмовы ўжывальнага азначніка… Карацей, прасцей за ўсё лічыць, што вы загадзя не можаце кіраваць тым, у якім парадку будуць (і ці будуць увогуле) вылічацца раўнапраўныя умовы.

Таму калі кіраваць прыярытэтам усё ж такі хочацца, трэба структурна. зрабіць гэтыя ўмовы няроўнымі з дапамогай умоўных выразаў и аператараў.

Дадзеныя і праца з імі - аснова нашага комплексу НВІСТаму нам вельмі важна, каб аперацыі над імі выконваліся не толькі карэктна, але і эфектыўна. Давайце паглядзім на канкрэтных прыкладах, дзе могуць быць дапушчаны памылкі вылічэння выразаў, а дзе варта палепшыць іх эфектыўнасць.

#0: RTFM

Стартавы прыклад з дакументацыі:

Калі парадак вылічэння важны, яго можна зафіксаваць з дапамогай канструкцыі CASE. Напрыклад, такі спосаб пазбегнуць дзялення на нуль у сказе WHERE ненадзейны:
SELECT ... WHERE x > 0 AND y/x > 1.5;
Бяспечны варыянт:
SELECT ... WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;
Ужывальная так канструкцыя CASE абараняе выраз ад аптымізацыі, таму выкарыстоўваць яе трэба толькі пры неабходнасці.

#1: умова ў трыгеры

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

Накшталт усё выглядае добра, але… Ніхто не абяцае, што ўкладзены SELECT не будзе выконвацца пры ілжывасці першай умовы. Паправім з дапамогай укладзеных IF:

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

Зараз паглядзім уважліва - усё цела трыгернай функцыі аказалася «загорнута» ў IF. А гэта значыць, што нам нішто не перашкаджае вынесці гэтую ўмову з працэдуры з дапамогай WHEN-умовы:

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

Такі падыход дазваляе гарантавана зэканоміць рэсурсы сервера пры памылковасці ўмовы.

#2: OR/AND-ланцужок

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

У адваротным выпадку можна атрымаць, што абодва EXISTS будуць "праўдзівымі", але абодва і выканаюцца.

Але калі мы сапраўды ведаем, што адзін з іх бывае "праўдзівым" шмат часцей (або "ілжывым" - для AND-ланцужкі) - ці нельга неяк «падвысіць яго прыярытэт», каб другі не выконваўся лішні раз?

Аказваецца, можна - алгарытмічна падыход блізкі да тэмы артыкула PostgreSQL Antipatterns: рэдкі запіс даляціць да сярэдзіны JOIN.

Давайце проста «засунем пад CASE» абедзве гэтыя ўмовы:

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

У дадзеным выпадку мы не вызначалі ELSE-значэнне, гэта значыць у выпадку ілжывасці абедзвюх умоў CASE верне NULL, што трактуецца як FALSE в WHERE-ўмове.

Дадзены прыклад можна скамбінаваць і інакш - на густ і колер:

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

#3: як [не] трэба пісаць умовы

На разбор прычын "дзіўнай" спрацоўкі гэтага трыгера мы патрацілі два дні - давайце паглядзім, чаму.

Зыходнік:

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

Праблема №1: няроўнасць не ўлічвае NULL

Уявім, што ўсё OLD-палі мелі значэнне NULL. Што атрымаецца?

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

А з пункту гледжання адпрацоўкі ўмовы NULL эквівалентны FALSE, як было згадана вышэй.

рашэнне: выкарыстоўвайце аператар IS DISTINCT FROM ад ROW-аператара, параўноўваючы адразу цэлыя запісы:

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

Праблема №2: розная рэалізацыя аднолькавага функцыяналу

параўнаем:

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

Навошта тут лішнія ўкладзеныя SELECT? А функцыя to_regclass? А па-рознаму чаму?..

Выправім:

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

Праблема №3: прыярытэт bool-аперацый

Адфарматуем зыходнік:

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

Упс… Па факце, атрымалася, што ў выпадку праўдзівасці любой з двух першых умоў, уся ўмова цалкам звяртаецца ў TRUE, без уліку няроўнасцей. А гэта зусім не тое, чаго мы хацелі.

Выправім:

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

Праблема №4 (маленькая): складанае OR-ўмова для аднаго поля

Уласна, праблемы ў №3 у нас узніклі роўна таму, што ўмоў было тры. Але замест іх можна абысціся адным, з дапамогай механізму. coalesce ... IN:

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

Так мы і NULL «зловім», і складаных OR са дужкамі гарадзіць не давядзецца.

Разам

Зафіксуем тое, што ў нас атрымалася:

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

А калі ўлічыць, што гэтая трыгерная функцыя можа прымяняцца толькі ў UPDATE-трыгеры з-за наяўнасці OLD/NEW ва ўмове верхняга ўзроўню, то гэта ўмова можна наогул вынесці ў WHEN-умова, як было паказана ў #1…

Крыніца: habr.com

PostgreSQL Antipatterns: вылічэнне умоў у SQL