PostgreSQL Antipatterns: "ומענדיקייַט איז נישט די שיעור!", אָדער אַ ביסל וועגן רעקורסיאָן

רעקורסיאָן - אַ זייער שטאַרק און באַקוועם מעקאַניזאַם אויב די זעלבע "טיפקייַט" אַקשאַנז זענען דורכגעקאָכט אויף פֿאַרבונדענע דאַטן. אָבער אַנקאַנטראָולד רעקורסיאָן איז אַ בייז וואָס קענען פירן צו יעדער סאָף דורכפירונג פּראָצעס, אָדער (וואָס כאַפּאַנז מער אָפט) צו "עסן" אַלע בנימצא זכּרון.

DBMS אין דעם אַכטונג אַרבעט אויף די זעלבע פּרינציפּן - "זיי האָבן מיר געזאָגט צו גראָבן, אַזוי איך גראָבן". דיין בקשה קענען ניט בלויז פּאַמעלעך די ארומיקע פּראַסעסאַז, קעסיידער נעמען פּראַסעסער רעסורסן, אָבער אויך "פאַלן" די גאנצע דאַטאַבייס, "עסן" אַלע בנימצא זכּרון. שוץ קעגן ינפאַנאַט רעקורסיאָן - די פֿאַראַנטוואָרטלעכקייט פון די דעוועלאָפּער זיך.

אין PostgreSQL, די פיייקייט צו נוצן רעקורסיווע פֿראגן דורך WITH RECURSIVE ארויס אין די ימאַמאָריאַל פון ווערסיע 8.4, אָבער איר קענען נאָך קעסיידער טרעפן פּאַטענטשאַלי שפּירעוודיק "דיפענסלעסס" ריקוועס. ווי צו באַפרייַען זיך פון פּראָבלעמס פון דעם מין?

דו זאלסט נישט שרייַבן רעקורסיווע פֿראגן

און שרייב נישט-רעקורסיווע. בעעמעס, דיין ק.אָ.

אין פאַקט, PostgreSQL גיט אַ פּלאַץ פון פאַנגקשאַנאַליטי אַז איר קענען נוצן קיין צולייגן רעקורסיאָן.

ניצן אַ פאַנדאַמענטאַלי אַנדערש צוגאַנג צו דעם פּראָבלעם

מאל איר קענען נאָר קוק די פּראָבלעם פון די "אַנדערש זייַט". איך האָב געגעבן אַ בייַשפּיל פון אַזאַ אַ סיטואַציע אין דעם אַרטיקל "SQL HowTo: 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;

ניצן generation_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