PostgreSQL ആന്റിപാറ്റേണുകൾ: "അനന്തത ഒരു പരിധിയല്ല!", അല്ലെങ്കിൽ ആവർത്തനത്തെക്കുറിച്ച് അൽപ്പം

ആവർത്തനം - ബന്ധപ്പെട്ട ഡാറ്റയിൽ ഒരേ "ആഴത്തിലുള്ള" പ്രവർത്തനങ്ങൾ നടത്തുകയാണെങ്കിൽ വളരെ ശക്തവും സൗകര്യപ്രദവുമായ ഒരു സംവിധാനം. എന്നാൽ അനിയന്ത്രിതമായ ആവർത്തനം ഒരു തിന്മയാണ്, അത് ഒന്നിലേക്ക് നയിച്ചേക്കാം അനന്തമായ വധശിക്ഷ പ്രോസസ്സ്, അല്ലെങ്കിൽ (കൂടുതൽ സംഭവിക്കുന്നത്). ലഭ്യമായ എല്ലാ മെമ്മറിയും "കഴിക്കുന്നു".

ഇക്കാര്യത്തിൽ ഡിബിഎംഎസ് ഒരേ തത്ത്വങ്ങളിൽ പ്രവർത്തിക്കുന്നു - "അവർ എന്നോട് കുഴിക്കാൻ പറഞ്ഞു, അതിനാൽ ഞാൻ കുഴിക്കുന്നു". നിങ്ങളുടെ അഭ്യർത്ഥന അയൽ പ്രക്രിയകളെ മന്ദഗതിയിലാക്കാൻ മാത്രമല്ല, പ്രോസസർ ഉറവിടങ്ങൾ നിരന്തരം ഏറ്റെടുക്കാനും മാത്രമല്ല, മുഴുവൻ ഡാറ്റാബേസും "ഡ്രോപ്പ്" ചെയ്യാനും, ലഭ്യമായ എല്ലാ മെമ്മറിയും "തിന്നാനും" കഴിയും. അനന്തമായ ആവർത്തനത്തിനെതിരായ സംരക്ഷണം - ഡവലപ്പറുടെ തന്നെ ഉത്തരവാദിത്തം.

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;

ലൂപ്പുകൾക്ക് പകരം ജനറേറ്റ്_സീരീസ് ഉപയോഗിക്കുക

ഒരു സ്‌ട്രിങ്ങിന് സാധ്യമായ എല്ലാ പ്രിഫിക്‌സുകളും സൃഷ്‌ടിക്കാനുള്ള ചുമതലയാണ് ഞങ്ങൾ നേരിടുന്നതെന്ന് പറയാം '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