🥇PostgreSQL Antipatterns: "Tanpa wates ora watesan!", utawa A sethitik babagan rekursi

Rekursi - mekanisme sing kuat lan trep yen tumindak "jero" sing padha ditindakake ing data sing gegandhengan. Nanging rekursi sing ora dikendhaleni minangka piala sing bisa nyebabake eksekusi tanpa wates proses, utawa (sing mengkono luwih asring) kanggo "mangan" kabeh memori kasedhiya.

DBMS ing babagan iki makarya kanthi prinsip sing padha - "Padha didhawuhi ndhudhuk, mula aku ndhudhuk". Panjaluk sampeyan ora mung bisa nyuda proses tetanggan, terus-terusan njupuk sumber daya prosesor, nanging uga "nyelehake" kabeh database, "mangan" kabeh memori sing kasedhiya. pangayoman marang recursion tanpa wates - tanggung jawab pangembang dhewe.

Ing PostgreSQL, kemampuan kanggo nggunakake pitakon rekursif liwat WITH RECURSIVE muncul ing jaman biyen versi 8.4, nanging sampeyan isih bisa nemoni panjalukan "ora duwe pertahanan" sing bisa rawan. Kepiye carane nyingkirake masalah kaya ngono?

Aja nulis pitakon rekursif

Lan nulis non-rekursif. Ikhlas, K.O.

Nyatane, PostgreSQL nyedhiyakake akeh fungsi sing bisa digunakake ora aplikasi rekursi.

Gunakake pendekatan dhasar beda kanggo masalah

Kadhangkala sampeyan mung bisa ndeleng masalah saka "sisih beda". Aku menehi conto kahanan kaya ing artikel kasebut "SQL HowTo: 1000 lan siji cara agregasi" - multiplikasi sakumpulan nomer tanpa nggunakake fungsi agregat 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;

Panjaluk iki bisa diganti karo opsi saka 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;

Gunakake generate_series tinimbang puteran

Contone, kita ngadhepi tugas ngasilake kabeh prefiks sing bisa ditindakake kanggo 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;

Sampeyan manawa sampeyan kudu recursion kene? .. Yen sampeyan nggunakake LATERAL и generate_series, banjur sampeyan ora butuh CTE:

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

Ngganti struktur database

Contone, sampeyan duwe tabel pesen forum karo sambungan saka sing nanggapi kanggo sapa, utawa thread ing jaringan sosial:

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

Ya, panjalukan umum kanggo ngundhuh kabeh pesen ing siji topik katon kaya iki:

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;

Nanging amarga kita tansah mbutuhake kabeh topik saka pesen root, mula kenapa ora nambah ID sawijining kanggo saben 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();

Saiki kabeh pitakon rekursif kita bisa dikurangi dadi mung iki:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Gunakake "limiter" sing ditrapake

Yen kita ora bisa ngganti struktur database sakperangan alesan, ayo kang ndeleng apa kita bisa gumantung ing malah ing ngarsane kesalahan ing data ora mimpin kanggo recursion telas.

Counter kedalaman rekursi

Kita mung nambah counter kanthi siji ing saben langkah rekursi nganti tekan watesan sing dianggep ora cukup:

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

Pro: Nalika kita nyoba kanggo daur ulang, kita isih bakal nindakake ora luwih saka watesan tartamtu saka iterasi "ing ambane".
cons: Ora ana jaminan yen kita ora bakal ngolah rekaman sing padha maneh - contone, ing ambane 15 lan 25, banjur saben +10. Lan ora ana sing janji apa-apa babagan "jembar".

Secara resmi, rekursi kasebut ora bakal tanpa wates, nanging yen ing saben langkah jumlah cathetan mundhak kanthi eksponensial, kita kabeh ngerti kepiye pungkasane ...

deleng "Masalah biji-bijian ing papan catur"

Penjaga "dalan"

Kita gantian nambahake kabeh pengenal obyek sing ditemoni ing sadawane jalur rekursi menyang array, yaiku "path" sing unik:

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

Pro: Yen ana siklus ing data, kita pancen ora bakal ngolah rekaman sing padha bola-bali ing dalan sing padha.
cons: Nanging ing wektu sing padha, kita bisa ngliwati kabeh rekaman tanpa mbaleni dhéwé.

ndeleng "Masalah Pindah Ksatria"

Path Length Limit

Kanggo ngindhari kahanan rekursi "ngumbara" ing kedalaman sing ora bisa dingerteni, kita bisa nggabungake rong cara sadurunge. Utawa, yen kita ora pengin ndhukung kolom sing ora perlu, tambahake syarat kanggo nerusake rekursi kanthi ngira dawane dalan:

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 cara kanggo rasa sampeyan!

Source: www.habr.com

PostgreSQL Antipatterns: "Tanpa wates ora watesan!", utawa A sethitik babagan recursion