🥇PostgreSQLren aurkako ereduak: “Infinitatea ez da muga!”, edo Errekurtsioari buruz apur bat

Errekurtsioa - Oso mekanismo indartsua eta erosoa erlazionatutako datuetan ekintza “sakon” berdinak egiten badira. Baina kontrolatu gabeko errekurtsioa bietara eraman dezakeen gaitza da exekuzio amaigabea prozesua, edo (gehiago gertatzen dena) to eskuragarri dagoen memoria guztia "jaten"..

Zentzu honetan DBMSk printzipio berdinetan lan egiten du - "Zulatzeko esan zidaten, beraz, zulatzen dut". Zure eskaerak aldameneko prozesuak moteltzeaz gain, prozesadorearen baliabideak etengabe hartuz, datu-base osoa ere "jaregin" dezake, eskuragarri dagoen memoria guztia "jaten". errekurtsio infinituaren aurkako babesa - Garatzailearen beraren erantzukizuna.

PostgreSQL-n, kontsulta errekurtsiboak erabiltzeko gaitasuna WITH RECURSIVE 8.4 bertsioaren antzina-antzina agertu zen, baina oraindik aldian-aldian zaurgarri izan daitezkeen "defentsarik gabeko" kontsultak topa ditzakezu. Nola libratu mota honetako arazoetatik?

Ez idatzi kontsulta errekurtsiboak

Eta idatzi ez-errekurtsiboak. Adeitasunez, Zure K.O.

Izan ere, PostgreSQL-k erabil ditzakezun funtzionalitate asko eskaintzen ditu ez errekurtsioa aplikatu.

Erabili arazoaren ikuspegi oso desberdina

Batzuetan arazoa "alde ezberdinetik" begiratu dezakezu. Horrelako egoera baten adibide bat jarri nuen artikuluan "SQL HowTo: 1000 eta agregazio modu bat" — Zenbaki multzo bat biderkatzea, agregazio-funtzio pertsonalizatuak erabili gabe:

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;

Eskaera hau matematika adituen aukera batekin ordezkatu daiteke:

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

Erabili generate_series begizten ordez

Demagun kate baterako aurrizki posible guztiak sortzeko zereginaren aurrean gaudela '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;

Ziur hemen errekurtsioa behar duzula?... Erabiltzen baduzu LATERAL и generate_series, orduan ez duzu CTE ere beharko:

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

Datu-basearen egitura aldatu

Adibidez, foroko mezuen taula bat duzu, nork erantzun dion nori edo hari bat sare soziala:

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

Beno, gai bateko mezu guztiak deskargatzeko eskaera arrunt batek honelako itxura du:

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;

Baina beti erroko mezutik gai osoa behar dugunez, zergatik ez dugu gehitu bere ID sarrera bakoitzean automatikoa?

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

Orain gure kontsulta errekurtsibo osoa honetara murriztu daiteke:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Erabili aplikatutako "mugatzaileak"

Arrazoiren bategatik datu-basearen egitura aldatu ezin badugu, ikus dezagun zertan fida gaitezkeen, datuetan errore bat egoteak ere errekurtsio amaigabea ekar ez dezan.

Errekurtsio-sakonera-kontagailua

Errekurtsio-urrats bakoitzean kontagailua bat handitzen dugu, jakina desegokia den muga batera iritsi arte:

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

Pro: Begizta egiten saiatzen garenean, oraindik ez dugu "sakonean" zehaztutako iterazioen muga baino gehiago egingo.
Cons: Ez dago bermerik erregistro bera berriro prozesatuko ez dugunik - adibidez, 15 eta 25eko sakoneran, eta gero +10 bakoitzean. Eta inork ez zuen ezer hitzeman “zabaltasunari buruz”.

Formalki, halako errekurtsio bat ez da infinitua izango, baina urrats bakoitzean erregistro kopurua esponentzialki handitzen bada, denok dakigu nola bukatzen den...

ikusi “Aleen arazoa xake taula batean”

"Bidearen" zaindaria

Errekurtsio-bidean zehar aurkitu ditugun objektu-identifikatzaile guztiak txandaka gehitzen ditugu array batean, "bide" bakarra dena:

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

Pro: Datuetan ziklo bat badago, ez dugu erregistro bera behin eta berriz prozesatuko bide berean.
Cons: Baina, aldi berean, literalki erregistro guztiak saihestu ditzakegu geure burua errepikatu gabe.

ikusi "Zaldunen mugimenduaren arazoa"

Bidearen luzera muga

Sakonera ulertezinean errekurtsioaren egoera saihesteko, aurreko bi metodoak konbina ditzakegu. Edo, alferrikako eremuak onartu nahi ez baditugu, osatu errekurtsioarekin jarraitzeko baldintza bidearen luzeraren estimazio batekin:

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
)

Aukeratu zure gustuko metodoa!

Iturria: www.habr.com

PostgreSQL Antipatterns: "Infinitatea ez da muga!", edo Errekurtsioari buruz pixka bat