🥇PostgreSQL Antipatterns: "Infinity ayingomda!", Okanye kancinci malunga nokuphindaphinda

Ukuphindaphinda -isixhobo esinamandla kakhulu kwaye esifanelekileyo ukuba izenzo ezifanayo "ezinzulu" zenziwa kwidatha ehambelanayo. Kodwa ukuphindaphinda okungalawulekiyo bububi obunokukhokelela kuzo zombini ukubulawa okungapheliyo inkqubo, okanye (okwenzeka rhoqo) ukuya "ukutya" yonke imemori ekhoyo.

I-DBMS kule nkalo isebenza kwimigaqo efanayo - "Bathi mandigrumbe, ndiye ndomba". Isicelo sakho asinakucothisa kuphela iinkqubo ezingabamelwane, ukuthatha rhoqo izixhobo zeprosesa, kodwa "ulahla" yonke isiseko sedatha, "ukutya" yonke imemori ekhoyo. ukhuseleko ngokuchasene nokuphindaphinda okungapheliyo - uxanduva lomphuhlisi ngokwakhe.

Kwi-PostgreSQL, ukukwazi ukusebenzisa imibuzo ephindaphindayo nge WITH RECURSIVE yavela kumaxesha amandulo enguquko yesi-8.4, kodwa usenako ukudibana rhoqo nezicelo “zokungakhuseleki” ezinokuba sesichengeni. Indlela yokuzikhupha kwiingxaki zolu hlobo?

Sukubhala imibuzo ephindaphindwayo

Kwaye ubhale non-recursive. Ozithobayo, K.O.

Ngapha koko, iPostgreSQL ibonelela ngomsebenzi omninzi onokuwusebenzisa hayi sebenzisa ukuphindaphinda.

Sebenzisa indlela eyahluke ngokupheleleyo kule ngxaki

Ngamanye amaxesha unokujonga nje ingxaki "kwicala elahlukileyo". Ndenze umzekelo wemeko enjalo kwinqaku "SQL HowTo: 1000 kunye nendlela enye yokudibanisa" -Uphinda-phindo lweseti yamanani ngaphandle kokusebenzisa aggregate imisebenzi yesiko:

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;

Esi sicelo sinokutshintshwa ngokhetho oluvela kwiingcali zemathematika:

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-generation_series endaweni yeelophu

Masithi sijongene nomsebenzi wokuvelisa zonke izimaphambili ezinokwenzeka zomtya '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 ukuba ufuna i-recursion apha?.. Ukuba uyasebenzisa LATERAL и generate_series, emva koko awuyi kufuna i-CTE:

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

Guqula ubume besiseko sedatha

Umzekelo, unetheyibhile yemiyalezo yeforum enonxibelelwano oluvela kubani uphendule kubani, okanye umsonto phakathi inethiwekhi yokuncokola:

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

Ewe, isicelo esiqhelekileyo sokukhuphela yonke imiyalezo kwisihloko esinye sijongeka ngolu hlobo:

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 ekubeni sisoloko sifuna isihloko siphela kumyalezo oyingcambu, kutheni singafuni yongeza i-ID yayo kwingeniso nganye oluzenzekelayo?

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

Ngoku wonke umbuzo wethu ophindaphindayo unokuncitshiswa kube koku nje:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Sebenzisa "imida" esetyenzisiweyo

Ukuba asikwazi ukutshintsha isakhiwo sesiseko sedatha ngesizathu esithile, makhe sibone ukuba yintoni esinokuthembela kuyo ukwenzela ukuba nokuba khona kwephutha kwidatha akuholeli kwi-recursion engapheliyo.

Ikhawuntara yobunzulu bokubuyisela

Sonyusa nje ikhawunta nganye kwinyathelo ngalinye lokuphinda sifike kumda esiwuthatha njengonganelanga:

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

Pro: Xa sizama ukulophu, asisayi kwenza ngaphezulu komda ochaziweyo wokuphindaphinda “bunzulu”.
bendlela: Akukho siqinisekiso sokuba asisayi kuphinda siqhubekise irekhodi efanayo-umzekelo, kubunzulu be-15 kunye ne-25, kwaye emva koko rhoqo +10. Kwaye akukho mntu wathembisa nantoni na "ngobubanzi".

Ngokusemthethweni, ukuphindaphinda okunjalo akuyi kuba nasiphelo, kodwa ukuba kwinqanaba ngalinye inani leerekhodi landa ngokukhawuleza, sonke siyazi kakuhle ukuba iphetha njani ...

bona "Ingxaki yeenkozo kwi-chessboard"

Umgcini "wendlela"

Sidibanisa zonke izichongi zento esiye sadibana nazo ecaleni kwendlela yokuphinda ibe kuluhlu, “indlela” eyodwa eya kuyo:

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

Pro: Ukuba kukho umjikelo kwidatha, asiyi kuphinda siqhube irekhodi enye ngokuphindaphindiweyo kwindlela enye.
bendlela: Kodwa kwangaxeshanye, sinokuzigqitha ngokoqobo zonke iirekhodi ngaphandle kokuziphinda.

bona "Knight's Move Problem"

Umda woBude beNdlela

Ukuze ugweme imeko yokuphindaphinda "ukuzulazula" kubunzulu obungaqondakaliyo, sinokudibanisa iindlela ezimbini zangaphambili. Okanye, ukuba asifuni kuxhasa imimandla engeyomfuneko, yongeza imeko yokuqhubeka nokuphindaphinda ngoqikelelo lobude 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 yokuthanda kwakho!

umthombo: www.habr.com

I-PostgreSQL Antipatterns: "Infinity ayingomda!", Okanye Incinci malunga nokuphindaphinda