Antipattern di PostgreSQL: calcolo delle condizioni in SQL

SQL non è C++ e non è JavaScript. Pertanto, il calcolo delle espressioni logiche avviene in modo diverso, e questo non è affatto la stessa cosa:

WHERE fncondX() AND fncondY()

= fncondX() && fncondY()

Durante l'ottimizzazione del piano di esecuzione della query, PostgreSQL può in modo arbitrario "riordinare" le condizioni equivalenti, non calcolare alcune di esse per singoli record, attribuirle alla condizione dell'indice utilizzato... Insomma, è più semplice considerare che non potete controllare l'ordine in cui saranno (e se saranno) calcolate le condizioni equivalenti. Pertanto, se volete gestire le priorità, dovete strutturalmente rendere queste condizioni disuguali

attraverso espressioni condizionali operatori I dati e il loro utilizzo sono la base e del nostro complesso SBIS.

, quindi è molto importante per noi che le operazioni su di essi vengano eseguite non solo correttamente, ma anche in modo efficiente. Diamo un'occhiata a esempi concreti, dove possono verificarsi errori nel calcolo delle espressioni e dove si può migliorare la loro efficienza. Inizialeesempio dalla documentazione

#0: RTFM

Quando l'ordine di calcolo è importante, può essere fissato tramite la costruzione CASE:

. Ad esempio, questo metodo per evitare la divisione per zero nella query non è affidabile:SELECT ... WHERE x > 0 AND y/x > 1.5; DOVE Opzione sicura:

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

La costruzione utilizzata in questo modo

proteggerebbe l'espressione dall'ottimizzazione, quindi va utilizzata solo quando necessario.

BEGIN IF cond(NEW.fld) AND EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; non è affidabile: Tutto sembra a posto, ma... Nessuno garantisce che il nidificato

#1: условие в триггере

non venga eseguito se la prima condizione è falsa. Correggiamo con

nidificati SELECT IF BEGIN IF cond(NEW.fld) THEN IF EXISTS(SELECT ...) THEN ... END IF; END IF; RETURN NEW; END; Ora vediamo attentamente: tutto il corpo della funzione trigger è risultato "avvolto" in:

. Ciò significa che nulla vieta di estrarre questa condizione dalla procedura tramite

WHEN Ora vediamo attentamente: tutto il corpo della funzione trigger è risultato "avvolto" in-condizione BEGIN IF EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; ... CREATE TRIGGER ... WHEN cond(NEW.fld);Questo approccio consente di risparmiare risorse del server in caso di falsità della condizione.:

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

Nel caso scomodo, è possibile che entrambi

#2: OR/AND-цепочка

siano "veritieri", ma

entrambi verranno eseguiti ESISTE Ma se sappiamo per certo che uno di essi è "veritiero" molto più spesso (o "falso" — per entrambi si eseguiranno.

Ma se sappiamo esattamente che uno di essi è «vero» molto più spesso (o «falso» — per E-catene) — non si può in qualche modo «aumentare la sua priorità», affinché il secondo non venga eseguito inutilmente?

A quanto pare, si può — l'approccio algoritmico è vicino al tema dell'articolo PostgreSQL Antipatterns: una scrittura rara arriverà fino a metà JOIN.

Mettiamo semplicemente «sotto CASE» entrambe queste condizioni:

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

In questo caso non abbiamo definito ELSE-valore, cioè nel caso in cui entrambe le condizioni siano false non è affidabile: restituirà NULL, che si interpreta come FALSE in DOVE-condizione.

Questo esempio può essere combinato anche in modo diverso — a piacere:

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

#3: как [не] надо писать условия

Abbiamo impiegato due giorni per analizzare le ragioni di questo comportamento «strano» del trigger — vediamo perché.

Codice sorgente:

IF( NEW."Documento_" is null or NEW."Documento_" = (select '"Completo"'::regclass::oid) or NEW."Documento_" = (select to_regclass('"DocumentoPerSalario"')::oid)
     AND (   OLD."DocumentoNostraOrganizzazione" <> NEW."DocumentoNostraOrganizzazione"
          OR OLD."Cancellato" <> NEW."Cancellato"
          OR OLD."Data" <> NEW."Data"
          OR OLD."Orario" <> NEW."Orario"
          OR OLD."Creatore" <> NEW."Creatore" ) ) THEN ...

Problema n. 1: l'ineguaglianza non tiene conto di NULL

Immaginiamo che tutti OLD-i campi avessero valore NULL. Cosa succederà?

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

E dal punto di vista dell'elaborazione della condizione NULL è equivalente FALSE, come accennato sopra.

Soluzione: utilizza l'operatore IS DISTINCT FROM di ROW-operatore, confrontando direttamente le intere registrazioni:

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

Problema n. 2: diversa implementazione della stessa funzionalità

Confrontiamo:

NEW."Documento_" = (select '"Completo"'::regclass::oid)
NEW."Documento_" = (select to_regclass('"DocumentoPerSalario"')::oid)

Perché qui ci sono nidificati non necessari SELECT? А функция to_regclass? А по-разному-то почему?..

Correggiamo:

NEW."Documento_" = '"Completo"'::regclass::oid
NEW."Documento_" = '"DocumentoPerSalario"'::regclass::oid

Problema n. 3: priorità delle operazioni bool

Formattiamo il codice sorgente:

{... IS NULL} OR
{... Completo} OR
{... DocumentoPerSalario} AND
( {... ineugualtà} )

Oops… Infatti, è successo che nel caso di verità di una qualsiasi delle prime due condizioni, l'intera condizione si trasforma in TRUE, senza considerare le ineguaglianze. E questo non è affatto ciò che volevamo.

Correggiamo:

(
  {... IS NULL} OR
  {... Completo} OR
  {... DocumentoPerSalario}
) AND
( {... ineugualtà} )

Problema n. 4 (piccolo): condizione OR complessa per un campo

Insomma, i problemi nel n. 3 sono emersi proprio perché c'erano tre condizioni. Ma invece di esse, si può fare con una sola, utilizzando il meccanismo coalesce ... IN:

coalesce(NEW."Documento_"::text, '') IN ('', '"Complesso"', '"DocumentoPerStipendio"')

Così possiamo NULL «catturare», e situazioni complesse OR non sarà necessario complicare con le parentesi.

Totale

Fissiamo ciò che abbiamo ottenuto:

IF (
  coalesce(NEW."Documento_"::text, '') IN ('', '"Complesso"', '"DocumentoPerStipendio"') AND
  (
    OLD."DocumentoNostraOrganizzazione"
  , OLD."Cancellato"
  , OLD."Data"
  , OLD."Tempo"
  , OLD."CreatoDa"
  ) IS DISTINCT FROM (
    NEW."DocumentoNostraOrganizzazione"
  , NEW."Cancellato"
  , NEW."Data"
  , NEW."Tempo"
  , NEW."CreatoDa"
  )
) THEN ...

E se consideriamo che questa funzione trigger può essere applicata solo in UPDATE-trigger a causa della presenza di OLD/NEW nella condizione di livello superiore, allora questa condizione può essere completamente spostata in un BEGIN IF EXISTS(SELECT ...) THEN ... END IF; RETURN NEW; END; ... CREATE TRIGGER ... WHEN cond(NEW.fld);-condizione, come mostrato in #1…

Fonte: habr.com