— è un meccanismo molto potente e conveniente, se si effettuano le stesse operazioni "in profondità" sui dati correlati. Ma la ricorsione incontrollata è un male che può portare o a un'esecuzione infinita del processo, o (cosa che accade più frequentemente) a una 'consunzione' dell'intera memoria disponibile.
I database funzionano secondo gli stessi principi — "hai detto di scavare, io scavo". La tua query non solo può rallentare i processi adiacenti, occupando costantemente risorse della CPU, ma può anche 'far crollare' l'intero database, 'mangiando' tutta la memoria disponibile. Pertanto la protezione da ricorsione infinita è responsabilità dello sviluppatore.
In PostgreSQL, la possibilità di utilizzare query ricorsive è stata introdotta sin dai tempi remoti della versione 8.4, ma ancora oggi è possibile incontrare query "indifese" potenzialmente vulnerabili. Come liberarsi da problemi di questo tipo? Non scrivere query ricorsive
Ma scrivere query non ricorsive. Con rispetto, il tuo K.O.
In realtà, PostgreSQL offre un numero sufficientemente ampio di funzionalità che possono essere utilizzate per
applicare la ricorsione. non Adottare un approccio radicalmente diverso al problema
A volte è sufficiente guardare al problema "da un altro angolo". Ho fornito un esempio di tale situazione nell'articolo
"SQL HowTo: 1000 e un modo di aggregazione" 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 da 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 cicliSupponiamo che ci sia la necessità di 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 si utilizza generate_series LATERALE e , non sarà nemmeno necessario il CTE:SELECT substr(str, 1, ln) str FROM (VALUES('abcdefgh')) T(str) , LATERAL( SELECT generate_series(length(str), 1, -1) ln ) X;
Modificare la struttura del databaseAd esempio, hai una tabella di messaggi nel forum con relazioni su chi ha risposto a chi o thread nel
Например, у вас есть таблица сообщений форума со связями кто-кому ответил или тред в :
CREA TABELLA message(
message_id
uuid
CHIAVE PRIMARIA
, reply_to
uuid
RIFERIMENTI message
, body
testo
);
CREA INDICE SU message(reply_to); 
Ecco un esempio tipico di query per caricare tutti i messaggi su un certo argomento:
CON CTE Ricorsiva T AS (
SELEZIONA
*
DA
message
DOVE
message_id = $1
UNIONE TUTTO
SELEZIONA
m.*
DA
T
UNISCI
message m
SU m.reply_to = T.message_id
)
TABELLA T;Ma visto che abbiamo sempre bisogno di tutto l'argomento dal messaggio radice, perché non aggiungere il suo identificatore a ogni record automaticamente?
-- aggiungiamo un campo con l'identificatore generale dell'argomento e un indice su di esso
ALTERA TABELLA message
AGGIUNGI COLONNA theme_id uuid;
CREA INDICE SU message(theme_id);
-- inizializziamo l'identificatore dell'argomento nel trigger al momento dell'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 iniziale
ALTRO ( -- o dal messaggio a cui rispondiamo
SELEZIONA
theme_id
DA
message
DOVE
message_id = NEW.reply_to
)
FINE;
RESTITUISCI NEW;
FINE;
$$ LINGUAGGIO plpgsql;
CREA TRIGGER ins PRIMA DELL'INSERIMENTO
SU message
PER OGNI RIGA
ESEGUI LA PROCEDURA ins(); 
Ora tutta la nostra query ricorsiva può essere ridotta a questa:
SELEZIONA
*
DA
message
DOVE
theme_id = $1;Utilizzare «limitatore» applicativo
Se non possiamo cambiare la struttura del database per vari motivi, vediamo su cosa possiamo contare affinché anche la presenza di un errore nei dati non porti a un'esecuzione infinita della ricorsione.
Contatore «profondità» della ricorsione
Aumentiamo semplicemente il contatore di uno a ogni passaggio della ricorsione fino a raggiungere un limite che consideriamo chiaramente inadeguato:
CON CTE Ricorsiva T AS (
SELEZIONA
0 i
...
UNIONE TUTTO
SELEZIONA
i + 1
...
DOVE
T.i < 64 -- limite
) Pro: In caso di tentativo di ricorsione infinita, comunque non eseguiremo oltre il limite specificato di iterazioni "in profondità".
Contro: Non c'è garanzia che non trattiamo di nuovo lo stesso record - ad esempio, a profondità 15 e 25, e così avanti ogni +10. Inoltre, nessuno ha promesso nulla per quanto riguarda "in ampiezza".
Formalmente, tale ricorsione non sarà infinita, ma se a ogni passaggio il numero di record aumenta esponenzialmente, sappiamo tutti come finisce...
Custode del «percorso»
Scriviamo uno per uno tutti gli identificatori degli oggetti incontrati lungo il percorso di ricorsione in un array, che rappresenta il «percorso» unico fino a esso:
CON CTE Ricorsiva T AS (
SELEZIONA
ARRAY[id] path
...
UNIONE TUTTO
SELEZIONA
path || id
...
DOVE
id ALL(T.path) -- non coincide con nessuno di
) Pro: Se vi è un ciclo nei dati, non elaboreremo mai due volte la stessa registrazione lungo lo stesso percorso.
Contro: Tuttavia, possiamo attraversare letteralmente tutte le registrazioni senza mai ripeterci.
Limitazione della lunghezza del percorso
Per evitare la situazione di "erranza" della ricorsione a profondità sconosciuta, possiamo combinare i due metodi precedenti. Oppure, se non vogliamo gestire campi aggiuntivi, 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
