PostgreSQL Antipatterns፡ "Infinity ገደብ አይደለም!"፣ ወይም ስለ ተደጋጋሚነት ትንሽ

ምልመላ - በተዛማጅ መረጃዎች ላይ ተመሳሳይ ድርጊቶች "በጥልቀት" ከተከናወኑ በጣም ኃይለኛ እና ምቹ ዘዴ. ነገር ግን ከቁጥጥር ውጭ የሆነ ተደጋጋሚነት ወደ ወይ ሊያመራ የሚችል ክፉ ነገር ነው። ማለቂያ የሌለው ግድያ ሂደት, ወይም (ብዙ ጊዜ የሚከሰት) ወደ ሁሉንም የሚገኙትን ማህደረ ትውስታዎች በመብላት ላይ.

በዚህ ረገድ ዲቢኤምኤስ በተመሳሳይ መርሆዎች ላይ ይሰራል - "ቆፍራለሁ አሉ።". የእርስዎ ጥያቄ የአጎራባች ሂደቶችን ማቀዝቀዝ, ያለማቋረጥ ፕሮሰሰር ሃብቶችን መውሰድ ብቻ ሳይሆን, ሁሉንም የሚገኙትን ማህደረ ትውስታዎች "መብላት" በአጠቃላይ የውሂብ ጎታውን "መጣል" ይችላል. ስለዚህ. ማለቂያ የሌለው ድግግሞሽ ጥበቃ የገንቢው ሃላፊነት ነው.

በ PostgreSQL፣ ተደጋጋሚ መጠይቆችን በመጠቀም የመጠቀም ችሎታ WITH RECURSIVE በጥንታዊው ስሪት 8.4 ታየ፣ ነገር ግን አሁንም በመደበኛነት ተጋላጭ ሊሆኑ የሚችሉ “መከላከያ የሌላቸው” ጥያቄዎችን ማሟላት ይችላሉ። ከእንደዚህ አይነት ችግሮች እራስዎን እንዴት ማዳን ይቻላል?

ተደጋጋሚ መጠይቆችን አይጻፉ

እና የማይደጋገሙ ይፃፉ። ከሠላምታ ጋር፣ የእርስዎ ኬ.ኦ.

በእርግጥ፣ 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;

ከ loops ይልቅ የመነጨ_ተከታታይን ተጠቀም

ለአንድ ሕብረቁምፊ ሁሉንም ሊሆኑ የሚችሉ ቅድመ ቅጥያዎችን የማመንጨት ሥራ ገጥሞናል እንበል '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ከዚያ CTEs እንኳን አያስፈልጉም፡-

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
)

በሚወዱት መንገድ ይምረጡ!

ምንጭ: hab.com