أنماط PostgreSQL المضادة: "إنفينيتي ليست الحد الأقصى!"، أو القليل عن العودية

العودية - آلية قوية ومريحة للغاية إذا تم تنفيذ نفس الإجراءات "المتعمقة" على البيانات ذات الصلة. لكن التكرار غير المنضبط هو شر يمكن أن يؤدي إلى أي منهما تنفيذ لا نهاية له العملية، أو (وهو ما يحدث في كثير من الأحيان) ل "الأكل" كل الذاكرة المتاحة.

يعمل نظام إدارة قواعد البيانات في هذا الصدد على نفس المبادئ - "قالوا لي أن أحفر، لذلك أحفر". لا يمكن أن يؤدي طلبك إلى إبطاء العمليات المجاورة فحسب، بل يستهلك موارد المعالج باستمرار، ولكن أيضًا "إسقاط" قاعدة البيانات بأكملها، و"التهام" كل الذاكرة المتوفرة. لذلك الحماية ضد العودية اللانهائية - مسؤولية المطور نفسه.

في 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;

استخدم generator_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