পোস্টগ্রেএসকিউএল অ্যান্টিপ্যাটার্নস: "ইনফিনিটি সীমা নয়!", বা পুনরাবৃত্তি সম্পর্কে একটু

পুনরাবৃত্তি - একটি খুব শক্তিশালী এবং সুবিধাজনক প্রক্রিয়া যদি একই "গভীরভাবে" ক্রিয়াগুলি সম্পর্কিত ডেটাতে সঞ্চালিত হয়। কিন্তু অনিয়ন্ত্রিত পুনরাবৃত্তি একটি মন্দ যা উভয়ের দিকেই নিয়ে যেতে পারে অন্তহীন মৃত্যুদন্ড প্রক্রিয়া, বা (যা প্রায়শই ঘটে) থেকে "খাওয়া" সমস্ত উপলব্ধ মেমরি.

এই বিষয়ে ডিবিএমএস একই নীতিতে কাজ করে - "তারা আমাকে খনন করতে বলেছিল, তাই আমি খনন করি"। আপনার অনুরোধ শুধুমাত্র প্রতিবেশী প্রক্রিয়াগুলিকে ধীর করে দিতে পারে না, ক্রমাগত প্রসেসরের সংস্থানগুলি গ্রহণ করতে পারে, তবে সমগ্র ডাটাবেসকে "ড্রপ" করতে পারে, সমস্ত উপলব্ধ মেমরি "খাচ্ছে"। অসীম পুনরাবৃত্তির বিরুদ্ধে সুরক্ষা - বিকাশকারী নিজেই দায়িত্ব।

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