🥇PostgreSQL ضد نمونې: "انفینیت حد نه دی!"، یا د تکرار په اړه لږ څه

تکرار - یو خورا پیاوړی او مناسب میکانیزم که ورته "ژور" کړنې په اړوندو معلوماتو کې ترسره شي. مګر بې کنټروله تکرار یو بد دی چې کیدی شي د دې لامل شي نه ختمیدونکی اعدام پروسه، یا (کوم چې ډیر ځله پیښیږي). "خوراک" ټول موجود حافظه.

په دې برخه کې DBMS په ورته اصولو کار کوي - "دوی ما ته وویل چې کیندل، نو زه کیندمستاسو غوښتنه نه یوازې د ګاونډیو پروسې سست کولی شي، په دوامداره توګه د پروسیسر سرچینې اخلي، بلکې ټول ډیټابیس "پرېږدي"، ټولې موجودې حافظې "خوري". د لامحدود تکرار پروړاندې محافظت - پخپله د پراختیا کونکي مسؤلیت.

په PostgreSQL کې، له لارې د تکراري پوښتنو کارولو وړتیا WITH RECURSIVE د 8.4 نسخه پخوانی وخت کې راڅرګند شوی، مګر تاسو لاهم کولی شئ په منظمه توګه د احتمالي زیان منونکي "بې دفاع" غوښتنو سره مخ شئ. څنګه د دې ډول ستونزو څخه ځان خلاص کړئ؟

تکراري پوښتنې مه لیکئ

او غیر تکراري لیکې. په درنښت، ستاسو 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;

مګر څنګه چې موږ تل د ریښې پیغام څخه ټوله موضوع ته اړتیا لرو، نو ولې موږ نه خپل ID هر ننوتلو ته اضافه کړئ اتومات؟

-- добавим поле с общим идентификатором темы и индекс на него
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 ضد نمونې: "انفینیت حد نه دی!"، یا د تکرار په اړه لږ څه