ProHoster > blog > administratie > PostgreSQL Antipatterns: conditie-evaluatie in SQL

PostgreSQL Antipatterns: conditie-evaluatie in SQL

SQL is geen C++ en ook geen JavaScript. Daarom is de evaluatie van logische uitdrukkingen anders, en dit is helemaal niet hetzelfde:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Terwijl het uitvoeringsplan van een PostgreSQL-query wordt geoptimaliseerd kan de equivalente voorwaarden willekeurig "herschikken"., bereken geen van hen voor individuele records, verwijs naar de toestand van de toegepaste index ... Kortom, de gemakkelijkste manier is om aan te nemen dat u kan het niet aan de volgorde waarin ze zullen zijn (en of ze überhaupt zullen worden berekend) Gelijk termen.

Dus als je nog prioriteit wilt managen, moet je dat structureel doen maken deze voorwaarden ongelijk met voorwaardelijk uitdrukkingen и operators.

PostgreSQL Antipatterns: conditie-evaluatie in SQL
Data en het werken daarmee is de basis van ons VLSI-complex, dus het is erg belangrijk voor ons dat bewerkingen erop niet alleen correct, maar ook efficiënt worden uitgevoerd. Laten we eens kijken naar concrete voorbeelden waar fouten in de evaluatie van expressies kunnen worden gemaakt en waar het de moeite waard is om hun efficiëntie te verbeteren.

#0: RTFM

Beginnend voorbeeld uit documentatie:

Wanneer de volgorde van evaluatie belangrijk is, kan deze worden vastgelegd met het construct CASE. Zo voorkom je bijvoorbeeld deling door nul in een zin WHERE onbetrouwbaar:

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

Veilige optie:

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

De gebruikte constructie CASE beschermt de expressie tegen optimalisatie, dus deze mag alleen worden gebruikt wanneer dat nodig is.

# 1: triggerconditie

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

Alles lijkt er goed uit te zien, maar... Niemand belooft dat de investering SELECT wordt niet uitgevoerd als de eerste voorwaarde onwaar is. Repareer het met genest IF:

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

Laten we nu eens goed kijken - het hele lichaam van de triggerfunctie bleek erin te zijn "verpakt". IF. En dit betekent dat niets ons ervan weerhoudt om deze aandoening uit de procedure te verwijderen WHEN-voorwaarden:

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

Met deze aanpak kunt u serverbronnen opslaan met een garantie als de voorwaarde onwaar is.

#2: OR/AND-ketting

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

Anders kan worden verkregen dat beide EXISTS zal waar zijn, maar beide worden uitgevoerd.

Maar als we zeker weten dat een van hen veel vaker "waar" is (of "onwaar" - voor AND-ketens) - is het mogelijk om op de een of andere manier "de prioriteit te verhogen" zodat de tweede niet opnieuw wordt uitgevoerd?

Het blijkt mogelijk te zijn - de algoritmische benadering ligt dicht bij het onderwerp van het artikel PostgreSQL Antipatterns: Zeldzame invoer bereikt het midden van een JOIN.

Laten we beide voorwaarden gewoon "onder CASE schuiven":

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

In dit geval hebben we niet gedefinieerd ELSE-waarde, dat wil zeggen, als beide voorwaarden onwaar zijn CASE zal terugkeren NULL, wat wordt geïnterpreteerd als FALSE в WHERE- voorwaarden.

Dit voorbeeld kan op een andere manier worden gecombineerd - naar smaak en kleur:

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

#3: hoe [niet] voorwaarden te schrijven

We hebben twee dagen besteed aan het analyseren van de redenen voor de "vreemde" activering van deze trigger - laten we eens kijken waarom.

Bron:

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

Probleem #1: Ongelijkheid houdt geen rekening met NULL

Laten we aannemen dat alles OLD-velden waren belangrijk NULL. Wat zal er gebeuren?

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

En vanuit het oogpunt van het uitwerken van de voorwaarden NULL equivalent FALSE, Zoals hierboven vermeld.

beslissing: gebruik operator IS DISTINCT FROM van ROW-operator, hele records in één keer vergelijken:

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

Probleem nummer 2: verschillende implementatie van dezelfde functionaliteit

Laten we het vergelijken:

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

Waarom zijn er extra investeringen SELECT? Een functie to_regclass? Waarom is het anders...

Laten we het volgende oplossen:

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

Probleem # 3: bool-voorrang

Laten we de bron formatteren:

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

Oeps ... In feite bleek dat in het geval van de waarheid van een van de eerste twee voorwaarden, de hele voorwaarde verandert in TRUE, zonder rekening te houden met ongelijkheden. En dit is helemaal niet wat we wilden.

Laten we het volgende oplossen:

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

Opgave #4 (klein): complexe OF-voorwaarde voor één veld

Eigenlijk hadden we problemen in nr. 3 juist omdat er drie voorwaarden waren. Maar in plaats van hen kun je er een gebruiken, met behulp van het mechanisme coalesce ... IN:

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

Zo zijn wij NULL "vangst", en complex OR Je hoeft niet te rommelen met haakjes.

In totaal

Laten we repareren wat we hebben:

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

En gezien het feit dat deze triggerfunctie alleen kan worden gebruikt in UPDATEtrigger vanwege de aanwezigheid OLD/NEW in de toestand op het hoogste niveau, dan kan deze toestand over het algemeen worden afgesloten WHEN-conditie zoals getoond in #1...

Bron: www.habr.com

Voeg een reactie