🥇PostgreSQL Antipatterns: “Infinity is not the limit!”، يا ٿورڙو recursion بابت

ورجائي - هڪ تمام طاقتور ۽ آسان ميکانيزم جيڪڏهن ساڳيو "گہرا" ڪارناما لاڳاپيل ڊيٽا تي ڪيا وڃن. پر اڻڄاتل ورهاڱي هڪ برائي آهي جيڪا ڪنهن به طرف وٺي سگهي ٿي لامحدود عملدرآمد عمل، يا (جيڪو گهڻو ڪري ٿئي ٿو). "کاڌو" سڀ موجود ياداشت.

ان سلسلي ۾ ڊي بي ايم ايس ساڳئي اصولن تي ڪم ڪري ٿو - "هنن مون کي کوٽڻ لاءِ چيو، پوءِ مان کوههتوهان جي درخواست نه رڳو پاڙيسري عملن کي سست ڪري سگهي ٿي، مسلسل پروسيسر وسيلن کي کڻڻ، پر پڻ "ڊراپ" سڄي ڊيٽابيس، "کاڌو" سڀني موجود يادداشتن کي. لامحدود تکرار جي خلاف تحفظ - ڊولپر جي ذميواري پاڻ کي.

PostgreSQL ۾، استعمال ڪرڻ جي صلاحيت ذريعي ورجائيندڙ سوالن کي WITH RECURSIVE نسخو 8.4 جي قديم زماني ۾ ظاهر ٿيو، پر توهان اڃا تائين باقاعدي طور تي ممڪن طور تي خطرناڪ "بي دفاع" درخواستن کي منهن ڏئي سگهو ٿا. ڪيئن پاڻ کي اهڙي قسم جي مسئلن کان نجات حاصل ڪرڻ لاء؟

بار بار سوالن کي نه لکو

۽ لکو غير recursive. مخلص، توهان جي K.O.

حقيقت ۾، PostgreSQL ڪافي ڪارڪردگي مهيا ڪري ٿي جيڪا توهان استعمال ڪري سگهو ٿا نه ٻيهر لاڳو ڪريو.

مسئلو لاء بنيادي طور تي مختلف طريقا استعمال ڪريو

ڪڏهن ڪڏهن توهان صرف "مختلف طرف" کان مسئلي کي ڏسي سگهو ٿا. مون مضمون ۾ اهڙي صورتحال جو مثال ڏنو "SQL ڪيئن ڪجي: 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

PostgreSQL Antipatterns: “Infinity is not the limit!”، يا ٿورڙو recursion بابت