ProHoster > Π‘Π»ΠΎΠ³ > Pagdumala > Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursion

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursion

Pagbalikbalik - usa ka kusgan kaayo ug kombenyente nga mekanismo kung ang parehas nga "lawom" nga mga aksyon gihimo sa may kalabotan nga datos. Apan ang dili makontrol nga pagbalik-balik usa ka daotan nga mahimong mosangpot sa bisan asa walay katapusan nga pagpatay proseso, o (nga mahitabo mas kanunay) sa "pagkaon" sa tanan nga magamit nga memorya.

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursion
Ang DBMS niining bahina nagtrabaho sa parehas nga mga prinsipyo - "Gisultihan ko nila nga magkalot, mao nga akong gikalot". Ang imong hangyo dili lamang makapahinay sa mga silingang proseso, kanunay nga nagkuha sa mga kahinguhaan sa processor, apan "ihulog" usab ang tibuok database, "pagkaon" sa tanang magamit nga memorya. panalipod batok sa walay katapusan nga recursion - ang responsibilidad sa developer mismo.

Sa PostgreSQL, ang abilidad sa paggamit sa recursive nga mga pangutana pinaagi sa WITH RECURSIVE nagpakita sa karaan nga panahon sa bersyon 8.4, apan mahimo ka gihapon nga kanunay nga makasugat sa posibleng huyang nga "walay depensa" nga mga pangutana. Unsaon pagtangtang sa imong kaugalingon sa ingon niini nga mga problema?

Ayaw pagsulat ug recursive nga mga pangutana

Ug isulat ang mga dili recursive. Sinsero, Imong K.O.

Sa tinuud, ang PostgreSQL naghatag daghang daghang gamit nga magamit nimo dili pag-apply recursion.

Gamita ang usa ka sukaranan nga lahi nga pamaagi sa problema

Usahay mahimo nimong tan-awon ang problema gikan sa "lainlain nga bahin". Naghatag ako usa ka pananglitan sa ingon nga kahimtang sa artikulo "SQL HowTo: 1000 ug usa ka paagi sa paghugpong" - pagpadaghan sa usa ka hugpong sa mga numero nga wala gigamit ang naandan nga aggregate function:

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;

Kini nga hangyo mahimong pulihan sa usa ka kapilian gikan sa mga eksperto sa matematika:

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

Gamita ang generate_series imbes nga mga loops

Ingnon ta nga nag-atubang kita sa tahas sa paghimo sa tanan nga posible nga prefix alang sa usa ka hilo '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;

Sigurado ka nga kinahanglan nimo ang recursion dinhi?.. Kung gigamit nimo LATERAL ΠΈ generate_series, unya dili na nimo kinahanglan ang CTE:

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

Usba ang istruktura sa database

Pananglitan, ikaw adunay usa ka talaan sa mga mensahe sa forum nga adunay mga koneksyon gikan sa kinsa mitubag kung kinsa, o usa ka thread sa social network:

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

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursion
Aw, usa ka kasagaran nga hangyo nga i-download ang tanan nga mga mensahe sa usa ka hilisgutan ingon niini:

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;

Apan tungod kay kinahanglan naton kanunay ang tibuuk nga hilisgutan gikan sa punoan nga mensahe, nan ngano nga dili naton idugang ang ID niini sa matag entry awtomatiko?

-- Π΄ΠΎΠ±Π°Π²ΠΈΠΌ ΠΏΠΎΠ»Π΅ с ΠΎΠ±Ρ‰ΠΈΠΌ ΠΈΠ΄Π΅Π½Ρ‚ΠΈΡ„ΠΈΠΊΠ°Ρ‚ΠΎΡ€ΠΎΠΌ Ρ‚Π΅ΠΌΡ‹ ΠΈ индСкс Π½Π° Π½Π΅Π³ΠΎ
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();

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursion
Karon ang among tibuok nga recursive nga pangutana mahimong mapakunhod ngadto lamang niini:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Gamita ang gipadapat nga "limitasyon"

Kung dili naton mabag-o ang istruktura sa database tungod sa usa ka hinungdan, tan-awon naton kung unsa ang atong masaligan aron bisan ang presensya sa usa ka sayup sa datos dili mosangpot sa walay katapusan nga pagbalik-balik.

Recursion depth counter

Gidugangan lang namo ang counter sa usa sa matag lakang sa pagbalik-balik hangtod nga makaabot kami sa limitasyon nga among gikonsiderar nga klaro nga dili igo:

WITH RECURSIVE T AS (
  SELECT
    0 i
  ...
UNION ALL
  SELECT
    i + 1
  ...
  WHERE
    T.i < 64 -- ΠΏΡ€Π΅Π΄Π΅Π»
)

Pro: Kung kita mosulay sa pag-loop, dili gihapon kita mobuhat og labaw pa sa gitakda nga limitasyon sa mga pag-uli "sa giladmon".
disbentaha: Walay garantiya nga dili na namo iproseso pag-usab ang parehas nga rekord - pananglitan, sa giladmon nga 15 ug 25, ug dayon matag +10. Ug walay usa nga misaad sa bisan unsa mahitungod sa "kalapad".

Sa pormal nga paagi, ang ingon nga pagbalik-balik dili mahimong walay katapusan, apan kung sa matag lakang ang gidaghanon sa mga rekord modaghan pag-ayo, kitang tanan nahibal-an kung giunsa kini matapos ...

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursiontan-awa ang "Ang problema sa mga lugas sa usa ka chessboard"

Tigbantay sa "dalan"

Gipuli-pulihan namon nga idugang ang tanan nga mga identifier sa butang nga among nasugatan sa agianan sa pagbalik sa usa ka laray, nga usa ka talagsaon nga "dalan" niini:

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] path
  ...
UNION ALL
  SELECT
    path || id
  ...
  WHERE
    id <> ALL(T.path) -- Π½Π΅ совпадаСт Π½ΠΈ с ΠΎΠ΄Π½ΠΈΠΌ ΠΈΠ·
)

Pro: Kung adunay usa ka siklo sa datos, dili gyud namo iproseso ang parehas nga rekord nga balik-balik sa parehas nga agianan.
disbentaha: Apan sa samang higayon, literal natong malaktawan ang tanang mga rekord nga dili na masubli ang atong kaugalingon.

Mga Antipattern sa PostgreSQL: "Ang Infinity dili ang limitasyon!", o Usa ka gamay bahin sa recursiontan-awa ang "Knight's Move Problem"

Limit sa Gitas-on sa Dalan

Aron malikayan ang sitwasyon sa recursion nga "paglatagaw" sa dili masabtan nga giladmon, mahimo natong ikombinar ang duha ka nangaging mga pamaagi. O, kung dili namo gusto nga suportahan ang wala kinahanglana nga mga natad, dugangi ang kondisyon alang sa pagpadayon sa recursion nga adunay banabana sa gitas-on sa agianan:

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
)

Pagpili usa ka pamaagi sa imong lami!

Source: www.habr.com

Idugang sa usa ka comment