- e ganz mächtegen a praktesche Mechanismus wann déi selwecht "an-Déift" Aktiounen op verbonne Donnéeën gemaach ginn. Awer onkontrolléiert Rekursioun ass e Béis dat zu entweder féieren kann endlos Ausféierung Prozess, oder (wat geschitt méi dacks) zu "iessen" all verfügbar Erënnerung.
DBMS an dëser Hisiicht funktionnéiert op déiselwecht Prinzipien - "Si sot mir graven, also ech graven". Är Ufro kann net nëmmen d'Nopeschprozesser verlangsamen, dauernd Prozessorressourcen ophuelen, awer och d'ganz Datebank "fréien", all verfügbar Erënnerung "iessen". Schutz géint onendlech Rekursioun - d'Verantwortung vum Entwéckler selwer.
An PostgreSQL, d'Fäegkeet fir rekursiv Ufroen ze benotzen via erschéngt an der onheemlecher Zäit vun der Versioun 8.4, awer Dir kënnt ëmmer nach reegelméisseg potenziell vulnérabel "verdeedegt" Ufroen begéinen. Wéi kënnt Dir Iech vu Probleemer vun dëser Aart befreien?
Schreift keng rekursiv Ufroen
A schreift net-rekursiv. Mat frëndleche Gréiss, Är K.O.
Tatsächlech bitt PostgreSQL zimmlech vill Funktionalitéit déi Dir benotze kënnt Net Rekursioun applizéieren.
Benotzt eng fundamental aner Approche zum Problem
Heiansdo kënnt Dir de Problem just vun der "aner Säit" kucken. Ech hunn e Beispill vun esou enger Situatioun am Artikel ginn - Multiplikatioun vun enger Rei vun Zuelen ouni Benotzerdefinéiert aggregéiert Funktiounen ze benotzen:
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;Dës Ufro kann duerch eng Optioun vu Mathematikexperten ersat ginn:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Benotzt Generate_series anstatt Loops
Loosst eis soen datt mir mat der Aufgab konfrontéiert sinn all méiglech Präfixe fir eng String ze generéieren '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;
Sidd Dir sécher datt Dir Rekursioun hei braucht? .. Wann Dir benotzt LATERAL и generate_series, da braucht Dir net emol CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Datebank Struktur änneren
Zum Beispill hutt Dir en Dësch mat Forum Messagen mat Verbindunge vu wien op wiem geäntwert huet, oder e Fuedem an :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Gutt, eng typesch Ufro fir all Messagen op engem Thema erofzelueden gesäit sou eppes aus:
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;Awer well mir ëmmer dat ganzt Thema aus der Rootmeldung brauchen, firwat net add seng ID op all Entrée automatesch?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Elo kann eis ganz rekursiv Ufro op just dëst reduzéiert ginn:
SELECT
*
FROM
message
WHERE
theme_id = $1;Benotzt applizéiert "Limiter"
Wa mir net fäeg sinn d'Struktur vun der Datebank aus irgendege Grënn z'änneren, loosst eis kucken op wat mir kënne vertrauen, sou datt och d'Präsenz vun engem Feeler an den Donnéeën net zu endlos Rekursioun féiert.
Rekursioun Déift Konter
Mir erhéijen einfach de Konter ëm een bei all Rekursiounsschrëtt bis mir eng Limit erreechen, déi mir als offensichtlech net genuch betruechten:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Wa mir probéieren ze Loop, maache mir nach ëmmer net méi wéi déi spezifizéiert Limit vun Iteratiounen "an Déift".
scheinbar: Et gëtt keng Garantie, datt mir net déi selwecht Rekord Prozess wäert erëm - zum Beispill, an enger Déift vun 15 an 25, an dann all +10. A keen huet eppes iwwer "Breet" versprach.
Formell wäert esou e Rekursioun net onendlech sinn, awer wann op all Schrëtt d'Zuel vun den Opzeechnungen exponentiell eropgeet, wësse mer all gutt wéi et endet ...
Wuechter vum "Wee"
Mir addéieren ofwiesselnd all Objektidentifizéierer déi mir laanscht de Rekursiounswee begéint hunn an eng Array, déi en eenzegaartege "Wee" dozou ass:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Wann et en Zyklus an den Donnéeën ass, wäerte mir absolut net dee selwechte Rekord ëmmer erëm am selwechte Wee veraarbechten.
scheinbar: Awer gläichzäiteg kënne mir wuertwiertlech all Rekorder ëmgoen ouni eis selwer ze widderhuelen.
Wee Längt Limite
Fir d'Situatioun vun der Rekursioun "wandert" op enger onverständlecher Déift ze vermeiden, kënne mir déi zwee virdrun Methoden kombinéieren. Oder, wa mir net onnéideg Felder ënnerstëtzen wëllen, ergänzen d'Konditioun fir d'Rekursioun weider mat enger Schätzung vun der Weelängt:
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
)
Wielt eng Method no Ärem Goût!
Source: will.com