- un meccanismo molto potente e conveniente se le stesse azioni vengono eseguite "in profondità" sui dati correlati. Ma la ricorsione incontrollata è un male che può portare a entrambi esecuzione senza fine processo, o (cosa che accade più spesso) a consumando tutta la memoria disponibile.
I DBMS a questo proposito lavorano sugli stessi principi - "hanno detto di scavare, io scavo". La tua richiesta non solo può rallentare i processi vicini, occupando costantemente le risorse del processore, ma anche "rilasciare" l'intero database nel suo insieme, "consumando" tutta la memoria disponibile. Pertanto protezione da ricorsione infinita è responsabilità dello sviluppatore.
In PostgreSQL, la possibilità di utilizzare query ricorsive tramite è apparso nei tempi antichi della versione 8.4, ma puoi ancora incontrare regolarmente query "indifese" potenzialmente vulnerabili. Come salvarsi da problemi di questo tipo?
Non scrivere query ricorsive
E scrivi non ricorsivo. Sinceramente, il tuo K.O.
In effetti, PostgreSQL fornisce molte funzionalità che puoi utilizzare no applicare la ricorsione.
Utilizzare un approccio fondamentalmente diverso al problema
A volte puoi semplicemente guardare il problema "dall'altra parte". Ho fornito un esempio di tale situazione nell'articolo - moltiplicazione di 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 -- отбор финального результата
i DESC
LIMIT 1;Tale richiesta può essere sostituita con una variante da esperti in 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;Usa generate_series invece di loop
Supponiamo di trovarci di fronte al compito di generare tutti i possibili prefissi per una 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;
Hai davvero bisogno di ricorsione qui?.. Se usi LATERAL и generate_series, quindi non sono necessarie nemmeno le 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 database
Ad esempio, hai una tabella di post del forum con collegamenti a chi ha risposto a chi o un thread in :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Bene, una tipica richiesta per scaricare tutti i messaggi su un argomento è simile a questa:
WITH RECURSIVE T AS (
SELECT
*
FROM
message
WHERE
message_id = $1
UNION ALL
SELECT
m.*
FROM
T
JOIN
message m
ON m.reply_to = T.message_id
)
TABLE T;Ma dal momento che abbiamo sempre bisogno dell'intero soggetto dal messaggio radice, perché non lo facciamo aggiungi il suo id a ogni voce automatico?
-- добавим поле с общим идентификатором темы и индекс на него
ALTER TABLE message
ADD COLUMN theme_id uuid;
CREATE INDEX ON message(theme_id);
-- инициализируем идентификатор темы в триггере при вставке
CREATE OR REPLACE FUNCTION ins() RETURNS TRIGGER AS $$
BEGIN
NEW.theme_id = CASE
WHEN NEW.reply_to IS NULL THEN NEW.message_id -- берем из стартового события
ELSE ( -- или из сообщения, на которое отвечаем
SELECT
theme_id
FROM
message
WHERE
message_id = NEW.reply_to
)
END;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER ins BEFORE INSERT
ON message
FOR EACH ROW
EXECUTE PROCEDURE ins();
Ora la nostra intera query ricorsiva può essere ridotta solo a questo:
SELECT
*
FROM
message
WHERE
theme_id = $1;Usa "limitatori" applicati
Se per qualche motivo non siamo in grado di modificare la struttura del database, vediamo su cosa possiamo fare affidamento in modo che anche la presenza di un errore nei dati non porti a una ricorsione infinita.
Contatore di "profondità" di ricorsione
Incrementiamo semplicemente il contatore di uno ad ogni passo della ricorsione fino a raggiungere il limite, che consideriamo ovviamente inadeguato:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Quando proviamo a eseguire il ciclo, non faremo comunque più del limite specificato di iterazioni "in profondità".
Contro: Non vi è alcuna garanzia che non rielaboreremo lo stesso record, ad esempio a una profondità di 15 e 25, e poi ogni +10. E nessuno ha promesso nulla sulla "larghezza".
Formalmente tale ricorsione non sarà infinita, ma se ad ogni passo il numero di record aumenta esponenzialmente, sappiamo tutti bene come va a finire...
Guardiano della Via
A nostra volta, aggiungiamo tutti gli identificatori di oggetto che abbiamo incontrato lungo il percorso di ricorsione a un array, che è un "percorso" univoco per esso:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Se c'è un ciclo nei dati, non elaboreremo assolutamente lo stesso record ripetutamente all'interno dello stesso percorso.
Contro: Ma allo stesso tempo possiamo bypassare, letteralmente, tutti i record senza ripeterci.
Limite di lunghezza del percorso
Per evitare la situazione di ricorsione "vagante" a una profondità incomprensibile, possiamo combinare i due metodi precedenti. Oppure, se non vogliamo supportare campi extra, integra la condizione di continuazione della ricorsione con una stima 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 modo che preferisci!
Fonte: habr.com