Antipatterns PostgreSQL: "Infinity حد نیست!" یا کمی در مورد بازگشت

بازگشت - یک مکانیسم بسیار قدرتمند و راحت در صورتی که همان اقدامات "عمق" روی داده های مرتبط انجام شود. اما بازگشت کنترل نشده شری است که می تواند منجر به هر دو شود اعدام بی پایان فرآیند، یا (که بیشتر اتفاق می افتد) به "خوردن" تمام حافظه موجود.

DBMS در این زمینه بر اساس همان اصول کار می کند - "به من گفتند حفاری کن، پس من می کنم". درخواست شما نه تنها می تواند فرآیندهای همسایه را کند کند، دائماً منابع پردازنده را اشغال می کند، بلکه کل پایگاه داده را "رها" می کند و تمام حافظه موجود را "می خورد". بنابراین محافظت در برابر بازگشت بی نهایت - مسئولیت خود توسعه دهنده است.

در PostgreSQL، امکان استفاده از پرس و جوهای بازگشتی از طریق WITH RECURSIVE در زمان های بسیار قدیم نسخه 8.4 ظاهر شد، اما همچنان می توانید مرتباً با درخواست های بالقوه آسیب پذیر "بی دفاع" روبرو شوید. چگونه خود را از شر این گونه مشکلات خلاص کنیم؟

پرس و جوهای بازگشتی ننویسید

و غیر بازگشتی بنویسید. با احترام، شما K.O.

در واقع، PostgreSQL عملکردهای بسیار زیادی را ارائه می دهد که می توانید از آنها استفاده کنید هیچ اعمال بازگشت

از یک رویکرد اساسی متفاوت برای مشکل استفاده کنید

گاهی اوقات می توانید فقط از "جهت متفاوت" به مشکل نگاه کنید. من نمونه ای از چنین وضعیتی را در مقاله آوردم "SQL HowTo: 1000 و یک راه تجمیع" - ضرب مجموعه ای از اعداد بدون استفاده از توابع جمع سفارشی:

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;

این درخواست را می توان با گزینه ای از کارشناسان ریاضی جایگزین کرد:

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

به جای حلقه ها از gener_series استفاده کنید

فرض کنید ما با وظیفه تولید تمام پیشوندهای ممکن برای یک رشته روبرو هستیم '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;

آیا مطمئن هستید که در اینجا نیاز به بازگشت دارید؟.. اگر استفاده می کنید LATERAL и generate_series، پس حتی به CTE هم نیاز نخواهید داشت:

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

تغییر ساختار پایگاه داده

به عنوان مثال، شما یک جدول از پیام های انجمن با ارتباط از طرف چه کسی به چه کسی پاسخ داده است، یا یک رشته در شبکه اجتماعی:

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

خوب، یک درخواست معمولی برای دانلود همه پیام ها در یک موضوع چیزی شبیه به این است:

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;

اما از آنجایی که ما همیشه به کل موضوع از پیام اصلی نیاز داریم، پس چرا به آن نیاز نداریم شناسه آن را به هر ورودی اضافه کنید خودکار؟

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

اکنون کل پرس و جو بازگشتی ما را می توان به این کاهش داد:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

استفاده از "محدود کننده" اعمال شده

اگر بنا به دلایلی نتوانستیم ساختار پایگاه داده را تغییر دهیم، بیایید ببینیم به چه چیزی می توانیم تکیه کنیم تا حتی وجود یک خطا در داده ها منجر به بازگشت بی پایان نشود.

شمارشگر عمق بازگشتی

ما به سادگی شمارنده را در هر مرحله بازگشتی یک بار افزایش می دهیم تا زمانی که به حدی برسیم که آشکارا ناکافی در نظر می گیریم:

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

نرم افزار: وقتی سعی می کنیم حلقه بزنیم، باز هم بیشتر از حد مشخص شده تکرارها "در عمق" انجام نمی دهیم.
منفی: هیچ تضمینی وجود ندارد که دوباره همان رکورد را پردازش نکنیم - به عنوان مثال، در عمق 15 و 25، و سپس هر +10. و هیچ کس چیزی در مورد "عرض" قول نداد.

به طور رسمی، چنین بازگشتی بی نهایت نخواهد بود، اما اگر در هر مرحله تعداد رکوردها به صورت تصاعدی افزایش یابد، همه ما به خوبی می دانیم که چگونه به پایان می رسد ...

"مشکل دانه ها روی صفحه شطرنج" را ببینید

نگهبان "مسیر"

ما به طور متناوب تمام شناسه‌های شی را که در طول مسیر بازگشت با آنها مواجه می‌شویم به یک آرایه اضافه می‌کنیم، که یک "مسیر" منحصر به فرد برای آن است:

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

نرم افزار: اگر چرخه ای در داده ها وجود داشته باشد، ما مطلقاً همان رکورد را به طور مکرر در همان مسیر پردازش نمی کنیم.
منفی: اما در عین حال، می توانیم به معنای واقعی کلمه همه رکوردها را بدون تکرار خود دور بزنیم.

"مشکل حرکت شوالیه" را ببینید

محدودیت طول مسیر

برای جلوگیری از وضعیت بازگشت "سرگردان" در عمق نامفهوم، ما می توانیم دو روش قبلی را ترکیب کنیم. یا اگر نمی‌خواهیم از فیلدهای غیرضروری پشتیبانی کنیم، شرط ادامه بازگشت را با تخمین طول مسیر تکمیل کنید:

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
)

روشی را به سلیقه خود انتخاب کنید!

منبع: www.habr.com