🥇PostgreSQL Antipatterns: "Infinity sio kikomo!", au Kidogo kuhusu kujirudia

Kujirudia - utaratibu wenye nguvu sana na unaofaa ikiwa vitendo sawa vya "kina" vinafanywa kwenye data inayohusiana. Lakini kujirudia bila kudhibitiwa ni uovu ambao unaweza kusababisha ama utekelezaji usio na mwisho mchakato, au (ambayo hutokea mara nyingi zaidi) kwa "kula" kumbukumbu zote zinazopatikana.

DBMS katika suala hili hufanya kazi kwa kanuni sawa - "Waliniambia nichimbe, nichimbe". Ombi lako haliwezi tu kupunguza kasi ya michakato ya jirani, kuchukua mara kwa mara rasilimali za wasindikaji, lakini pia "kuacha" hifadhidata nzima, "kula" kumbukumbu zote zinazopatikana. ulinzi dhidi ya kujirudia kwa ukomo - jukumu la msanidi mwenyewe.

Katika PostgreSQL, uwezo wa kutumia maswali ya kujirudia kupitia WITH RECURSIVE ilionekana katika kumbukumbu ya toleo la 8.4, lakini bado unaweza kukutana mara kwa mara na maombi ambayo yanaweza kuathiriwa "yasiyo na ulinzi". Jinsi ya kujiondoa matatizo ya aina hii?

Usiandike maswali yanayojirudia

Na andika zisizo za kujirudia. Kwa dhati, K.O.

Kwa kweli, PostgreSQL hutoa utendaji mwingi ambao unaweza kutumia hakuna tumia kujirudia.

Tumia mbinu tofauti kimsingi kwa tatizo

Wakati mwingine unaweza tu kuangalia tatizo kutoka "upande tofauti". Nilitoa mfano wa hali kama hiyo katika makala hiyo "SQL HowTo: 1000 na njia moja ya kujumlisha" - kuzidisha seti ya nambari bila kutumia vitendaji maalum vya jumla:

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;

Ombi hili linaweza kubadilishwa na chaguo kutoka kwa wataalam wa hisabati:

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

Tumia generate_series badala ya vitanzi

Hebu tuseme tunakabiliwa na kazi ya kuzalisha viambishi vyote vinavyowezekana vya mfuatano '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;

Je, una uhakika unahitaji kujirudia hapa?.. Ikiwa unatumia LATERAL и generate_series, basi hautahitaji hata CTE:

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

Badilisha muundo wa hifadhidata

Kwa mfano, una jedwali la jumbe za jukwaa zenye miunganisho kutoka kwa nani alimjibu nani, au mazungumzo ndani mtandao wa kijamii:

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

Kweli, ombi la kawaida la kupakua ujumbe wote kwenye mada moja inaonekana kama hii:

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;

Lakini kwa kuwa sisi daima tunahitaji mada nzima kutoka kwa ujumbe wa mizizi, basi kwa nini hatufanyi ongeza kitambulisho chake kwa kila ingizo moja kwa moja?

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

Sasa swala letu lote la kujirudia linaweza kupunguzwa hadi hivi:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Tumia "vikomo" vilivyotumika

Ikiwa hatuwezi kubadilisha muundo wa hifadhidata kwa sababu fulani, hebu tuone kile tunachoweza kutegemea ili hata uwepo wa hitilafu katika data hauongoi kurudi tena.

Kaunta ya kina cha kujirudia

Tunaongeza tu kaunta moja kwa kila hatua ya kujirudia hadi tufikie kikomo ambacho tunaona kuwa hakitoshi:

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

Pro: Tunapojaribu kuzunguka, bado hatutafanya zaidi ya kikomo maalum cha marudio "kwa kina".
Africa: Hakuna hakikisho kwamba hatutashughulikia rekodi sawa tena - kwa mfano, kwa kina cha 15 na 25, na kisha kila +10. Na hakuna mtu aliyeahidi chochote kuhusu "upana".

Hapo awali, urejeshaji kama huo hautakuwa na mwisho, lakini ikiwa kwa kila hatua idadi ya rekodi inaongezeka kwa kasi, sote tunajua vizuri jinsi inavyoisha ...

tazama "Tatizo la nafaka kwenye ubao wa chess"

Mlinzi wa "njia"

Tunaongeza kwa njia mbadala vitambulishi vyote vya vitu tulivyokutana navyo kwenye njia ya urejeshaji kwenye safu, ambayo ni "njia" ya kipekee kwake:

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

Pro: Ikiwa kuna mzunguko katika data, hatutachakata rekodi sawa mara kwa mara ndani ya njia sawa.
Africa: Lakini wakati huo huo, tunaweza kupita rekodi zote bila kujirudia.

tazama "Tatizo la Knight's Move"

Kikomo cha Urefu wa Njia

Ili kuepuka hali ya kujirudia "tanga" kwa kina kisichoeleweka, tunaweza kuchanganya njia mbili zilizopita. Au, ikiwa hatutaki kuauni sehemu zisizohitajika, ongeza sharti la kuendelea kujirudia kwa makadirio ya urefu wa njia:

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
)

Chagua njia kwa ladha yako!

Chanzo: mapenzi.com

PostgreSQL Antipatterns: "Infinity sio kikomo!", Au kidogo juu ya kujirudia