PostgreSQL ಆಂಟಿಪ್ಯಾಟರ್ನ್‌ಗಳು: “ಅನಂತವು ಮಿತಿಯಲ್ಲ!”, ಅಥವಾ ಪುನರಾವರ್ತನೆಯ ಬಗ್ಗೆ ಸ್ವಲ್ಪ

ಪುನರಾವರ್ತನೆ - ಸಂಬಂಧಿತ ಡೇಟಾದಲ್ಲಿ ಅದೇ "ಆಳವಾದ" ಕ್ರಿಯೆಗಳನ್ನು ನಿರ್ವಹಿಸಿದರೆ ಅತ್ಯಂತ ಶಕ್ತಿಯುತ ಮತ್ತು ಅನುಕೂಲಕರ ಕಾರ್ಯವಿಧಾನ. ಆದರೆ ಅನಿಯಂತ್ರಿತ ಪುನರಾವರ್ತನೆಯು ದುಷ್ಟತನವಾಗಿದ್ದು ಅದು ಎರಡಕ್ಕೂ ಕಾರಣವಾಗಬಹುದು ಅಂತ್ಯವಿಲ್ಲದ ಮರಣದಂಡನೆ ಪ್ರಕ್ರಿಯೆ, ಅಥವಾ (ಇದು ಹೆಚ್ಚಾಗಿ ಸಂಭವಿಸುತ್ತದೆ) ಗೆ ಲಭ್ಯವಿರುವ ಎಲ್ಲಾ ಮೆಮೊರಿಯನ್ನು "ತಿನ್ನುವುದು".

ಈ ನಿಟ್ಟಿನಲ್ಲಿ DBMS ಅದೇ ತತ್ವಗಳ ಮೇಲೆ ಕೆಲಸ ಮಾಡುತ್ತದೆ - "ಅವರು ನನಗೆ ಅಗೆಯಲು ಹೇಳಿದರು, ಹಾಗಾಗಿ ನಾನು ಅಗೆಯುತ್ತೇನೆ". ನಿಮ್ಮ ವಿನಂತಿಯು ನೆರೆಯ ಪ್ರಕ್ರಿಯೆಗಳನ್ನು ನಿಧಾನಗೊಳಿಸುತ್ತದೆ, ನಿರಂತರವಾಗಿ ಪ್ರೊಸೆಸರ್ ಸಂಪನ್ಮೂಲಗಳನ್ನು ತೆಗೆದುಕೊಳ್ಳುತ್ತದೆ, ಆದರೆ ಸಂಪೂರ್ಣ ಡೇಟಾಬೇಸ್ ಅನ್ನು "ಡ್ರಾಪ್" ಮಾಡುತ್ತದೆ, ಲಭ್ಯವಿರುವ ಎಲ್ಲಾ ಮೆಮೊರಿಯನ್ನು "ತಿನ್ನುತ್ತದೆ". ಆದ್ದರಿಂದ ಅನಂತ ಪುನರಾವರ್ತನೆಯ ವಿರುದ್ಧ ರಕ್ಷಣೆ - ಡೆವಲಪರ್ ಸ್ವತಃ ಜವಾಬ್ದಾರಿ.

PostgreSQL ನಲ್ಲಿ, ಮೂಲಕ ಪುನರಾವರ್ತಿತ ಪ್ರಶ್ನೆಗಳನ್ನು ಬಳಸುವ ಸಾಮರ್ಥ್ಯ WITH RECURSIVE ಆವೃತ್ತಿ 8.4 ರ ಪ್ರಾಚೀನ ಕಾಲದಲ್ಲಿ ಕಾಣಿಸಿಕೊಂಡಿತು, ಆದರೆ ನೀವು ಇನ್ನೂ ನಿಯಮಿತವಾಗಿ ಸಂಭಾವ್ಯ ದುರ್ಬಲ "ರಕ್ಷಣಾ ರಹಿತ" ವಿನಂತಿಗಳನ್ನು ಎದುರಿಸಬಹುದು. ಈ ರೀತಿಯ ಸಮಸ್ಯೆಗಳಿಂದ ನಿಮ್ಮನ್ನು ಹೇಗೆ ಮುಕ್ತಗೊಳಿಸುವುದು?

