DBMS i denne forbindelse jobber etter de samme prinsippene - "De ba meg grave, så jeg graver". Forespørselen din kan ikke bare bremse naboprosesser, stadig ta opp prosessorressurser, men også "slippe" hele databasen og "spise" alt tilgjengelig minne. Derfor beskyttelse mot uendelig rekursjon - ansvar for utbygger selv.
I PostgreSQL, muligheten til å bruke rekursive spørringer via WITH RECURSIVE
Ikke skriv rekursive søk
Og skriv ikke-rekursive. Vennlig hilsen din K.O.
Faktisk gir PostgreSQL ganske mye funksjonalitet som du kan bruke til no bruke rekursjon.
Bruk en fundamentalt annen tilnærming til problemet
Noen ganger kan du bare se på problemet fra den "anne siden". Jeg ga et eksempel på en slik situasjon i artikkelen
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;
Denne forespørselen kan erstattes med et alternativ fra matematikkeksperter:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;
Bruk gener_series i stedet for loops
La oss si at vi står overfor oppgaven med å generere alle mulige prefikser for en streng '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;
Er du sikker på at du trenger rekursjon her?.. Hvis du bruker LATERAL
и generate_series
, da trenger du ikke engang CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;
Endre databasestruktur
Du har for eksempel en tabell med forummeldinger med forbindelser fra hvem som har svart til hvem, eller en tråd inn
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Vel, en typisk forespørsel om å laste ned alle meldinger om ett emne ser omtrent slik ut:
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;
Men siden vi alltid trenger hele emnet fra rotmeldingen, hvorfor gjør vi det ikke legg til ID-en til hver oppføring Automatisk?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Nå kan hele det rekursive søket vårt reduseres til nettopp dette:
SELECT
*
FROM
message
WHERE
theme_id = $1;
Bruk brukte "begrensere"
Hvis vi av en eller annen grunn ikke er i stand til å endre strukturen til databasen, la oss se hva vi kan stole på slik at selv tilstedeværelsen av en feil i dataene ikke fører til endeløs rekursjon.
Rekursjonsdybdeteller
Vi øker ganske enkelt telleren med én for hvert rekursjonstrinn til vi når en grense som vi anser som åpenbart utilstrekkelig:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Når vi prøver å loope, vil vi fortsatt ikke gjøre mer enn den angitte grensen for iterasjoner "i dybden".
ulemper: Det er ingen garanti for at vi ikke vil behandle den samme posten igjen - for eksempel på en dybde på 15 og 25, og deretter hver +10. Og ingen lovet noe om "bredde".
Formelt sett vil ikke en slik rekursjon være uendelig, men hvis antallet poster øker eksponentielt for hvert trinn, vet vi alle godt hvordan det ender...
Vokter av "stien"
Vi legger vekselvis til alle objektidentifikatorene vi møtte langs rekursjonsbanen til en matrise, som er en unik "sti" til den:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Hvis det er en syklus i dataene, vil vi absolutt ikke behandle den samme posten gjentatte ganger innenfor den samme banen.
ulemper: Men samtidig kan vi bokstavelig talt omgå alle postene uten å gjenta oss selv.
Banelengdegrense
For å unngå situasjonen med rekursjon "vandrende" på en uforståelig dybde, kan vi kombinere de to foregående metodene. Eller, hvis vi ikke ønsker å støtte unødvendige felt, suppler betingelsen for å fortsette rekursjonen med et estimat av veilengden:
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
)
Velg en metode etter din smak!
Kilde: www.habr.com