ProHoster > Блог > Administracija > PostgreSQL antiuzorci: Procjena stanja u SQL-u

PostgreSQL antiuzorci: Procjena stanja u SQL-u

SQL nije C++, niti JavaScript. Dakle, evaluacija logičkih izraza je drugačija, a to uopće nije ista stvar:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Dok optimizirate plan izvršenja PostgreSQL upita može proizvoljno "preurediti" ekvivalentne uslove, ne računajte nijedan od njih za pojedinačne zapise, pozivajte se na stanje primijenjenog indeksa... Ukratko, najlakše je pretpostaviti da ste ne mogu upravljati redosledom kojim će biti (i da li će se uopšte računati) jednaka uslova.

Stoga, ako i dalje želite upravljati prioritetom, trebate strukturalno čine ove uslove nejednakim sa uslovnim izrazi и operateri.

PostgreSQL antiuzorci: Procjena stanja u SQL-u
Podaci i rad sa njima su osnova našeg VLSI kompleksa, pa nam je veoma važno da se operacije na njima izvode ne samo korektno, već i efikasno. Pogledajmo konkretne primjere gdje se mogu napraviti greške u evaluaciji izraza i gdje vrijedi poboljšati njihovu efikasnost.

#0: RTFM

Počinjati primjer iz dokumentacije:

Kada je redoslijed evaluacije važan, može se fiksirati konstruktom CASE. Na primjer, na ovaj način izbjegavate dijeljenje nulom u rečenici WHERE nepouzdan:

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

Sigurna opcija:

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

Korišćena konstrukcija CASE štiti izraz od optimizacije, tako da ga treba koristiti samo kada je potrebno.

#1: stanje okidača

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

Čini se da sve izgleda dobro, ali... Niko ne obećava da je uloženo SELECT neće se izvršiti ako je prvi uslov netačan. Popravi to sa ugniježđen IF:

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

Sada pogledajmo pažljivo - pokazalo se da je cijelo tijelo funkcije okidača "umotano". IF. A to znači da nas ništa ne sprečava da ovo stanje uklonimo iz procedure korišćenja WHEN-uslovi:

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

Ovaj pristup vam omogućava da sačuvate resurse servera uz garanciju ako je uslov netačan.

#2: ILI/I lanac

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

U suprotnom, može se dobiti da oboje EXISTS biće istina, ali oba će biti izvršena.

Ali ako sa sigurnošću znamo da je jedan od njih "tačan" mnogo češće (ili "netačan" - za AND-lanci) - da li je moguće nekako "povećati njegov prioritet" da se drugi ne izvrši još jednom?

Ispostavilo se da je moguće - algoritamski pristup je blizak temi članka PostgreSQL antiobrasci: Rijetki unos dostiže sredinu JOIN-a.

Hajde da samo "gurnemo pod CASE" oba ova uslova:

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

U ovom slučaju nismo definirali ELSE-vrijednost, odnosno ako su oba uslova netačna CASE Će se vratiti NULL, što se tumači kao FALSE в WHERE- uslovi.

Ovaj primjer se može kombinirati na drugi način - po ukusu i boji:

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

#3: kako [ne] napisati uslove

Proveli smo dva dana analizirajući razloge “čudnog” pokretanja ovog okidača – da vidimo zašto.

Izvor:

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

Problem #1: Nejednakost ne uzima u obzir NULL

Pretpostavimo da je sve OLD-polja su bila važna NULL. Šta će se desiti?

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

I sa stanovišta razrade uslova NULL ekvivalentan FALSE, kao što je gore navedeno.

odluka: koristi operator IS DISTINCT FROM iz ROW-operater, koji upoređuje čitave zapise odjednom:

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

Problem broj 2: različita implementacija iste funkcionalnosti

Uporedi:

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

Zašto postoje dodatna ulaganja SELECT? Funkcija to_regclass? Zašto je drugačije...

da popravimo:

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

Problem #3: Bool prioritet

Formatirajmo izvor:

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

Ups... U stvari, pokazalo se da se u slučaju istinitosti bilo kojeg od prva dva uslova cijeli uvjet pretvara u TRUE, bez obzira na nejednakosti. A ovo uopšte nije ono što smo želeli.

da popravimo:

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

Problem #4 (mali): složeni uslov ILI za jedno polje

Zapravo, imali smo problema u broju 3 upravo zato što su bila tri uslova. Ali umjesto njih, možete se snaći s jednim, koristeći mehanizam coalesce ... IN:

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

I mi smo NULL "ulov", i složen OR Ne morate se mučiti sa zagradama.

Ukupno

Hajde da popravimo šta imamo:

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

A s obzirom da se ova funkcija okidača može koristiti samo u UPDATEokidač zbog prisustva OLD/NEW u stanju višeg nivoa, onda se ovo stanje generalno može izbaciti WHEN- stanje kao što je prikazano u #1...

izvor: www.habr.com

Dodajte komentar