ಪುನರಾವರ್ತಿತ ಪ್ರಶ್ನೆಗಳನ್ನು ಬರೆಯಬೇಡಿ

ಮತ್ತು ಪುನರಾವರ್ತಿತವಲ್ಲದವುಗಳನ್ನು ಬರೆಯಿರಿ. ವಿಧೇಯಪೂರ್ವಕವಾಗಿ, ನಿಮ್ಮ K.O.

ವಾಸ್ತವವಾಗಿ, PostgreSQL ನೀವು ಬಳಸಬಹುದಾದ ಸಾಕಷ್ಟು ಕಾರ್ಯಗಳನ್ನು ಒದಗಿಸುತ್ತದೆ ಕೇವಲ ಪುನರಾವರ್ತನೆಯನ್ನು ಅನ್ವಯಿಸಿ.

ಸಮಸ್ಯೆಗೆ ಮೂಲಭೂತವಾಗಿ ವಿಭಿನ್ನ ವಿಧಾನವನ್ನು ಬಳಸಿ

ಕೆಲವೊಮ್ಮೆ ನೀವು ಸಮಸ್ಯೆಯನ್ನು "ವಿಭಿನ್ನ ಬದಿಯಿಂದ" ನೋಡಬಹುದು. ಅಂತಹ ಪರಿಸ್ಥಿತಿಯ ಉದಾಹರಣೆಯನ್ನು ನಾನು ಲೇಖನದಲ್ಲಿ ನೀಡಿದ್ದೇನೆ "SQL ಹೇಗೆ: 1000 ಮತ್ತು ಒಟ್ಟುಗೂಡಿಸುವಿಕೆಯ ಒಂದು ಮಾರ್ಗ" - ಕಸ್ಟಮ್ ಒಟ್ಟು ಕಾರ್ಯಗಳನ್ನು ಬಳಸದೆ ಸಂಖ್ಯೆಗಳ ಗುಂಪಿನ ಗುಣಾಕಾರ:

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;

ಆದರೆ ನಮಗೆ ಯಾವಾಗಲೂ ಮೂಲ ಸಂದೇಶದಿಂದ ಸಂಪೂರ್ಣ ವಿಷಯ ಬೇಕಾಗಿರುವುದರಿಂದ, ನಾವು ಏಕೆ ಮಾಡಬಾರದು ಪ್ರತಿ ಪ್ರವೇಶಕ್ಕೆ ಅದರ ID ಸೇರಿಸಿ ಸ್ವಯಂಚಾಲಿತ?

-- добавим поле с общим идентификатором темы и индекс на него
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 -- предел
)

ಪ್ರೊ: ನಾವು ಲೂಪ್ ಮಾಡಲು ಪ್ರಯತ್ನಿಸಿದಾಗ, "ಆಳದಲ್ಲಿ" ಪುನರಾವರ್ತನೆಗಳ ನಿಗದಿತ ಮಿತಿಗಿಂತ ಹೆಚ್ಚಿನದನ್ನು ನಾವು ಇನ್ನೂ ಮಾಡುವುದಿಲ್ಲ.
ಕಾನ್ಸ್: ನಾವು ಅದೇ ದಾಖಲೆಯನ್ನು ಮತ್ತೆ ಪ್ರಕ್ರಿಯೆಗೊಳಿಸುವುದಿಲ್ಲ ಎಂಬುದಕ್ಕೆ ಯಾವುದೇ ಗ್ಯಾರಂಟಿ ಇಲ್ಲ - ಉದಾಹರಣೆಗೆ, 15 ಮತ್ತು 25 ರ ಆಳದಲ್ಲಿ, ಮತ್ತು ನಂತರ ಪ್ರತಿ +10. ಮತ್ತು "ಅಗಲ" ದ ಬಗ್ಗೆ ಯಾರೂ ಭರವಸೆ ನೀಡಲಿಲ್ಲ.

