PostgreSQL Antipatterns: "اللانهاية ليست هي الحد!" ، أو قليلاً عن العودية

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





يعمل نظام إدارة قواعد البيانات (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;

استخدم create_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، ثم ليست هناك حاجة حتى CTEs:

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
)

اختر الطريقة التي تناسب ذوقك!