- um mecanismo muito poderoso e conveniente se as mesmas ações “profundas” forem executadas em dados relacionados. Mas a recursão descontrolada é um mal que pode levar a execução sem fim processo, ou (o que acontece com mais frequência) para "comendo" toda a memória disponível.
Nesse sentido, o SGBD trabalha com os mesmos princípios - "Eles me disseram para cavar, então eu cavo". Sua solicitação pode não apenas desacelerar processos vizinhos, ocupando constantemente recursos do processador, mas também “descartar” todo o banco de dados, “comendo” toda a memória disponível. Portanto proteção contra recursão infinita - responsabilidade do próprio desenvolvedor.
No PostgreSQL, a capacidade de usar consultas recursivas via apareceu nos tempos imemoriais da versão 8.4, mas você ainda pode encontrar regularmente solicitações “indefesas” potencialmente vulneráveis. Como se livrar de problemas desse tipo?
Não escreva consultas recursivas
E escreva outros não recursivos. Atenciosamente, seu K.O.
Na verdade, o PostgreSQL oferece muitas funcionalidades que você pode usar para não aplicar recursão.
Use uma abordagem fundamentalmente diferente para o problema
Às vezes você pode simplesmente olhar para o problema do “lado diferente”. Dei um exemplo de tal situação no artigo — multiplicação de um conjunto de números sem usar funções agregadas personalizadas:
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;Esta solicitação pode ser substituída por uma opção de especialistas em matemática:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Use generate_series em vez de loops
Digamos que nos deparamos com a tarefa de gerar todos os prefixos possíveis para uma string '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;
Tem certeza de que precisa de recursão aqui? .. Se você usar LATERAL и generate_series, então você nem precisará de CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Alterar estrutura do banco de dados
Por exemplo, você tem uma tabela de mensagens do fórum com conexões de quem respondeu a quem, ou um tópico em :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Bem, uma solicitação típica para baixar todas as mensagens em um tópico é mais ou menos assim:
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;Mas como sempre precisamos de todo o tópico da mensagem raiz, por que não adicione seu ID a cada entrada automático?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Agora toda a nossa consulta recursiva pode ser reduzida apenas a isto:
SELECT
*
FROM
message
WHERE
theme_id = $1;Use "limitadores" aplicados
Se por algum motivo não conseguirmos alterar a estrutura do banco de dados, vamos ver em que podemos confiar para que mesmo a presença de um erro nos dados não leve a uma recursão infinita.
Contador de profundidade de recursão
Simplesmente aumentamos o contador em um a cada passo da recursão até atingirmos um limite que consideramos obviamente inadequado:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Quando tentamos fazer um loop, ainda não faremos mais do que o limite especificado de iterações “em profundidade”.
contras: Não há garantia de que não processaremos o mesmo registro novamente - por exemplo, a uma profundidade de 15 e 25 e depois a cada +10. E ninguém prometeu nada sobre “amplitude”.
Formalmente, tal recursão não será infinita, mas se a cada passo o número de registros aumentar exponencialmente, todos sabemos bem como isso termina...
Guardião do "caminho"
Alternativamente, adicionamos todos os identificadores de objeto que encontramos ao longo do caminho de recursão em um array, que é um “caminho” exclusivo para ele:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Se houver um ciclo nos dados, não processaremos de forma alguma o mesmo registro repetidamente no mesmo caminho.
contras: Mas, ao mesmo tempo, podemos literalmente ignorar todos os registros sem nos repetirmos.
Limite de comprimento do caminho
Para evitar a situação de recursão “vagando” em uma profundidade incompreensível, podemos combinar os dois métodos anteriores. Ou, se não quisermos suportar campos desnecessários, complemente a condição para continuar a recursão com uma estimativa do comprimento do caminho:
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
)
Escolha um método ao seu gosto!
Fonte: habr.com