🥇PostgreSQL Antipatterns: "අනන්තය යනු සීමාව නොවේ!", හෝ පුනරාවර්තනය ගැන ටිකක්

පුනරාවර්තනය - අදාළ දත්ත මත එකම "ගැඹුරු" ක්රියාවන් සිදු කරන්නේ නම් ඉතා බලවත් සහ පහසු යාන්ත්රණයක්. නමුත් පාලනයකින් තොරව පුනරාවර්තනය යනු එක්කෝ හේතු විය හැකි නපුරකි නිමක් නැති ක්රියාත්මක කිරීම ක්රියාවලිය, හෝ (බොහෝ විට සිදු වන) වෙත පවතින සියලුම මතකය "කනවා".

මේ සම්බන්ධයෙන් 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;

ලූප වෙනුවට ජනන_ශ්‍රේණි භාවිතා කරන්න

තන්තුවක් සඳහා හැකි සියලුම උපසර්ග ජනනය කිරීමේ කාර්යයට අප මුහුණ දී ඇතැයි කියමු '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 -- предел
)

Pro: අපි ලූප් කිරීමට උත්සාහ කරන විට, අපි තවමත් "ගැඹුරු" පුනරාවර්තන සීමාවට වඩා වැඩි දෙයක් නොකරනු ඇත.
අවාසි: අපි එම වාර්තාවම නැවත සකස් නොකරන බවට සහතිකයක් නොමැත - උදාහරණයක් ලෙස, 15 සහ 25 ගැඹුරකදී, පසුව සෑම +10 ක්ම. තවද "පළල" ගැන කිසිවෙකු කිසිවක් පොරොන්දු වූයේ නැත.

විධිමත් ලෙස, එවැනි පුනරාවර්තනයක් අසීමිත නොවනු ඇත, නමුත් සෑම පියවරකදීම වාර්තා ගණන ඝාතීය ලෙස වැඩි වුවහොත්, එය අවසන් වන්නේ කෙසේදැයි අපි කවුරුත් හොඳින් දනිමු.

"චෙස් පුවරුවක ධාන්ය පිළිබඳ ගැටළුව" බලන්න

"මාර්ගයේ" ආරක්ෂකයා

ප්‍රත්‍යාවර්තන මාර්ගය ඔස්සේ අපට හමු වූ සියලුම වස්තු හඳුනාගැනීම් අපි විකල්ප ලෙස අරාවකට එකතු කරමු, එය එයට අද්විතීය “මාර්ගයක්” වේ:

WITH RECURSIVE T AS (
  SELECT
    ARRAY[id] path
  ...
UNION ALL
  SELECT
    path || id
  ...
  WHERE
    id <> ALL(T.path) -- не совпадает ни с одним из
)

Pro: දත්තවල චක්‍රයක් තිබේ නම්, අපි නියත වශයෙන්ම එකම මාර්ගයක් තුළ එකම වාර්තාව නැවත නැවත සකසන්නේ නැත.
අවාසි: නමුත් ඒ සමඟම, අපට නැවත නැවත නොකියා සියලුම වාර්තා වචනාර්ථයෙන් මඟ හැරිය හැක.

"නයිට්ගේ චලනය ගැටලුව" බලන්න

මාර්ග දිග සීමාව

තේරුම්ගත නොහැකි ගැඹුරකින් පුනරාවර්තන "ඉබාගාතේ" තත්ත්වය වළක්වා ගැනීම සඳහා, අපට පෙර ක්රම දෙක ඒකාබද්ධ කළ හැකිය. එසේත් නැතිනම්, අපට අනවශ්‍ය ක්ෂේත්‍ර සඳහා සහය දැක්වීමට අවශ්‍ය නැතිනම්, මාර්ගයේ දිග පිළිබඳ ඇස්තමේන්තුවක් සමඟ ප්‍රත්‍යාවර්තනය දිගටම කරගෙන යාම සඳහා කොන්දේසිය අතිරේක කරන්න:

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: "අනන්තය යනු සීමාව නොවේ!", හෝ පුනරාවර්තනය ගැන ටිකක්