- un mecanisme molt potent i convenient si es realitzen les mateixes accions "en profunditat" sobre dades relacionades. Però la recursivitat incontrolada és un mal que pot conduir a qualsevol dels dos execució sense fi procés, o (cosa que passa més sovint) a "menjar" tota la memòria disponible.
En aquest sentit, els DBMS treballen amb els mateixos principis: "Em van dir que cagués, així que vaig cavar". La vostra sol·licitud no només pot alentir els processos veïns, ocupant constantment recursos del processador, sinó que també "deixa caure" tota la base de dades, "menjant" tota la memòria disponible. protecció contra la recursivitat infinita - la responsabilitat del propi desenvolupador.
A PostgreSQL, la capacitat d'utilitzar consultes recursives mitjançant va aparèixer en temps immemorials de la versió 8.4, però encara us podeu trobar regularment amb consultes "indefenses" potencialment vulnerables. Com desfer-se de problemes d'aquest tipus?
No escriviu consultes recursives
I escriu-ne de no recursives. Atentament, el vostre K.O.
De fet, PostgreSQL ofereix una gran quantitat de funcionalitats que podeu utilitzar no aplicar recursivitat.
Utilitzeu un enfocament fonamentalment diferent del problema
De vegades, només podeu mirar el problema des d'un "costat diferent". Vaig donar un exemple d'aquesta situació a l'article — multiplicació d'un conjunt de nombres sense utilitzar funcions d'agregació personalitzades:
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;Aquesta sol·licitud es pot substituir per una opció d'experts en matemàtiques:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Utilitzeu generate_series en lloc de bucles
Suposem que estem davant la tasca de generar tots els prefixos possibles per a una cadena '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;
Esteu segur que necessiteu recursivitat aquí?... Si feu servir LATERAL и generate_series, llavors ni tan sols necessitareu CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Canviar l'estructura de la base de dades
Per exemple, teniu una taula de missatges del fòrum amb connexions de qui ha respost a qui o un fil :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Bé, una sol·licitud típica per descarregar tots els missatges d'un tema sembla una cosa així:
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;Però com que sempre necessitem tot el tema des del missatge arrel, per què no? afegiu el seu identificador a cada entrada automàtic?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Ara tota la nostra consulta recursiva es pot reduir només a això:
SELECT
*
FROM
message
WHERE
theme_id = $1;Utilitzeu "limitadors" aplicats
Si per algun motiu no podem canviar l'estructura de la base de dades, vegem en què podem confiar perquè fins i tot la presència d'un error a les dades no condueixi a una recursivitat infinita.
Comptador de profunditat de recursivitat
Simplement augmentem el comptador en un a cada pas de recursivitat fins a arribar a un límit que considerem òbviament inadequat:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Quan intentem fer un bucle, encara no farem més que el límit especificat d'iteracions "en profunditat".
contres: No hi ha cap garantia que no tornarem a processar el mateix registre, per exemple, a una profunditat de 15 i 25, i després cada +10. I ningú va prometre res sobre "amplada".
Formalment, aquesta recursivitat no serà infinita, però si a cada pas el nombre de registres augmenta exponencialment, tots sabem bé com acaba...
Guardià del "camí"
Afegim alternativament tots els identificadors d'objectes que hem trobat al llarg del camí de recursivitat a una matriu, que és un "camí" únic:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Si hi ha un cicle a les dades, absolutament no processarem el mateix registre repetidament dins del mateix camí.
contres: Però al mateix temps, literalment podem saltar tots els registres sense repetir-nos.
Límit de longitud del camí
Per evitar la situació de recursivitat "deambulant" a una profunditat incomprensible, podem combinar els dos mètodes anteriors. O, si no volem admetre camps innecessaris, complementeu la condició per continuar la recursivitat amb una estimació de la longitud del camí:
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
)
Tria un mètode al teu gust!
Font: www.habr.com