🥇PostgreSQL Antipatterns: "Infinity haisiriyo muganhu!", Kana zvishoma nezve kudzokorora

Recursion - ine simba kwazvo uye iri nyore michina kana zvakafanana "zvakadzama" zviito zvikaitwa pane inoenderana data. Asi kudzokorora kusingadzoreki chinhu chakaipa chinogona kutungamirira kune chero kusingaperi kuurayiwa process, kana (zvinoitika kakawanda) ku "kudya" zvese zviripo ndangariro.

DBMS munyaya iyi inoshanda pamitemo yakafanana - "Vakanditi ndichere, saka ndinochera". Chikumbiro chako hachingodzikisi maitiro evavakidzani, kugara uchitora processor zviwanikwa, asiwo "kudonhedza" database yese, "kudya" zvese zviripo ndangariro. Naizvozvo dziviriro kubva pakudzokororwa kusingaperi - basa remugadziri pachake.

MuPostgreSQL, kugona kushandisa mibvunzo inodzokororwa kuburikidza WITH RECURSIVE yakaonekwa munguva yekare yevhezheni 8.4, asi iwe unogona kugara uchisangana nemivhunzo "isina dziviriro" ine njodzi. Nzira yekubvisa sei matambudziko emhando iyi?

Usanyore mibvunzo inodzokororwa

Uye nyora zvisiri zvekudzokorora. Nemoyo wese, K.O.

Muchokwadi, PostgreSQL inopa kwakawanda kushanda kwaunogona kushandisa kwete shandisa recursion.

Shandisa imwe nzira yakasiyana kune dambudziko

Dzimwe nguva unogona kungotarisa dambudziko kubva "kudivi rakasiyana". Ndakapa muenzaniso wemamiriro ezvinhu akadaro munyaya yacho "SQL HowTo: 1000 uye imwe nzira yekuunganidza" - kuwanda kweseti yenhamba pasina kushandisa tsika yakaunganidzwa mabasa:

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;

Ichi chikumbiro chinogona kutsiviwa nesarudzo kubva kunyanzvi dzemasvomhu:

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

Shandisa generate_series pachinzvimbo chezvishwe

Ngatitii takatarisana nebasa rekugadzira zvese zvinokwanisika prefixes yetambo '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;

Une chokwadi chekuti unoda kudzokororwa pano?.. Kana ukashandisa LATERAL и generate_series, saka hauzotomboda CTE:

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

Shandura dhizaini chimiro

Semuenzaniso, iwe une tafura yeforum meseji ine hukama kubva kuna ani akapindura, kana tambo mukati pasocial network:

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

Zvakanaka, chikumbiro chakajairika chekudhawunirodha mameseji ese pane imwe nyaya inotaridzika seizvi:

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;

Asi sezvo isu tichigara tichida iyo nyaya yese kubva kumudzi meseji, saka nei isu tisingadi wedzera ID yayo kune yega yega otomatiki?

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

Iye zvino mubvunzo wedu wese unodzokororwa unogona kudzikiswa kuita izvi chete:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Shandisa yakashandiswa "limiters"

Kana isu tisingakwanisi kushandura chimiro chedhatabhesi nekuda kwechimwe chikonzero, ngationei zvatingavimba nazvo kuitira kuti kunyangwe kuvepo kwekukanganisa mune data kusingatungamirire kusingaperi kudzokorora.

Recursion kudzika counter

Isu tinongowedzera kaunda neimwe pane imwe neimwe nhanho yekudzokorodza kudzamara tasvika pamuganho watinoona sekuti hauna kukwana:

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

Pro: Kana isu tikayedza kuruka, isu hatingaite zvinopfuura zvakatarwa muganho wekudzokorodza "zvakadzama".
nezvayakaipira: Iko hakuna vimbiso yekuti isu hatizogadzirise rekodhi imwechete zvakare - semuenzaniso, pakadzika kwe15 ne25, uyezve yega +10. Uye hapana akavimbisa chero chinhu nezve "hupamhi".

Pakare, kudzokororwa kwakadaro hakuzove kusingagumi, asi kana padanho rega rega nhamba yemarekodhi ichiwedzera zvakanyanya, isu tese tinoziva zvakanaka kuti inopera sei ...

ona "Dambudziko rezviyo pane chessboard"

Muchengeti we "nzira"

Isu tinowedzera kuwedzera zvese zvinongedzo zvechinhu zvatakasangana nazvo munzira yekudzokorodza muhurongwa, inova yakasarudzika "nzira" kwairi:

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

Pro: Kana paine kutenderera mune data, isu hatizogadzirise rekodhi imwechete kakawanda mukati menzira imwechete.
nezvayakaipira: Asi panguva imwe cheteyo, tinogona kunzvenga zvinyorwa zvese pasina kudzokorora isu pachedu.

ona "Knight's Move Problem"

Path Length Limit

Kuti udzivise mamiriro ekudzokorora "kudzungaira" pakudzika kusinganzwisisike, tinogona kusanganisa nzira mbiri dzakapfuura. Kana, kana isu tisingadi kutsigira minda isingakoshi, wedzera mamiriro ekuenderera mberi nekudzokororwa nefungidziro yehurefu hwenzira:

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
)

Sarudza nzira yekuda kwako!

Source: www.habr.com

PostgreSQL Antipatterns: "Infinity haisiriyo muganho!", Kana Zvishoma nezve kudzokorora