ಔಪಚಾರಿಕವಾಗಿ, ಅಂತಹ ಪುನರಾವರ್ತನೆಯು ಅನಂತವಾಗಿರುವುದಿಲ್ಲ, ಆದರೆ ಪ್ರತಿ ಹಂತದಲ್ಲಿ ದಾಖಲೆಗಳ ಸಂಖ್ಯೆಯು ಘಾತೀಯವಾಗಿ ಹೆಚ್ಚಾದರೆ, ಅದು ಹೇಗೆ ಕೊನೆಗೊಳ್ಳುತ್ತದೆ ಎಂಬುದು ನಮಗೆಲ್ಲರಿಗೂ ಚೆನ್ನಾಗಿ ತಿಳಿದಿದೆ.

"ಚದುರಂಗ ಫಲಕದಲ್ಲಿ ಧಾನ್ಯಗಳ ಸಮಸ್ಯೆ" ನೋಡಿ

"ಮಾರ್ಗ" ದ ರಕ್ಷಕ

ಪುನರಾವರ್ತನೆಯ ಹಾದಿಯಲ್ಲಿ ನಾವು ಎದುರಿಸಿದ ಎಲ್ಲಾ ಆಬ್ಜೆಕ್ಟ್ ಐಡೆಂಟಿಫೈಯರ್‌ಗಳನ್ನು ನಾವು ಪರ್ಯಾಯವಾಗಿ ಒಂದು ಶ್ರೇಣಿಗೆ ಸೇರಿಸುತ್ತೇವೆ, ಅದು ಅದಕ್ಕೆ ವಿಶಿಷ್ಟವಾದ "ಮಾರ್ಗ" ಆಗಿದೆ:

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] path
  ...
UNION ALL
  SELECT
    path || id
  ...
  WHERE
    id <> ALL(T.path) -- не совпадает ни с одним из
)

ಪ್ರೊ: ಡೇಟಾದಲ್ಲಿ ಚಕ್ರವಿದ್ದರೆ, ನಾವು ಒಂದೇ ದಾಖಲೆಯನ್ನು ಒಂದೇ ಹಾದಿಯಲ್ಲಿ ಪುನರಾವರ್ತಿತವಾಗಿ ಪ್ರಕ್ರಿಯೆಗೊಳಿಸುವುದಿಲ್ಲ.
ಕಾನ್ಸ್: ಆದರೆ ಅದೇ ಸಮಯದಲ್ಲಿ, ನಾವು ಪುನರಾವರ್ತಿಸದೆ ಎಲ್ಲಾ ದಾಖಲೆಗಳನ್ನು ಅಕ್ಷರಶಃ ಬೈಪಾಸ್ ಮಾಡಬಹುದು.

"ನೈಟ್ಸ್ ಮೂವ್ ಸಮಸ್ಯೆ" ನೋಡಿ

ಮಾರ್ಗದ ಉದ್ದದ ಮಿತಿ

ಗ್ರಹಿಸಲಾಗದ ಆಳದಲ್ಲಿ ಮರುಕಳಿಸುವ "ಅಲೆದಾಡುವ" ಪರಿಸ್ಥಿತಿಯನ್ನು ತಪ್ಪಿಸಲು, ನಾವು ಎರಡು ಹಿಂದಿನ ವಿಧಾನಗಳನ್ನು ಸಂಯೋಜಿಸಬಹುದು. ಅಥವಾ, ನಾವು ಅನಗತ್ಯ ಕ್ಷೇತ್ರಗಳನ್ನು ಬೆಂಬಲಿಸಲು ಬಯಸದಿದ್ದರೆ, ಪಥದ ಉದ್ದದ ಅಂದಾಜಿನೊಂದಿಗೆ ಪುನರಾವರ್ತನೆಯನ್ನು ಮುಂದುವರಿಸುವ ಸ್ಥಿತಿಯನ್ನು ಪೂರಕಗೊಳಿಸಿ:

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