🥇PostgreSQL Antipatterns: "Tsy fetra ny tsy manam-petra!", na Somary momba ny famerenana

Recursion - mekanika tena mahery vaika sy mety raha toa ny hetsika "lalina" mitovy amin'ny angon-drakitra mifandraika. Fa ny recursion tsy voafehy dia ratsy izay mety hitarika ho amin'ny iray ihany famonoana tsy misy farany dingana, na (izay mitranga matetika kokoa) amin'ny "mihinana" ny fahatsiarovana rehetra misy.

Ny DBMS amin'io lafiny io dia miasa amin'ny fitsipika mitovy - "Nolazain'izy ireo aho fa mihady, ka izaho no nihady". Ny fangatahanao dia tsy vitan'ny hoe mampiadana ny fizotran'ny mpifanolo-bodirindrina, maka tsy tapaka ny loharanon'ny processeur, fa koa "manary" ny tahiry manontolo, "mihinana" ny fitadidiana rehetra misy. fiarovana amin'ny fiverenana tsy manam-petra - ny andraikitry ny mpamorona ny tenany.

Ao amin'ny PostgreSQL, ny fahafahana mampiasa fanontaniana miverimberina amin'ny alàlan'ny WITH RECURSIVE Nipoitra tamin'ny vanim-potoana taloha tamin'ny dikan-teny 8.4, saingy mbola afaka mifanena matetika amin'ny fangatahana "tsy misy fiarovana" mety ho marefo ianao. Ahoana no hialana amin'ny olana toy izany?

Aza manoratra fanontaniana miverimberina

Ary soraty ireo tsy recursive. Misaotra anao K.O.

Raha ny marina, ny PostgreSQL dia manome fiasa be dia be azonao ampiasaina tsy mampihatra recursion.

Mampiasà fomba fijery hafa amin'ny olana

Indraindray ianao dia afaka mijery fotsiny ny olana amin'ny "lafy samihafa". Nomeko ohatra ny amin’ny toe-javatra toy izany tao amin’ilay lahatsoratra "SQL HowTo: 1000 ary fomba iray fanangonana" - fampitomboana isa maromaro nefa tsy mampiasa fiasa mitambatra mahazatra:

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;

Ity fangatahana ity dia azo soloina amin'ny safidy avy amin'ny manampahaizana 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;

Ampiasao genera_series fa tsy loops

Andeha atao hoe miatrika ny asa famoronana prefix rehetra mety ho an'ny tady '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;

Tena mila recursion ve ianao eto?.. Raha mampiasa ianao LATERAL и generate_series, dia tsy mila CTE akory ianao:

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

Hanova ny rafitra angona

Ohatra, manana tabilao misy hafatra forum misy fifandraisana avy amin'izay namaly an'iza ianao, na kofehy iray Tambazotram-pifandraisana:

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

Eny, toy izao ny fangatahana fampidinana ny hafatra rehetra amin'ny lohahevitra iray:

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;

Saingy satria mila ny lohahevitra iray manontolo avy amin'ny hafatra fototra isika, nahoana no tsy ampio ny ID isaky ny fidirana mandeha ho azy?

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

Amin'izao fotoana izao dia azo ahena ho toy izao fotsiny ny fangatahanay miverimberina:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Mampiasà "limiter" ampiharina

Raha tsy afaka manova ny firafitry ny angon-drakitra isika noho ny antony iray, dia andeha hojerentsika izay azontsika ianteherana ka na ny fisian'ny hadisoana amin'ny angon-drakitra aza dia tsy mitondra mankany amin'ny famerenana tsy misy farany.

Recursion depth counter

Ampitomboinay iray fotsiny ny kaontera isaky ny dingana miverimberina mandra-pahatonganay amin'ny fetra izay heverinay fa tsy ampy:

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

Pro: Rehefa manandrana mametaka isika dia mbola tsy hanao mihoatra ny fetra voatondro amin'ny famerimberenana "amin'ny halaliny".
maharatsy ny mifampiresaka: Tsy misy antoka fa tsy hamerina ny rakitra mitovy izahay - ohatra, amin'ny halalin'ny 15 sy 25, ary avy eo isaky ny +10. Ary tsy nisy nampanantena na inona na inona momba ny "sakany".

Amin'ny fomba ofisialy, ny fiverenana toy izany dia tsy ho tsy manam-petra, fa raha isaky ny dingana dia mitombo ny isan'ny rakitra, dia fantatsika tsara ny fiafaran'izany ...

jereo ny “Ny olan'ny voamaina eo amin'ny solaitrabe”

Mpiambina ny "lalana"

Ampifandraisinay amin'ny laha-tahiry ny famantarana ny zavatra hitanay teny amin'ny lalan'ny recursion, izay "lalana" tokana ho azy:

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

Pro: Raha misy tsingerina ao amin'ny angon-drakitra, dia tsy hanao ny firaketana an-tsoratra miverimberina ao anatin'ny lalana iray ihany izahay.
maharatsy ny mifampiresaka: Saingy miaraka amin'izay koa, afaka mandingana ara-bakiteny ny rakitsoratra rehetra isika nefa tsy mamerina ny tenantsika.

jereo ny "Olan'ny Hetsika Knight"

Famerana ny halavan'ny lalana

Mba hisorohana ny toe-javatra "mirenireny" amin'ny halalin'ny tsy takatry ny saina, dia afaka manambatra ireo fomba roa teo aloha isika. Na, raha tsy te hanohana saha tsy ilaina izahay, ampio ny fepetra hanohizana ny fiverenana amin'ny tombantombana ny halavan'ny lalana:

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
)

Mifidiana fomba mifanaraka amin'ny tsironao!

Source: www.habr.com

Antipatterns PostgreSQL: "Tsy voafetra ny tsy manam-petra!", Na kely momba ny famerenana