PostgreSQL Antipatterns: "Infinity is net de limyt!", Of In bytsje oer rekursje

Rekursy - in heul krêftich en handich meganisme as deselde "djipte" aksjes wurde útfierd op relatearre gegevens. Mar ûnkontrollearre rekursje is in kwea dat kin liede ta beide einleaze útfiering proses, of (wat faker bart) oan "eating" alle beskikbere ûnthâld.

DBMS wurket yn dit ferbân op deselde prinsipes - "Se seine my te graven, dus ik grave". Jo oanfraach kin net allinnich fertrage oanbuorjende prosessen, hieltyd opnimme prosessor middels, mar ek "drop" de hiele databank, "iten" alle beskikbere ûnthâld. Dêrom beskerming tsjin ûneinige rekursje - de ferantwurdlikens fan 'e ûntwikkelder sels.

Yn PostgreSQL is de mooglikheid om rekursive queries te brûken fia WITH RECURSIVE ferskynde yn de âlde tiid fan ferzje 8.4, mar jo kinne noch geregeld tsjinkomme potinsjeel kwetsbere "ferdigeningleaze" queries. Hoe kinne jo josels kwytreitsje fan dit soarte problemen?

Skriuw gjin rekursive fragen

En skriuw net-rekursive. Mei freonlike groetnis, Jo K.O.

Yn feite biedt PostgreSQL nochal in soad funksjonaliteit wêrmei jo kinne brûke net tapasse rekursje.

Brûk in fûneminteel oare oanpak fan it probleem

Soms kinne jo gewoan sjen nei it probleem fan 'e "oare kant". Ik joech in foarbyld fan sa'n situaasje yn it artikel "SQL HowTo: 1000 en ien manier fan aggregaasje" - fermannichfâldigje fan in set nûmers sûnder gebrûk fan oanpaste aggregaatfunksjes:

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;

Dit fersyk kin wurde ferfongen troch in opsje fan wiskundige saakkundigen:

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

Brûk generearje_series ynstee fan loops

Litte wy sizze dat wy te krijen hawwe mei de taak om alle mooglike foarheaksels foar in tekenrige te generearjen '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;

Binne jo wis dat jo nedich rekursion hjir? .. As jo ​​brûke LATERAL и generate_series, dan sille jo net iens CTE nedich hawwe:

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

Feroarje databank struktuer

Jo hawwe bygelyks in tabel mei foarumberjochten mei ferbiningen fan wa't op wa hat reagearre, of in thread yn sosjaal netwurk:

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

No, in typysk fersyk om alle berjochten oer ien ûnderwerp te downloaden sjocht der sa ú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;

Mar om't wy altyd it hiele ûnderwerp fan it rootberjocht nedich binne, wêrom dogge wy dat dan net foegje syn ID ta oan elke yngong automatysk?

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

No kin ús hiele rekursive query wurde fermindere ta krekt dit:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Brûk tapaste "beheiners"

As wy om ien of oare reden de struktuer fan 'e databank net kinne feroarje, litte wy sjen wêr't wy op kinne fertrouwe, sadat sels de oanwêzigens fan in flater yn' e gegevens net liedt ta einleaze rekursje.

Recursion djipte teller

Wy ferheegje de teller gewoan mei ien by elke rekursjestap oant wy in limyt berikke dy't wy fansels net genôch fine:

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

pro: As wy besykje te loopen, sille wy noch net mear dwaan as de opjûne limyt fan iteraasjes "yn djipte".
Cons: D'r is gjin garânsje dat wy itselde rekord net wer ferwurkje - bygelyks op in djipte fan 15 en 25, en dan elke +10. En nimmen tasein wat oer "breedte".

Formeel sil sa'n rekursje net ûneinich wêze, mar as by elke stap it oantal records eksponentieel ferheget, witte wy allegear goed hoe't it einiget ...

sjoch "It probleem fan korrels op in skaakboerd"

Hoeder fan it "paad"

Wy foegje ôfwikseljend alle objektidentifikatoren ta dy't wy tsjinkamen lâns it rekursjepaad yn in array, dat is in unyk "paad" nei it:

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

pro: As d'r in syklus is yn 'e gegevens, sille wy perfoarst itselde rekôr net meardere kearen ferwurkje binnen itselde paad.
Cons: Mar tagelyk kinne wy ​​alle records letterlik omgean sûnder ússels te herheljen.

sjoch "Knight's Move Problem"

Paad Length Limit

Om foar te kommen dat de situaasje fan rekursion "swalkjen" op in ûnbegryplike djipte, kinne wy ​​kombinearje de twa foarige metoaden. Of, as wy gjin ûnnedige fjilden stypje wolle, oanfolje de betingst foar it fuortsetten fan de rekursje mei in skatting fan 'e paadlingte:

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
)

Kies in metoade nei jo smaak!

Boarne: www.habr.com