ProHoster > Blog > Идораи > Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия

рекурсия - механизми хеле пурқувват ва қулай, агар ҳамон амалҳои "амиқ" дар бораи додаҳои алоқаманд иҷро карда шаванд. Аммо рекурсияи беназорат як бадӣ аст, ки метавонад ба ҳарду оварда расонад иҷрои беохир раванд, ё (ки бештар рӯй медиҳад) ба тамоми хотираи дастрасро "хӯрдан".

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия
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;

Ба ҷои ҳалқаҳо generator_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);

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия
Хуб, дархости маъмулӣ барои зеркашии ҳама паёмҳо дар як мавзӯъ чунин аст:

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();

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия
Акнун тамоми дархости рекурсивии моро метавон танҳо ба ин кам кард:

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. Ва касе дар бораи «фано» чизе ваъда надод.

Ба таври расмӣ чунин рекурсия беохир нахоҳад буд, аммо агар дар ҳар як қадам шумораи сабтҳо ба таври экспоненсиалӣ зиёд шавад, мо ҳама хуб медонем, ки он чӣ гуна анҷом меёбад...

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсиянигаред ба "Мушкилоти гандум дар тахтаи шоҳмот"

Посбони "роҳ"

Мо бо навбат ҳамаи идентификаторҳои объектро, ки дар роҳи рекурсия дучор шудаем, ба массив илова мекунем, ки он "роҳ"-и беназири он аст:

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

Pro: Агар дар маълумот давра вуҷуд дошта бошад, мо комилан як сабтро дар як роҳ такроран коркард намекунем.
Омӯз: Аммо дар айни замон мо метавонем ба таври айнан ҳамаи сабтҳоро бидуни такрори худ канор гузорем.

Antipatterns PostgreSQL: "Беохирӣ маҳдудият нест!", Ё каме дар бораи рекурсия"Мушкилоти ҳаракати Найт" нигаред

Маҳдудияти дарозии роҳ

Барои роҳ надодан ба ҳолати «саргардонии» рекурсия дар умқи нофаҳмо, мо метавонем ду усули қаблиро якҷоя кунем. Ё, агар мо нахоҳем, ки майдонҳои нолозимро дастгирӣ кунем, шарти идомаи рекурсияро бо арзёбии дарозии роҳ илова кунед:

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
)

Усули мувофиқи табъи худ интихоб кунед!

Манбаъ: will.com

Илова Эзоҳ