🥇PostgreSQL Antipatternləri: “Sonsuzluq həddi deyil!” və ya rekursiya haqqında bir az

Rekursiya - əlaqəli məlumatlar üzərində eyni "dərin" hərəkətlər həyata keçirildiyi təqdirdə çox güclü və rahat mexanizm. Ancaq nəzarətsiz rekursiya hər ikisinə səbəb ola biləcək bir pislikdir sonsuz icra proses və ya (daha tez-tez olur) üçün bütün mövcud yaddaşı "yemək".

Bu baxımdan DBMS eyni prinsiplər üzərində işləyir - "Mənə qazma dedilər, mən də qazıram". Sorğunuz nəinki daim prosessor resurslarını götürərək qonşu prosesləri ləngidə bilər, həm də bütün mövcud yaddaşı "yeyərək" bütün verilənlər bazasını "atır". sonsuz rekursiyadan qorunma - tərtibatçının özünün məsuliyyəti.

PostgreSQL-də rekursiv sorğulardan istifadə etmək imkanı WITH RECURSIVE 8.4 versiyasının qədim zamanlarında ortaya çıxdı, lakin siz hələ də müntəzəm olaraq potensial olaraq həssas “müdafiəsiz” sorğularla qarşılaşa bilərsiniz. Özünüzü bu cür problemlərdən necə xilas etmək olar?

Rekursiv sorğular yazmayın

Və rekursiv olmayanları yazın. Hörmətlə, Sizin K.O.

Əslində, PostgreSQL istifadə edə biləcəyiniz bir çox funksionallıq təmin edir heç bir rekursiya tətbiq edin.

Problemə kökündən fərqli yanaşmadan istifadə edin

Bəzən problemə sadəcə “fərqli tərəfdən” baxa bilərsiniz. Məqalədə belə bir vəziyyətə misal çəkdim "SQL HowTo: 1000 və bir toplama üsulu" — xüsusi məcmu funksiyalardan istifadə etmədən bir sıra ədədlərin vurulması:

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;

Bu sorğu riyaziyyat mütəxəssislərinin seçimi ilə əvəz edilə bilər:

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

Döngələr əvəzinə generator_series istifadə edin

Tutaq ki, bir sətir üçün bütün mümkün prefiksləri yaratmaq vəzifəsi ilə qarşılaşırıq '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;

Burada rekursiyaya ehtiyacınız olduğuna əminsinizmi?.. İstifadə etsəniz LATERAL и generate_series, onda sizə CTE lazım olmayacaq:

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

Verilənlər bazası strukturunu dəyişdirin

Məsələn, kimin kimə cavab verdiyi və ya bir mövzu ilə əlaqəsi olan forum mesajları cədvəliniz var sosial şəbəkə:

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

Yaxşı, bir mövzuda bütün mesajları yükləmək üçün tipik bir sorğu belə görünür:

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;

Ancaq həmişə kök mesajdan bütün mövzuya ehtiyacımız olduğundan, niyə də olmasın hər girişə onun ID-sini əlavə edin avtomatik?

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

İndi bütün rekursiv sorğumuz yalnız buna endirilə bilər:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Tətbiq olunan "məhdudlaşdırıcılardan" istifadə edin

Əgər nədənsə verilənlər bazasının strukturunu dəyişdirə bilmiriksə, gəlin görək nəyə etibar edə bilərik ki, hətta verilənlərdə xətanın olması da sonsuz rekursiyaya səbəb olmasın.

Rekursiya dərinliyi sayğacı

Açıqca qeyri-adekvat hesab etdiyimiz həddə çatana qədər hər rekursiya addımında sayğacı bir artırırıq:

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

Pro: Döngə etməyə çalışdığımız zaman, biz yenə də "dərinlikdə" təkrarlamaların müəyyən edilmiş limitindən çoxunu etməyəcəyik.
Cons: Eyni qeydi yenidən emal etməyəcəyimizə zəmanət yoxdur - məsələn, 15 və 25 dərinlikdə, sonra isə hər +10. Və heç kim "genişlik" haqqında heç nə vəd etmədi.

Formal olaraq belə bir rekursiya sonsuz olmayacaq, lakin hər addımda qeydlərin sayı eksponent olaraq artırsa, bunun necə bitdiyini hamımız yaxşı bilirik...

bax "Şahmat taxtasında taxıl problemi"

"Yolun" keşikçisi

Rekursiya yolu boyunca rastlaşdığımız bütün obyekt identifikatorlarını növbə ilə massivə əlavə edirik ki, bu da ona unikal “yol”dur:

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

Pro: Məlumatda bir dövr varsa, biz eyni qeydi eyni yolda təkrar emal etməyəcəyik.
Cons: Ancaq eyni zamanda, özümüzü təkrarlamadan bütün qeydləri sözün əsl mənasında yan keçə bilərik.

"Cəngavərin Hərəkət Problemi"nə baxın

Yol uzunluğu limiti

Anlaşılmaz bir dərinlikdə rekursiyanın "gəzən" vəziyyətindən qaçmaq üçün əvvəlki iki üsulu birləşdirə bilərik. Və ya lazımsız sahələri dəstəkləmək istəmiriksə, rekursiyanı davam etdirmək şərtini yol uzunluğunun təxmini ilə tamamlayın:

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
)

Zövqünüzə uyğun bir üsul seçin!

Mənbə: www.habr.com

PostgreSQL Antipatterns: “Sonsuzluq həddi deyil!” və ya bir az rekursiya haqqında