ProHoster > blog > Amministrazzjoni > PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoni

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoni

Rikorsjoni - mekkaniżmu b'saħħtu u konvenjenti ħafna jekk l-istess azzjonijiet "fil-fond" jitwettqu fuq data relatata. Iżda rikorsi mhux ikkontrollat ​​huwa ħażen li jista 'jwassal għal xi waħda eżekuzzjoni bla tarf proċess, jew (li jiġri aktar spiss) biex "tiekol" il-memorja kollha disponibbli.

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoni
DBMS f'dan ir-rigward jaħdmu fuq l-istess prinċipji - "Qaluli biex ħaffer, allura ħaffer". It-talba tiegħek tista 'mhux biss tnaqqas il-veloċità tal-proċessi ġirien, kontinwament tieħu r-riżorsi tal-proċessur, iżda wkoll "waqqa" id-database kollha, "tiekol" il-memorja kollha disponibbli. Għalhekk protezzjoni kontra rikorsi infinita - ir-responsabbiltà tal-iżviluppatur innifsu.

F'PostgreSQL, il-ħila li tuża mistoqsijiet rikorsivi permezz WITH RECURSIVE deher fiż-żmien antiki tal-verżjoni 8.4, iżda xorta tista' regolarment tiltaqa' ma' mistoqsijiet "mingħajr difiża" potenzjalment vulnerabbli. Kif teħles minn problemi ta 'dan it-tip?

Tiktebx mistoqsijiet rikorsivi

U ikteb dawk mhux rikursivi. Sinċerament, K.O.

Fil-fatt, PostgreSQL jipprovdi pjuttost ħafna ta 'funzjonalità li tista' tuża ebda japplikaw rikorsi.

Uża approċċ fundamentalment differenti għall-problema

Xi drabi tista 'sempliċement tħares lejn il-problema minn "naħa differenti". Tajt eżempju ta’ sitwazzjoni bħal din fl-artiklu "SQL HowTo: 1000 u mod wieħed ta' aggregazzjoni" — il-multiplikazzjoni ta’ sett ta’ numri mingħajr l-użu ta’ funzjonijiet aggregati tad-dwana:

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;

Din it-talba tista' tiġi sostitwita b'għażla minn esperti fil-matematika:

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

Uża generate_series minflok loops

Ejja ngħidu li qed niffaċċjaw il-kompitu li niġġeneraw il-prefissi kollha possibbli għal string '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;

Int żgur li għandek bżonn rikorsi hawn?.. Jekk tuża LATERAL и generate_series, allura lanqas ikollok bżonn CTE:

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

Ibdel l-istruttura tad-database

Pereżempju, għandek tabella ta' messaġġi fil-forum b'konnessjonijiet minn min wieġeb lil min, jew ħajta netwerk soċjali:

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

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoni
Ukoll, talba tipika biex tniżżel il-messaġġi kollha fuq suġġett wieħed tidher xi ħaġa bħal din:

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;

Imma peress li dejjem għandna bżonn is-suġġett kollu mill-messaġġ għerq, allura għaliex m'għandniex żid l-ID tagħha ma' kull entrata awtomatiku?

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

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoni
Issa l-mistoqsija rikorsiva kollha tagħna tista' titnaqqas għal dan biss:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Uża "limitaturi" applikati

Jekk ma nkunux nistgħu nbiddlu l-istruttura tad-database għal xi raġuni, ejja naraw fuq xiex nistgħu nistrieħu sabiex anke l-preżenza ta 'żball fid-dejta ma twassalx għal rikorsi bla tmiem.

Kontatur tal-fond tar-rikorsjoni

Aħna sempliċement inżidu l-counter b'wieħed f'kull pass ta' rikorsi sakemm nilħqu limitu li nqisu ovvjament inadegwat:

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

Pro: Meta nippruvaw nagħmlu loop, xorta se nagħmlu mhux aktar mil-limitu speċifikat ta 'iterazzjonijiet "fil-fond".
cons: M'hemm l-ebda garanzija li mhux se nipproċessaw l-istess rekord mill-ġdid - pereżempju, f'fond ta '15 u 25, u mbagħad kull +10. U ħadd ma wiegħed xejn dwar "wisa '".

Formalment, rikorsi bħal dan mhux se jkun infinit, imma jekk f’kull pass in-numru ta’ rekords jiżdied b’mod esponenzjali, ilkoll nafu sew kif tispiċċa...

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoniara “Il-problema tal-ħbub fuq bord taċ-ċess”

Gwardjan tal-"mogħdija"

Aħna nżidu alternattivament l-identifikaturi tal-oġġetti kollha li ltqajna magħhom tul il-mogħdija tar-rikorsi f'firxa, li hija "mogħdija" unika għaliha:

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

Pro: Jekk ikun hemm ċiklu fid-dejta, aħna assolutament mhux se nipproċessaw l-istess rekord ripetutament fl-istess triq.
cons: Iżda fl-istess ħin, nistgħu litteralment naqbdu r-rekords kollha mingħajr ma nirrepetu lilna nfusna.

PostgreSQL Antipatterns: "L-infinità mhix il-limitu!", jew Ftit dwar ir-rikorżjoniara "Problema tal-Move tal-Kavallieri"

Limitu tat-Tul tal-Path

Biex tiġi evitata s-sitwazzjoni ta 'rikursjoni "wandering" f'fond inkomprensibbli, nistgħu ngħaqqdu ż-żewġ metodi preċedenti. Jew, jekk ma rridux nappoġġjaw oqsma mhux meħtieġa, issupplimenta l-kundizzjoni biex titkompla r-rikors b'stima tat-tul tal-passaġġ:

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
)

Agħżel metodu għall-gosti tiegħek!

Sors: www.habr.com

Żid kumment