پوسٹگری ایس کیو ایل اینٹی پیٹرنز: "انفینٹی حد نہیں ہے!"، یا تکرار کے بارے میں تھوڑا سا

تکرار - ایک بہت ہی طاقتور اور آسان طریقہ کار اگر متعلقہ ڈیٹا پر وہی "گہرائی سے" کارروائیاں کی جائیں۔ لیکن بے قابو تکرار ایک برائی ہے جو دونوں میں سے کسی ایک کا باعث بن سکتی ہے۔ لامتناہی عملدرآمد عمل، یا (جو اکثر ہوتا ہے) کے لیے تمام دستیاب میموری کو "کھانا".

اس سلسلے میں ڈی بی ایم ایس انہی اصولوں پر کام کرتا ہے۔انہوں نے مجھے کھودنے کو کہا تو میں کھودتا ہوں۔آپ کی درخواست نہ صرف پڑوسیوں کے عمل کو سست کر سکتی ہے، مسلسل پروسیسر کے وسائل اٹھاتی ہے، بلکہ پورے ڈیٹا بیس کو "ڈراپ" کر سکتی ہے، تمام دستیاب میموری کو "کھانا" بھی سکتی ہے۔ لامحدود تکرار کے خلاف تحفظ - ڈویلپر کی ذمہ داری خود۔

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;

لوپس کے بجائے generate_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