🥇PostgreSQL Antipatterns: “Infinity tsis yog qhov txwv!”, los yog me ntsis txog recursion

Rov ua dua - lub tshuab muaj zog thiab yooj yim heev yog tias tib yam "qhov tob" ua haujlwm ntawm cov ntaub ntawv cuam tshuam. Tab sis kev tswj tsis tau rov qab yog qhov kev phem uas tuaj yeem ua rau ob qho tib si kev tua tsis kawg txheej txheem, los yog (uas tshwm sim ntau zaus) rau "noj" tag nrho cov cim xeeb muaj.

DBMS hais txog qhov no ua haujlwm ntawm tib lub hauv paus ntsiab lus - "Lawv hais kom kuv khawb, ces kuv khawb". Koj qhov kev thov tuaj yeem ua tsis tau tsuas yog ua rau cov txheej txheem nyob sib ze, tas li noj cov peev txheej processor, tab sis kuj "poob" tag nrho cov ntaub ntawv, "noj" tag nrho cov cim xeeb muaj. tiv thaiv infinite recursion - lub luag haujlwm ntawm tus tsim tawm nws tus kheej.

Hauv PostgreSQL, muaj peev xwm siv cov lus nug recursive ntawm WITH RECURSIVE tshwm sim nyob rau hauv lub sij hawm immemorial ntawm version 8.4, tab sis koj tseem muaj peev xwm tsis tu ncua ntsib tej yam yooj yim "defenseless" thov. Yuav ua li cas tshem koj tus kheej ntawm cov teeb meem ntawm hom no?

Tsis txhob sau cov lus nug recursive

Thiab sau cov tsis-recursive ones. Ua tsaug, Koj K.O.

Qhov tseeb, PostgreSQL muab ntau yam haujlwm uas koj tuaj yeem siv tau tsis siv recursion.

Siv ib txoj hauv kev sib txawv rau qhov teeb meem

Qee lub sij hawm koj tuaj yeem saib qhov teeb meem ntawm "sab txawv". Kuv tau muab piv txwv txog qhov xwm txheej zoo li no hauv tsab xov xwm "SQL HowTo: 1000 thiab ib txoj kev sib sau ua ke" - Kev sib npaug ntawm cov lej tsis tas siv cov kev cai sib sau ua ke:

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;

Qhov kev thov no tuaj yeem hloov nrog kev xaiv los ntawm cov kws paub txog lej:

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

Siv generate_series es tsis txhob loops

Cia peb hais tias peb tau ntsib nrog txoj haujlwm ntawm kev tsim txhua qhov ua tau ua ntej rau txoj hlua '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;

Koj puas paub tseeb tias koj xav tau recursion ntawm no?.. Yog koj siv LATERAL и generate_seriesTom qab ntawd koj yuav tsis xav tau CTE:

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

Hloov cov qauv database

Piv txwv li, koj muaj lub rooj sib tham ntawm cov lus nrog kev sib txuas los ntawm leej twg teb rau leej twg, lossis xov hauv social network:

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

Zoo, ib qho kev thov kom rub tawm tag nrho cov lus ntawm ib lub ncauj lus zoo li no:

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;

Tab sis txij li thaum peb ib txwm xav tau tag nrho cov ncauj lus los ntawm cov lus hauv paus, yog vim li cas peb tsis ua ntxiv nws tus ID rau txhua qhov nkag tsis siv neeg?

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

Tam sim no peb tag nrho cov lus nug recursive tuaj yeem raug txo kom tsuas yog qhov no:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Siv cov "limiters"

Yog tias peb tsis tuaj yeem hloov cov qauv ntawm cov ntaub ntawv rau qee qhov laj thawj, cia saib seb peb tuaj yeem tso siab li cas thiaj li txawm tias muaj qhov yuam kev hauv cov ntaub ntawv tsis ua rau muaj kev rov ua tsis tiav.

Recursion qhov tob txee

Peb tsuas yog nce lub txee los ntawm ib qho ntawm txhua kauj ruam rov qab mus txog thaum peb mus txog qhov txwv uas peb xav tias tsis txaus:

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

Pro: Thaum peb sim voj, peb tseem yuav ua tsis tau ntau tshaj li qhov kev txwv ntawm iterations "hauv qhov tob".
cons: Tsis muaj kev lees paub tias peb yuav tsis ua cov ntaub ntawv qub dua - piv txwv li, ntawm qhov tob ntawm 15 thiab 25, thiab tom qab ntawd txhua txhua +10. Thiab tsis muaj leej twg tau cog lus dab tsi txog "ntev".

Raws li txoj cai, xws li kev rov ua dua yuav tsis muaj qhov kawg, tab sis yog tias ntawm txhua kauj ruam tus lej ntawm cov ntaub ntawv nce exponentially, peb txhua tus paub zoo tias nws xaus li cas ...

saib "Qhov teeb meem ntawm nplej ntawm chessboard"

Tus saib xyuas ntawm "path"

Peb hloov pauv ntxiv tag nrho cov khoom txheeb xyuas peb tau ntsib raws txoj kev rov ua dua rau hauv ib qho array, uas yog qhov tshwj xeeb "txoj kev" rau nws:

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

Pro: Yog hais tias muaj ib lub voj voog nyob rau hauv cov ntaub ntawv, peb kiag li yuav tsis ua tib cov ntaub ntawv dua nyob rau hauv tib txoj kev.
cons: Tab sis tib lub sijhawm, peb tuaj yeem hla dhau tag nrho cov ntaub ntawv yam tsis tau rov ua dua peb tus kheej.

saib "Knight's Move Problem"

Txoj Kev Length txwv

Txhawm rau zam qhov xwm txheej ntawm kev rov ua dua "mus ncig" ntawm qhov tsis nkag siab qhov tob, peb tuaj yeem muab ob txoj hauv kev dhau los. Los yog, yog tias peb tsis xav txhawb cov teb tsis tsim nyog, ntxiv cov xwm txheej rau kev rov ua dua nrog kev kwv yees ntawm txoj kev ntev:

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
)

Xaiv ib txoj hauv kev rau koj saj!

Tau qhov twg los: www.hab.com

PostgreSQL Antipatterns: "Infinity tsis yog qhov txwv!", los yog me ntsis txog recursion