- ένας πολύ ισχυρός και βολικός μηχανισμός εάν εκτελούνται οι ίδιες «σε βάθος» ενέργειες σε σχετικά δεδομένα. Αλλά η ανεξέλεγκτη υποτροπή είναι ένα κακό που μπορεί να οδηγήσει σε ένα από τα δύο ατελείωτη εκτέλεση διαδικασία, ή (που συμβαίνει πιο συχνά) σε «τρώγοντας» όλη τη διαθέσιμη μνήμη.
Το DBMS από αυτή την άποψη λειτουργεί με τις ίδιες αρχές - "Μου είπαν να σκάψω, έτσι σκάβω". Το αίτημά σας μπορεί όχι μόνο να επιβραδύνει τις γειτονικές διαδικασίες, καταλαμβάνοντας συνεχώς πόρους επεξεργαστή, αλλά και να "ρίξει" ολόκληρη τη βάση δεδομένων, "τρώγοντας" όλη τη διαθέσιμη μνήμη. Επομένως προστασία από άπειρη υποτροπή - ευθύνη του ίδιου του προγραμματιστή.
Στην PostgreSQL, η δυνατότητα χρήσης αναδρομικών ερωτημάτων μέσω εμφανίστηκε στα αμνημονεύτων χρόνων της έκδοσης 8.4, αλλά μπορείτε ακόμα να αντιμετωπίζετε τακτικά δυνητικά ευάλωτα ερωτήματα «ανάμυνας». Πώς να απαλλαγείτε από τέτοιου είδους προβλήματα;
Μην γράφετε επαναλαμβανόμενα ερωτήματα
Και γράψτε μη αναδρομικά. Με εκτίμηση, Κ.Ο.
Στην πραγματικότητα, η PostgreSQL παρέχει πολλές λειτουργίες που μπορείτε να χρησιμοποιήσετε όχι εφαρμόστε αναδρομή.
Χρησιμοποιήστε μια θεμελιωδώς διαφορετική προσέγγιση στο πρόβλημα
Μερικές φορές μπορείτε απλώς να δείτε το πρόβλημα από τη «διαφορετική πλευρά». Έδωσα ένα παράδειγμα μιας τέτοιας κατάστασης στο άρθρο — πολλαπλασιασμός ενός συνόλου αριθμών χωρίς τη χρήση προσαρμοσμένων συναρτήσεων συγκεντρωτικών στοιχείων:
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;Αυτό το αίτημα μπορεί να αντικατασταθεί με μια επιλογή από ειδικούς στα μαθηματικά:
WITH src AS (
SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
exp(sum(ln(prime)))::integer val
FROM
src;Χρησιμοποιήστε το generate_series αντί για βρόχους
Ας υποθέσουμε ότι βρισκόμαστε αντιμέτωποι με το καθήκον να δημιουργήσουμε όλα τα πιθανά προθέματα για μια συμβολοσειρά '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;
Είστε βέβαιοι ότι χρειάζεστε αναδρομή εδώ;.. Εάν χρησιμοποιείτε LATERAL и generate_series, τότε δεν θα χρειαστείτε καν CTE:
SELECT
substr(str, 1, ln) str
FROM
(VALUES('abcdefgh')) T(str)
, LATERAL(
SELECT generate_series(length(str), 1, -1) ln
) X;Αλλαγή δομής βάσης δεδομένων
Για παράδειγμα, έχετε έναν πίνακα με μηνύματα φόρουμ με συνδέσεις από ποιος απάντησε σε ποιον ή ένα νήμα στο :
CREATE TABLE message(
message_id
uuid
PRIMARY KEY
, reply_to
uuid
REFERENCES message
, body
text
);
CREATE INDEX ON message(reply_to);
Λοιπόν, ένα τυπικό αίτημα για λήψη όλων των μηνυμάτων σε ένα θέμα μοιάζει κάπως έτσι:
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;Αλλά επειδή χρειαζόμαστε πάντα ολόκληρο το θέμα από το βασικό μήνυμα, τότε γιατί να μην το κάνουμε προσθέστε το αναγνωριστικό του σε κάθε καταχώριση αυτόματο?
-- добавим поле с общим идентификатором темы и индекс на него
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();
Τώρα ολόκληρο το αναδρομικό ερώτημά μας μπορεί να περιοριστεί σε αυτό ακριβώς:
SELECT
*
FROM
message
WHERE
theme_id = $1;Χρησιμοποιήστε εφαρμοσμένους "περιοριστές"
Εάν δεν είμαστε σε θέση να αλλάξουμε τη δομή της βάσης δεδομένων για κάποιο λόγο, ας δούμε σε τι μπορούμε να βασιστούμε ώστε ακόμη και η παρουσία ενός σφάλματος στα δεδομένα να μην οδηγήσει σε ατελείωτη αναδρομή.
Μετρητής βάθους αναδρομής
Απλώς αυξάνουμε τον μετρητή κατά ένα σε κάθε βήμα αναδρομής μέχρι να φτάσουμε σε ένα όριο που θεωρούμε προφανώς ανεπαρκές:
WITH RECURSIVE T AS (
SELECT
0 i
...
UNION ALL
SELECT
i + 1
...
WHERE
T.i < 64 -- предел
)
Pro: Όταν προσπαθούμε να κάνουμε βρόχο, δεν θα κάνουμε περισσότερα από το καθορισμένο όριο επαναλήψεων "σε βάθος".
Μειονεκτήματα: Δεν υπάρχει καμία εγγύηση ότι δεν θα επεξεργαστούμε ξανά την ίδια εγγραφή - για παράδειγμα, σε βάθος 15 και 25, και μετά κάθε +10. Και κανείς δεν υποσχέθηκε τίποτα για το «πλάτος».
Τυπικά, μια τέτοια αναδρομή δεν θα είναι άπειρη, αλλά αν σε κάθε βήμα ο αριθμός των εγγραφών αυξάνεται εκθετικά, όλοι ξέρουμε καλά πώς τελειώνει...
Φύλακας του "μονοπατιού"
Προσθέτουμε εναλλάξ όλα τα αναγνωριστικά αντικειμένων που συναντήσαμε κατά μήκος της διαδρομής αναδρομής σε έναν πίνακα, ο οποίος είναι μια μοναδική "διαδρομή" σε αυτόν:
WITH RECURSIVE T AS (
SELECT
ARRAY[id] path
...
UNION ALL
SELECT
path || id
...
WHERE
id <> ALL(T.path) -- не совпадает ни с одним из
)
Pro: Εάν υπάρχει ένας κύκλος στα δεδομένα, δεν θα επεξεργαστούμε την ίδια εγγραφή επανειλημμένα στην ίδια διαδρομή.
Μειονεκτήματα: Αλλά ταυτόχρονα, μπορούμε κυριολεκτικά να παρακάμψουμε όλα τα ρεκόρ χωρίς να επαναλαμβανόμαστε.
Όριο μήκους διαδρομής
Για να αποφύγουμε την κατάσταση της αναδρομικής «περιπλάνησης» σε ακατανόητο βάθος, μπορούμε να συνδυάσουμε τις δύο προηγούμενες μεθόδους. Ή, αν δεν θέλουμε να υποστηρίξουμε περιττά πεδία, συμπληρώστε τη συνθήκη για τη συνέχιση της αναδρομής με μια εκτίμηση του μήκους της διαδρομής:
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
)
Επιλέξτε μια μέθοδο σύμφωνα με το γούστο σας!
Πηγή: www.habr.com