🥇PostgreSQL Antipatterns: "Teu aya watesna teu wates!", atawa A saeutik ngeunaan recursion

Rekursi - mékanisme anu pohara kuat sareng merenah upami tindakan "jero" anu sami dilakukeun dina data anu aya hubunganana. Tapi recursion uncontrolled mangrupa jahat nu bisa ngakibatkeun boh palaksanaan sajajalan prosés, atawa (anu lumangsung leuwih mindeng) ka "dahar" sagala memori sadia.

DBMS dina hal ieu dianggo dina prinsip anu sami - "Aranjeunna nitah kuring ngagali, jadi kuring ngagali". Paménta anjeun henteu ngan ukur tiasa ngalambatkeun prosés tatangga, terus-terusan nyéépkeun sumber daya prosésor, tapi ogé "ngaleupaskeun" sadayana pangkalan data, "dahar" sadaya mémori anu sayogi. panyalindungan ngalawan recursion taya - tanggung jawab pamekar sorangan.

Dina PostgreSQL, kamampuh ngagunakeun queries recursive via WITH RECURSIVE mucunghul dina waktos immemorial tina versi 8.4, tapi anjeun masih bisa rutin sapatemon poténsi rentan "defenseless" requests. Kumaha carana ngabebaskeun diri tina masalah sapertos kitu?

Ulah nulis queries rekursif

Jeung nulis non-recursive. Hormat, K.O Anjeun.

Nyatana, PostgreSQL nyayogikeun seueur fungsionalitas anu anjeun tiasa dianggo teu nerapkeun recursion.

Paké pendekatan fundamentally béda pikeun masalah

Kadang-kadang anjeun ngan ukur tiasa ningali masalah tina "sisi anu béda". Kuring masihan conto kaayaan sapertos dina tulisan "SQL HowTo: 1000 sareng hiji cara agrégasi" - ngalikeun sakumpulan nomer tanpa nganggo fungsi agrégat khusus:

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;

Paménta ieu tiasa diganti ku pilihan ti para ahli 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;

Paké generate_series tinimbang loop

Anggap urang disanghareupan ku tugas pikeun ngahasilkeun sadaya awalan anu mungkin pikeun senar '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;

Anjeun pasti kana nu peryogi recursion dieu? .. Lamun make LATERAL и generate_series, teras anjeun moal peryogi CTE:

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

Robah struktur database

Contona, anjeun gaduh tabel seratan forum sareng sambungan ti anu ngabales ka saha, atawa thread di jaringan sosial:

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

Nya, pamundut umum pikeun ngaunduh sadaya pesen dina hiji topik sapertos kieu:

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;

Tapi saprak urang salawasna butuh sakabéh topik ti pesen root, lajeng naha urang henteu tambahkeun ID na ka unggal entri otomatis?

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

Ayeuna sakabéh pamundut rekursif urang bisa diréduksi jadi ngan ieu:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Anggo "limiter" anu diterapkeun

Upami urang henteu tiasa ngarobih struktur pangkalan data pikeun sababaraha alesan, hayu urang tingali naon anu urang tiasa diandelkeun ku kituna ayana kasalahan dina data henteu ngakibatkeun rekursi anu teu aya tungtungna.

counter jero recursion

Urang ngan saukur ningkatkeun counter ku hiji di unggal hambalan recursion dugi kami ngahontal wates nu anggap we écés inadequate:

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

pro: Nalika urang nyobian loop, urang masih bakal ngalakukeun teu leuwih ti wates dieusian of iteration "jero".
kontra: Teu aya jaminan yén urang moal ngolah catetan anu sami deui - contona, dina jerona 15 sareng 25, teras unggal +10. Jeung teu saurang ogé jangji nanaon ngeunaan "lebar".

Sacara resmi, rekursi sapertos kitu moal aya watesna, tapi upami dina unggal léngkah jumlah rékaman ningkat sacara éksponénsial, urang sadayana terang kumaha tungtungna ...

tingali "Masalah bijirin dina papan catur"

Penjaga "jalan"

Urang gantian nambahkeun sagala identifiers objék kami encountered sapanjang jalur recursion kana Asép Sunandar Sunarya, nu mangrupakeun unik "jalur" pikeun eta:

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

pro: Upami aya siklus dina data, urang leres-leres moal ngolah catetan anu sami sababaraha kali dina jalur anu sami.
kontra: Tapi dina waktos anu sami, urang sacara harfiah tiasa ngalangkungan sadaya rékaman tanpa ngulang deui.

tingali "Masalah Ksatria Pindahkeun"

Wates Panjang Jalur

Pikeun ngahindarkeun kaayaan rekursi "ngumbara" dina jero anu teu kaharti, urang tiasa ngagabungkeun dua metode sateuacana. Atanapi, upami urang henteu hoyong ngadukung widang anu teu perlu, tambahkeun kaayaan pikeun neraskeun rekursi kalayan perkiraan panjang jalur:

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
)

Pilih metodeu pikeun rasa anjeun!

sumber: www.habr.com

PostgreSQL Antipatterns: "Teu aya watesna teu wates!", atawa A saeutik ngeunaan recursion