🥇 PostgreSQL Antipatterns: "Infinitas non est terminus", vel paulo de recursione

Recursion - validissima et opportuna machina, si actiones "in profundis" in notitia ligata aguntur. Sed immoderata recursus est malum ad utrumque sine fine supplicium processus, seu (quod saepius fit) ad "comedere" omnia available memoria.

DBMS in hoc opere de eisdem principiis — "Dixerunt mihi fodere, sic fodere". Petitio tua non solum processus proximos retardet, semper facultates processus tollens, sed etiam "guttam" datorum totius, "comedere" omnia memoria prompta. praesidium contra infinitum recursus - spectat ad ipsum elit.

In PostgreSQL, facultas utendi recursivos queries via WITH RECURSIVE apparuit in tempore immemorabili versionis 8.4, sed etiamnum regulariter invenire potes interrogationes vulnerabiles "defensores". Quomodo te huiusmodi problematum exuere?

Noli scribere recursive quaero

Et scribe non-recurrentes. SUM VERO K.O.

Re vera, PostgreSQL multum praebet functionality qua uti potes non recursus adhibere.

Utere fundamentaliter alium ad quaestionem

Aliquando iustus videris quaestionem ex parte "diversa". Exemplum dedi talis rei in articulo "SQL HowTo: 1000 et unus modus aggregationis" - Multiplicatio statuti numerorum absque usu functionum aggregati consuetudinis;

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;

Haec petitio reponi potest cum optione a peritis mathematicis:

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

Utere generate_series pro ora sagi alterius

Dicamus nos ante oculos esse negotium generandi omnia praefixa possibilia pro filo '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;

Certus esne hic recursus opus?... Si uteris? LATERAL и generate_seriestunc ne opus quidem CTE;

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

Mutatio database compages

Exempli gratia, tabulam fori epistulas habes cum hospitibus e quibus qui responderint, vel linum in socialis network:

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

Bene, postulatio typica ut omnes nuntios in uno loco accipias, aliquid simile hoc spectat:

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;

Sed cum semper opus sit totum argumentum ab radice nuntius, cur non sumus? addere suum id unicuique ingressum latae sententiae?

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

Nunc tota nostra quaestio recursiva ad hoc modo reduci potest:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Usus applicatus "limiters"

Si structuram datorum aliqua ratione mutare non possumus, videamus quid fidendum sit ut etiam praesentia erroris in notitia non ad infinitam recursionem inducat.

Recursus profundum contra

Peripatetici simpliciter augemus singulos in singulos gradus recursionis, donec perveniamus ad terminum quem manifesto insufficiens consideramus;

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

pro: Cum ansam temptamus, adhuc non plus quam definitum iterationis limitem "in profundum" faciemus.
cons: Nulla cautio est ne iterum idem recordetur processus, verbi gratia, in altitudinem 15 et 25, ac deinde singulae +10. Nec quisquam de latitudine aliquid promisit.

Formaliter talis recursus non erit infinitus, sed si in singulis gradibus numerus monumentorum auget exponentialiter, omnes probe noverunt quomodo finiatur.

vide "Quaestio granorum in LATRUNCULARIA"

Custos semitae

Nos alternatim addimus omne obiectum identificatorium quod per recursus iter in aciem invenimus, quod unicum est ei "via";

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

pro: Si cyclus est in notitiis, absolute non eadem recorda identidem intra eandem viam procedemus.
cons: Sed simul omnia monumenta litteraliter praeterire possumus, quin nosmetipsos repetamus.

vide "Milites Movere Problem"

Longitudo limitis iter

Ad evitandam recursionis condicionem in profunditate incomprehensibili « errantes », duobus modis praecedentibus coniungi possumus. Aut si agros necessarias sustinere non volumus, condicionem recursus cum aestimatione viae longitudinis supplemus:

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
)

Elige modum ad gustum tuum!

Source: www.habr.com

PostgreSQL Antipatterns: "Infinitas non est terminus!", vel pauxillum de recursionibus