🥇PostgreSQL Antipatterns: "Infinity si malire!", Kapena Pang'ono za kubwereza

Kubwereza - njira yamphamvu kwambiri komanso yabwino ngati zochita "zakuya" zomwezo zikuchitidwa pa data yokhudzana. Koma kubwereza kosalamulirika ndi vuto lomwe lingayambitse kapena kuphedwa kosatha ndondomeko, kapena (zomwe zimachitika kawirikawiri) ku "kudya" kukumbukira zonse zomwe zilipo.

DBMS pankhaniyi imagwira ntchito pa mfundo zomwezo - "Anandiuza kuti ndikumbe, ndiye ndikukumba"Pempho lanu silingachedwetse njira zoyandikana nazo, kugwiritsa ntchito purosesa nthawi zonse, komanso "kugwetsa" database yonse, "kudya" kukumbukira zonse zomwe zilipo. chitetezo ku kubwereza kosatha - udindo wa wopanga yekha.

Mu PostgreSQL, kuthekera kogwiritsa ntchito mafunso obwereza kudzera WITH RECURSIVE adawonekera kalekale mu mtundu 8.4, koma mutha kukumana ndi mafunso omwe ali pachiwopsezo "osadziteteza". Kodi mungachotse bwanji mavuto amtunduwu?

Osalemba mafunso obwerezabwereza

Ndipo lembani zosabwerezabwereza. Moona mtima, K.O.

M'malo mwake, PostgreSQL imapereka magwiridwe antchito ambiri omwe mungagwiritse ntchito osati gwiritsani ntchito recursion.

Gwiritsani ntchito njira yosiyana kwambiri yothetsera vutoli

Nthawi zina mutha kungoyang'ana vutoli kuchokera ku "mbali yosiyana". Ndinapereka chitsanzo cha mkhalidwe wotero m’nkhaniyo "SQL HowTo: 1000 ndi njira imodzi yophatikizira" - kuchulutsa manambala osagwiritsa ntchito zophatikizira:

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;

Pempholi litha kusinthidwa ndi njira yochokera kwa akatswiri a masamu:

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

Gwiritsani ntchito generate_series m'malo mwa malupu

Tiyerekeze kuti takumana ndi ntchito yopanga ma prefixes onse a chingwe '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;

Mukutsimikiza kuti mukufuna kubwereza apa?.. Ngati mugwiritsa ntchito LATERAL и generate_series, ndiye simudzasowa CTE:

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

Sinthani kapangidwe ka database

Mwachitsanzo, muli ndi tebulo la mauthenga a forum omwe amalumikizana ndi omwe adayankha kwa ndani, kapena ulusi social network:

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

Chabwino, pempho lodziwika bwino lotsitsa mauthenga onse pamutu umodzi limawoneka motere:

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;

Koma popeza nthawi zonse timafunikira mutu wonse kuchokera ku uthenga wa muzu, ndiye bwanji osatero onjezani ID yake pazolemba zilizonse automatic?

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

Tsopano funso lathu lonse lobwerezabwereza litha kuchepetsedwa kukhala motere:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Gwiritsani ntchito "limiters"

Ngati sitingathe kusintha mawonekedwe a database pazifukwa zina, tiyeni tiwone zomwe tingadalire kuti ngakhale kukhalapo kwa zolakwika mu deta sikuyambitsa kubwereza kosatha.

Recursion deep counter

Timangowonjezera kauntala ndi imodzi pa sitepe iliyonse yobwereza mpaka titafika malire omwe timawaona kuti ndi osakwanira:

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

Pro: Tikayesa kuzungulira, sitidzachitanso zochulukirapo kuposa malire omwe adanenedwa "mozama".
kuipa: Palibe chitsimikizo kuti sitidzakonzanso mbiri yomweyo - mwachitsanzo, pakuya kwa 15 ndi 25, ndiyeno +10 iliyonse. Ndipo palibe amene adalonjeza chilichonse chokhudza "kufalikira".

Mwamwayi, kubwereza koteroko sikudzakhala kosatha, koma ngati pa sitepe iliyonse chiwerengero cha zolemba chikuwonjezeka kwambiri, tonse timadziwa bwino momwe zimathera ...

onani "Vuto la mbewu pa chessboard"

Mtetezi wa "njira"

Timawonjezera zizindikiritso zonse zomwe tidakumana nazo panjira yobwereza kukhala mndandanda, womwe ndi "njira" yapadera yopitako:

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

Pro: Ngati pali kuzungulira mu data, sitidzakonza mbiri yomweyo mobwerezabwereza munjira yomweyo.
kuipa: Koma panthawi imodzimodziyo, tikhoza kulambalala zolemba zonse popanda kubwereza tokha.

onani "Knight's Move Problem"

Kutalika kwa Njira

Kuti tipewe vuto la "kungoyendayenda" mozama mosadziwika bwino, titha kuphatikiza njira ziwiri zam'mbuyomu. Kapena, ngati sitikufuna kuthandizira magawo osafunikira, onjezerani mkhalidwe wopitiliza kubwereza ndikuyerekeza kutalika kwa njira:

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
)

Sankhani njira yomwe mumakonda!

Source: www.habr.com

PostgreSQL Antipatterns: "Infinity si malire!", Kapena Pang'ono za kubwereza