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;

Օգտագործեք gener_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 -- предел
)

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
)

Ընտրեք ձեր ճաշակի մեթոդը:

Source: www.habr.com