🥇I-PostgreSQL Antipatterns: “I-Infinity akuwona umkhawulo!”, noma Okuncane mayelana nokuphindaphinda

Ukuphindaphinda — очень мощный и удобный механизм, если над связанными данными делаются одни и те же действия «вглубь». Но неконтролируемая рекурсия — зло, которое может приводить или к ukubulawa okungapheli inqubo, noma (okwenzeka kaningi) ukuze "ukudla" yonke inkumbulo etholakalayo.

I-DBMS kulokhu isebenza ngezimiso ezifanayo - "Bathi angimbe, ngakho ngiyamba". Isicelo sakho asikwazi nje ukunciphisa izinqubo ezingomakhelwane, sithatha njalo izinsiza zokucubungula, kodwa futhi "silahle" yonke imininingwane egciniwe, "idla" yonke inkumbulo etholakalayo. Ngakho-ke isivikelo ekuphindaphindeni okungapheli - umthwalo womthuthukisi ngokwakhe.

Ku-PostgreSQL, ikhono lokusebenzisa imibuzo ephindaphindayo nge WITH RECURSIVE yavela esikhathini eside esidlule senguqulo 8.4, kodwa usengakwazi ukuhlangabezana nezicelo ezingase zibe sengozini "yokungavikeleki". Ungasusa kanjani izinkinga zalolu hlobo?

Ungabhali imibuzo eyimpinda

Futhi bhala ezingaphindi. Ozithobayo, u-K.O.

Eqinisweni, i-PostgreSQL ihlinzeka ngokusebenza okuningi ongakusebenzisa kukho hhayi sebenzisa ukuphindaphinda.

Sebenzisa indlela ehluke ngokuphelele kule nkinga

Kwesinye isikhathi ungabheka inkinga "ohlangothini oluhlukile". Nginikeze isibonelo sesimo esinjalo esihlokweni "SQL HowTo: 1000 kanye nendlela eyodwa yokuhlanganisa" - ukuphindaphindeka kwesethi yezinombolo ngaphandle kokusebenzisa imisebenzi ehlanganisiwe yangokwezifiso:

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;

Lesi sicelo singashintshwa ngokukhethwa kochwepheshe bezibalo:

WITH src AS (
  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
  exp(sum(ln(prime)))::integer val
FROM
  src;

Sebenzisa i-generative_series esikhundleni samaluphu

Ake sithi sibhekene nomsebenzi wokukhiqiza zonke iziqalo ezingaba khona zeyunithi yezinhlamvu '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;

Uqinisekile ukuthi udinga ukuphindaphinda lapha?.. Uma usebenzisa LATERAL и generate_series, khona-ke ngeke uze udinge i-CTE:

SELECT
  substr(str, 1, ln) str
FROM
  (VALUES('abcdefgh')) T(str)
, LATERAL(
    SELECT generate_series(length(str), 1, -1) ln
  ) X;

Shintsha isakhiwo sesizindalwazi

Isibonelo, unethebula lemilayezo yenkundla enokuxhumana okusuka kokuthi ubani ophendule kubani, noma uchungechunge phakathi inethiwekhi Yokuxhumana:

CREATE TABLE message(
  message_id
    uuid
      PRIMARY KEY
, reply_to
    uuid
      REFERENCES message
, body
    text
);
CREATE INDEX ON message(reply_to);

Nokho, isicelo esijwayelekile sokulanda yonke imilayezo esihlokweni esisodwa sibukeka kanje:

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;

Kodwa njengoba sihlala sidinga sonke isihloko esivela emlayezweni wempande, kungani-ke singakwenzi engeza i-ID yayo ekungeneni ngakunye okuzenzakalelayo?

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

Manje wonke umbuzo wethu ophindaphindayo ungancishiswa kube lokhu nje:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Sebenzisa "imikhawulo" esetshenzisiwe

Uma singakwazi ukushintsha ukwakheka kwesizindalwazi ngesizathu esithile, ake sibone ukuthi yini esingathembela kukho ukuze ngisho nokuba khona kwephutha kudatha kungaholeli ekuphindaphindeni okungapheli.

Ikhawunta yokujula kokuphindaphinda

Simane sikhulise ikhawunta ngayinye esinyathelweni ngasinye sokuphindaphinda kuze kube yilapho sifinyelela umkhawulo esiwubheka njengonganele:

WITH RECURSIVE T AS (
  SELECT
    0 i
  ...
UNION ALL
  SELECT
    i + 1
  ...
  WHERE
    T.i < 64 -- предел
)

Pro: Uma sizama ukwenza iluphu, sisazokwenza okungaphezu komkhawulo oshiwo wokuphindaphinda “ngokujulile”.
nebubi: Asikho isiqinisekiso sokuthi ngeke siphinde sicubungule irekhodi elifanayo - isibonelo, ekujuleni kuka-15 no-25, bese kuba njalo ku-+10. Futhi akekho owathembisa okuthile "ngobubanzi".

Ngokusemthethweni, ukuphindaphinda okunjalo ngeke kube okungapheli, kodwa uma esinyathelweni ngasinye inani lamarekhodi likhula ngokushesha, sonke sazi kahle ukuthi iphetha kanjani...

bheka "Inkinga yokusanhlamvu ebhodini le-chess"

Umnakekeli "wendlela"

Sishintshana sengeza zonke izihlonzi zento esihlangabezane nazo endleleni ye-recursion zibe amalungu afanayo, “okuyindlela” ehlukile eya kukho:

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

Pro: Uma kunomjikelezo kudatha, ngeke neze sicubungule irekhodi elifanayo ngokuphindaphindiwe ngaphakathi kwendlela efanayo.
nebubi: Kodwa ngesikhathi esifanayo, singaweqa ngokoqobo wonke amarekhodi ngaphandle kokuziphindaphinda.

bona "Knight's Move Problem"

Umkhawulo Wobude Bendlela

Ukuze ugweme isimo sokuphindaphinda "ukuzulazula" ekujuleni okungaqondakali, singahlanganisa izindlela ezimbili zangaphambilini. Noma, uma singafuni ukusekela izinkambu ezingadingekile, gcwalisa umbandela wokuqhubeka nokuphindaphinda ngesilinganiso sobude bendlela:

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
)

Khetha indlela yokunambitha kwakho!

Source: www.habr.com

I-PostgreSQL Antipatterns: "I-Infinity ayiwona umkhawulo!", Noma Okuncane mayelana nokuphindaphinda