🥇PostgreSQL Antipatterns: “Infinity is not the limit!”, eða Smá um endurkomu

endurkomu - mjög öflugt og þægilegt kerfi ef sömu „ídýptar“ aðgerðir eru gerðar á tengdum gögnum. En stjórnlaus endurkoma er illt sem getur leitt til hvors tveggja endalaus framkvæmd ferli, eða (sem gerist oftar) til „borða“ allt tiltækt minni.

DBMS í þessu sambandi vinnur eftir sömu meginreglum - "Þeir sögðu mér að grafa, svo ég grafa". Beiðni þín getur ekki aðeins hægt á nálægum ferlum, stöðugt tekið upp örgjörvaauðlindir, heldur einnig "sleppt" allan gagnagrunninn, "borðað" allt tiltækt minni. Þess vegna vörn gegn óendanlega endurkomu - ábyrgð framkvæmdaraðila sjálfs.

Í PostgreSQL, hæfileikinn til að nota endurkvæmar fyrirspurnir í gegnum WITH RECURSIVE birtist í örófi alda útgáfu 8.4, en þú getur samt reglulega rekist á hugsanlega viðkvæmar „varnarlausar“ beiðnir. Hvernig á að losa þig við vandamál af þessu tagi?

Ekki skrifa endurkvæmar fyrirspurnir

Og skrifaðu óendurkvæmar. Með kveðju, K.O.

Reyndar býður PostgreSQL upp á töluvert af virkni sem þú getur notað til ekki beita endurkomu.

Notaðu í grundvallaratriðum mismunandi nálgun á vandamálið

Stundum er bara hægt að horfa á vandamálið frá „öðrum hlið“. Ég tók dæmi um slíkt í greininni "SQL HowTo: 1000 og ein leið til að safna saman" — margföldun talnamengis án þess að nota sérsniðnar uppsöfnunaraðgerðir:

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;

Þessari beiðni er hægt að skipta út fyrir valkost frá stærðfræðisérfræðingum:

WITH src AS (
  SELECT unnest('{2,3,5,7,11,13,17,19}'::integer[]) prime
)
SELECT
  exp(sum(ln(prime)))::integer val
FROM
  src;

Notaðu gener_series í staðinn fyrir lykkjur

Segjum að við stöndum frammi fyrir því verkefni að búa til öll möguleg forskeyti fyrir streng '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;

Ertu viss um að þú þurfir endurtekningu hér?.. Ef þú notar LATERAL и generate_series, þá þarftu ekki einu sinni CTE:

SELECT
  substr(str, 1, ln) str
FROM
  (VALUES('abcdefgh')) T(str)
, LATERAL(
    SELECT generate_series(length(str), 1, -1) ln
  ) X;

Breyta uppbyggingu gagnagrunns

Til dæmis, þú ert með töflu yfir spjallskilaboð með tengingum frá þeim sem svaraði hverjum, eða þráð inn samfélagsmiðill:

CREATE TABLE message(
  message_id
    uuid
      PRIMARY KEY
, reply_to
    uuid
      REFERENCES message
, body
    text
);
CREATE INDEX ON message(reply_to);

Jæja, dæmigerð beiðni um að hlaða niður öllum skilaboðum um eitt efni lítur einhvern veginn svona út:

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;

En þar sem við þurfum alltaf allt efnið frá rótarskilaboðunum, af hverju gerum við það þá ekki bæta auðkenni sínu við hverja færslu sjálfvirkur?

-- добавим поле с общим идентификатором темы и индекс на него
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();

Nú er hægt að minnka alla endurkvæma fyrirspurnina okkar í þetta:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Notaðu notaða „takmarkara“

Ef við getum ekki breytt uppbyggingu gagnagrunnsins af einhverjum ástæðum skulum við sjá á hverju við getum treyst svo að jafnvel tilvist villu í gögnunum leiði ekki til endalausrar endurtekningar.

Endurkvæmni dýpt teljari

Við hækkum teljarann einfaldlega um einn við hvert endurkomuskref þar til við náum mörkum sem við teljum augljóslega ófullnægjandi:

WITH RECURSIVE T AS (
  SELECT
    0 i
  ...
UNION ALL
  SELECT
    i + 1
  ...
  WHERE
    T.i < 64 -- предел
)

Pro: Þegar við reynum að lykkja, gerum við samt ekki meira en tilgreind takmörk endurtekningar „í dýpt“.
gallar: Það er engin trygging fyrir því að við munum ekki vinna úr sömu skránni aftur - til dæmis á 15 og 25 dýpi og síðan á +10 fresti. Og enginn lofaði neinu um "breidd".

Formlega verður slík endurkoma ekki óendanleg, en ef við hvert skref fjölgar færslum með veldisvísi, vitum við öll vel hvernig hún endar...

sjá „Vandamál korns á skákborði“

Verndari "leiðarinnar"

Við bætum til skiptis öllum hlutaauðkennum sem við hittum á endurtekningarleiðinni í fylki, sem er einstök „slóð“ til hennar:

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

Pro: Ef það er hringrás í gögnunum munum við algerlega ekki vinna úr sömu skránni ítrekað á sömu slóðinni.
gallar: En á sama tíma getum við bókstaflega farið framhjá öllum metum án þess að endurtaka okkur.

sjá "Knight's Move Problem"

Vegalengdartakmörk

Til að koma í veg fyrir að endurkoma "ráfaði" á óskiljanlegu dýpi, getum við sameinað tvær fyrri aðferðirnar. Eða, ef við viljum ekki styðja óþarfa reiti, bætið við skilyrðinu fyrir áframhaldandi endurkomu með mati á leiðarlengd:

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
)

Veldu aðferð að þínum smekk!

Heimild: www.habr.com

PostgreSQL Antipatterns: "Infinity is not the limit!", Eða smá um endurkomu