- një mekanizëm shumë i fuqishëm dhe i përshtatshëm nëse të njëjtat veprime "të thelluara" kryhen në të dhënat përkatëse. Por rekursioni i pakontrolluar është një e keqe që mund të çojë në njërën ose tjetrën ekzekutim i pafund proces, ose (që ndodh më shpesh) për të "ngrënia" e të gjithë kujtesës në dispozicion.
DBMS në këtë drejtim punojnë në të njëjtat parime - "Më thanë të gërmoj, kështu që unë gërmoj". Kërkesa juaj jo vetëm që mund të ngadalësojë proceset fqinje, duke marrë vazhdimisht burimet e procesorit, por gjithashtu "të hedhë" të gjithë bazën e të dhënave, duke "ngrënë" të gjithë memorien e disponueshme. Prandaj mbrojtje kundër rekursionit të pafund - përgjegjësia e vetë zhvilluesit.
Në PostgreSQL, aftësia për të përdorur pyetje rekursive nëpërmjet u shfaq në kohët e lashta të versionit 8.4, por ende mund të hasni rregullisht pyetje "të pambrojtur" potencialisht të cenueshme. Si të shpëtoni nga problemet e këtij lloji?
Mos shkruani pyetje rekursive
Dhe shkruani ato jo-rekurzive. Sinqerisht, K.O juaj.
Në fakt, PostgreSQL ofron mjaft funksionalitete që mund t'i përdorni jo aplikoni rekursionin.
Përdorni një qasje thelbësisht të ndryshme ndaj problemit
Ndonjëherë mund ta shikoni problemin vetëm nga "ana e ndryshme". Unë dhashë një shembull të një situate të tillë në artikull - shumëzimi i një grupi numrash pa përdorur funksione agreguese të personalizuara:
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;Kjo kërkesë mund të zëvendësohet me një opsion nga ekspertët e matematikës:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Përdor gjenerimin_seri në vend të sytheve
Le të themi se jemi përballur me detyrën për të gjeneruar të gjitha parashtesat e mundshme për një varg '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;
Jeni i sigurt që keni nevojë për rekursion këtu?.. Nëse përdorni LATERAL и generate_series, atëherë nuk do t'ju duhet as CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Ndryshoni strukturën e bazës së të dhënave
Për shembull, ju keni një tabelë me mesazhe të forumit me lidhje nga kush i është përgjigjur, ose një temë në :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Epo, një kërkesë tipike për të shkarkuar të gjitha mesazhet në një temë duket diçka si kjo:
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;Por meqenëse ne gjithmonë kemi nevojë për të gjithë temën nga mesazhi rrënjësor, atëherë pse të mos kemi nevojë shtoni ID-në e tij në çdo hyrje automatike?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Tani e gjithë pyetja jonë rekursive mund të reduktohet vetëm në këtë:
SELECT
*
FROM
message
WHERE
theme_id = $1;Përdorni "kufizuesit" e aplikuar
Nëse nuk jemi në gjendje të ndryshojmë strukturën e bazës së të dhënave për ndonjë arsye, le të shohim se në çfarë mund të mbështetemi në mënyrë që edhe prania e një gabimi në të dhëna të mos çojë në rekursion të pafund.
Numëruesi i thellësisë së rekursionit
Ne thjesht e rrisim numëruesin me një në çdo hap rekursioni derisa të arrijmë një kufi që e konsiderojmë dukshëm të pamjaftueshëm:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Kur përpiqemi të bëjmë lak, ne ende nuk do të bëjmë më shumë se kufiri i specifikuar i përsëritjeve "në thellësi".
Cons: Nuk ka asnjë garanci që ne nuk do të përpunojmë përsëri të njëjtin rekord - për shembull, në një thellësi prej 15 dhe 25, dhe pastaj çdo +10. Dhe askush nuk premtoi asgjë për "gjerësinë".
Formalisht, një rekursion i tillë nuk do të jetë i pafund, por nëse në çdo hap numri i rekordeve rritet në mënyrë eksponenciale, të gjithë e dimë mirë se si përfundon...
Mbrojtësi i "rrugës"
Ne i shtojmë në mënyrë alternative të gjithë identifikuesit e objekteve që kemi hasur përgjatë shtegut të rekursionit në një grup, i cili është një "rrugë" unike për të:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Nëse ka një cikël në të dhëna, ne absolutisht nuk do të përpunojmë të njëjtin rekord në mënyrë të përsëritur brenda së njëjtës rrugë.
Cons: Por në të njëjtën kohë, ne mund të anashkalojmë fjalë për fjalë të gjitha të dhënat pa u përsëritur.
Kufiri i gjatësisë së shtegut
Për të shmangur situatën e "bredhjes" së rekursionit në një thellësi të pakuptueshme, ne mund të kombinojmë dy metodat e mëparshme. Ose, nëse nuk duam të mbështesim fusha të panevojshme, plotësoni kushtin për vazhdimin e rekursionit me një vlerësim të gjatësisë së shtegut:
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
)
Zgjidhni një metodë për shijen tuaj!
Burimi: www.habr.com