— un meccanismo molto potente e comodo, se si eseguono le stesse azioni "in profondità" su dati correlati. Ma la ricorsione incontrollata è il male, che può portare a un'esecuzione infinita del processo, o (cosa che succede più spesso) a una «consunzione» di tutta la memoria disponibile.
I DBMS funzionano su basi simili — "hanno detto di scavare, e io scavo". La tua query può non solo rallentare i processi vicini, occupando continuamente le risorse della CPU, ma anche «far crollare» l'intero database, «mangiando» tutta la memoria disponibile. Pertanto, la protezione contro la ricorsione infinita è un obbligo dello sviluppatore stesso.
In PostgreSQL, la possibilità di utilizzare query ricorsive tramite è disponibile da tempi immemori con la versione 8.4, ma ancora oggi è possibile incontrare regolarmente query potenzialmente vulnerabili e "indifese". Come liberarsi da problemi di questo tipo?
Non scrivere query ricorsive
Ma scrivere query non ricorsive. Cordialmente, Il tuo C.O.
In realtà, PostgreSQL offre un ampio repertorio di funzionalità da sfruttare per non applicare la ricorsione.
Adottare un approccio totalmente differente al problema
A volte è possibile osservare il problema "da un altro punto di vista". Un esempio di tale situazione è stato presentato nell'articolo — moltiplicare un insieme di numeri senza utilizzare funzioni di aggregazione personalizzate:
WITH RECURSIVE src AS (
SELECT '{2,3,5,7,11,13,17,19}'::integer[] arr
)
, T(i, val) AS (
SELECT
1::bigint
, 1
UNION ALL
SELECT
i + 1
, val * arr[i]
FROM
T
, src
WHERE
i <= array_length(arr, 1)
)
SELECT
val
FROM
T
ORDER BY -- selezione del risultato finale
i DESC
LIMIT 1;Questa query può essere sostituita con una variante degli esperti di matematica:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Utilizzare generate_series invece dei cicli
Supponiamo di voler generare tutti i possibili prefissi per la stringa 'abcdefgh':
WITH RECURSIVE T AS (
SELECT 'abcdefgh' str
UNION ALL
SELECT
substr(str, 1, length(str) - 1)
FROM
T
WHERE
length(str) > 1
)
TABLE T; È davvero necessaria la ricorsione qui?.. Se utilizziamo LATERAL e generate_series, nemmeno il CTE sarà necessario:
SELEZIONA
substr(str, 1, ln) str
DA
(VALORI('abcdefgh')) T(str)
, LATERALE(
SELEZIONA generate_series(length(str), 1, -1) ln
) X;Modificare la struttura del database
Ad esempio, hai una tabella di messaggi del forum con collegamenti su chi ha risposto a chi o un thread in :
CREA TABELLA message(
message_id
uuid
CHIAVE PRIMARIA
, reply_to
uuid
RIFERIMENTI message
, body
text
);
CREA INDICE SU message(reply_to); 
E il tipo di query per caricare tutti i messaggi su un argomento appare più o meno così:
CON RICORSO T COME (
SELEZIONA
*
DA
message
DOVE
message_id = $1
UNIONE TUTTI
SELEZIONA
m.*
DA
T
UNISCI
message m
SU m.reply_to = T.message_id
)
TABella T;Ma dato che abbiamo sempre bisogno di tutto il tema dal messaggio radice, perché non aggiungere il suo identificatore in ogni registrazione automaticamente?
-- aggiungiamo un campo con l'identificatore comune del tema e un indice su di esso
ALTERA TABELLA message
AGGIUNGI COLONNA theme_id uuid;
CREA INDICE SU message(theme_id);
-- inizializziamo l'identificatore del tema nel trigger all'inserimento
CREA O SOSTITUISCI FUNZIONE ins() RESTITUISCE TRIGGER AS $$
INIZIO
NEW.theme_id = CASO
QUANDO NEW.reply_to È NULL ALLORA NEW.message_id -- prendiamo dall'evento di partenza
ALTRO ( -- o dal messaggio a cui stiamo rispondendo
SELEZIONA
theme_id
DA
message
DOVE
message_id = NEW.reply_to
)
FINE;
RESTITUISCI NEW;
FINE;
$$ LINGUAGGIO plpgsql;
CREA TRIGGER ins PRIMA DI INSERIRE
SU message
PER OGNI RIGA
ESEGUI PROCEDURA ins(); 
Ora, la nostra richiesta ricorsiva può essere ridotta a qualcosa di simile a questo:
SELECT
*
FROM
message
WHERE
theme_id = $1;Utilizzare 'delimiter' applicativi
Se non possiamo cambiare la struttura del database per qualche motivo, vediamo su cosa possiamo fare affidamento affinché anche la presenza di errori nei dati non porti a un'esecuzione ricorsiva infinita.
Contatore di 'profondità' della ricorsione
Aumentiamo semplicemente il contatore di uno ad ogni passo della ricorsione fino a raggiungere il limite che consideriamo evidentemente inadeguato:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- limite
) Pro: In caso di tentativo di ciclo, comunque non eseguiremo più del limite di iterazioni "in profondità" specificato.
Contro: Non c'è garanzia che non elaboreremo nuovamente la stessa registrazione — ad esempio, a profondità 15 e 25, e poi ogni +10. E nessuno ha promesso nulla riguardo "in larghezza".
Formalmente, questa ricorsione non sarà infinita, ma se ad ogni passo il numero di registrazioni aumenta esponenzialmente, sappiamo tutti come finisce...
Custode del 'percorso'
Aggiungiamo uno dopo l'altro tutti gli identificativi degli oggetti che incontriamo lungo il percorso della ricorsione in un array, che rappresenta l'unico "percorso" fino a esso:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id ALL(T.path) -- non coincide con nessuno di
) Pro: In presenza di un ciclo nei dati, sicuramente non elaboreremo di nuovo la stessa registrazione all'interno di un singolo percorso.
Contro: Tuttavia, possiamo comunque scorrere, letteralmente, tutte le registrazioni senza ripeterci.
Limitazione della lunghezza del percorso
Per evitare la situazione di "smarrimento" della ricorsione a una profondità sconosciuta, possiamo combinare i due metodi precedenti. Oppure, se non vogliamo mantenere campi non necessari, possiamo integrare la condizione di continuazione della ricorsione con una valutazione della lunghezza del percorso:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id ALL(T.path) AND
array_length(T.path, 1) < 10
) Scegli il metodo che preferisci!
Fonte: habr.com
