🥇PostgreSQL Antipatterns: “Ailopin kii ṣe opin!”, Tabi Diẹ nipa iṣipopada

Recursion - ẹrọ ti o lagbara pupọ ati irọrun ti awọn iṣe “ijinle” kanna ni a ṣe lori data ti o ni ibatan. Ṣugbọn atunṣe ti ko ni iṣakoso jẹ ibi ti o le ja si boya ailopin ipaniyan ilana, tabi (eyi ti o ṣẹlẹ diẹ igba) lati "njẹ" gbogbo iranti ti o wa.

DBMS ni iyi yii ṣiṣẹ lori awọn ilana kanna - ”Wọ́n ní kí n gbẹ́ ilẹ̀, nítorí náà, mo gbẹ́". Rẹ ìbéèrè ko le nikan fa fifalẹ adugbo lakọkọ, nigbagbogbo mu soke isise oro, sugbon tun "ju" gbogbo database, "njẹ" gbogbo wa iranti. Nitorina. aabo lodi si ailopin recursion - awọn ojuse ti awọn Olùgbéejáde ara.

Ni PostgreSQL, agbara lati lo awọn ibeere loorekoore nipasẹ WITH RECURSIVE farahan ni igba atijọ ti ẹya 8.4, ṣugbọn o tun le ba pade nigbagbogbo awọn ibeere “aini aabo” ti o ni ipalara. Bawo ni lati yọ ara rẹ kuro ninu iru awọn iṣoro yii?

Maṣe kọ awọn ibeere loorekoore

Ki o si kọ ti kii-recursive. Tọkàntọkàn, K.O.

Ni otitọ, PostgreSQL n pese iṣẹ ṣiṣe pupọ ti o le lo lati kii ṣe waye recursion.

Lo ọna ipilẹ ti o yatọ si iṣoro naa

Nigba miiran o le kan wo iṣoro naa lati “ẹgbẹ oriṣiriṣi”. Mo fun apẹẹrẹ ti iru ipo kan ninu nkan naa "SQL HowTo: 1000 ati ọna kan ti apapọ" - isodipupo ti awọn nọmba kan laisi lilo awọn iṣẹ akojọpọ aṣa:

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;

Ibeere yii le paarọ rẹ pẹlu aṣayan lati ọdọ awọn amoye mathematiki:

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

Lo gene_series dipo awọn yipo

Jẹ ká sọ pé a ti wa ni dojuko pẹlu awọn iṣẹ-ṣiṣe ti a npese gbogbo awọn ti ṣee prefixes fun a okun '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;

Ṣe o da ọ loju pe o nilo atunwi nibi?.. Ti o ba lo LATERAL и generate_series, lẹhinna iwọ kii yoo paapaa nilo CTE:

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

Yi database be

Fun apẹẹrẹ, o ni tabili awọn ifiranṣẹ apejọ kan pẹlu awọn asopọ lati ọdọ ẹniti o dahun si tani, tabi o tẹle ara ninu awujo nẹtiwọki:

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

O dara, ibeere aṣoju lati ṣe igbasilẹ gbogbo awọn ifiranṣẹ lori koko-ọrọ kan dabi iru eyi:

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;

Ṣugbọn niwọn igba ti a nilo gbogbo koko-ọrọ nigbagbogbo lati ifiranṣẹ gbongbo, lẹhinna kilode ti a ko ṣe fi awọn oniwe-ID si kọọkan titẹsi laifọwọyi?

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

Bayi gbogbo ibeere wa ti o le dinku si eyi nikan:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Lo "awọn ifilelẹ" ti a lo

Ti a ko ba le yi ọna ipilẹ data pada fun idi kan, jẹ ki a wo ohun ti a le gbẹkẹle ki paapaa wiwa aṣiṣe ninu data ko ni ja si atunṣe ailopin.

Recursion ijinle counter

A nìkan mu counter naa pọ si ni ẹyọkan ni igbesẹ atunkọ kọọkan titi ti a yoo fi de opin ti a ro pe ko pe:

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

Pro: Nigba ti a ba gbiyanju lati lupu, a yoo tun ṣe ko si siwaju sii ju awọn pàtó kan iye to ti iterations "ni ijinle".
konsi: Ko si iṣeduro pe a kii yoo tun ṣe igbasilẹ igbasilẹ kanna lẹẹkansi - fun apẹẹrẹ, ni ijinle 15 ati 25, ati lẹhinna ni gbogbo +10. Ati pe ko si ẹnikan ti o ṣe ileri ohunkohun nipa “iwọn”.

Ni deede, iru isọdọtun kii yoo jẹ ailopin, ṣugbọn ti o ba jẹ pe ni igbesẹ kọọkan nọmba awọn igbasilẹ pọ si lọpọlọpọ, gbogbo wa mọ daradara bi o ṣe pari…

wo "Iṣoro ti awọn irugbin lori chessboard"

Oluṣọ ti "ọna"

Ni omiiran, a ṣafikun gbogbo awọn idamọ ohun ti a ba pade ni ọna ipadabọ sinu opo kan, eyiti o jẹ “ọna” alailẹgbẹ si rẹ:

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

Pro: Ti ọmọ ba wa ninu data, a ko ni ṣe ilana igbasilẹ kanna leralera laarin ọna kanna.
konsi: Ṣugbọn ni akoko kanna, a le ṣe itumọ ọrọ gangan gbogbo awọn igbasilẹ laisi atunwi ara wa.

wo "Isoro Gbe Knight"

Ifilelẹ Ipari Ọna

Lati yago fun ipo ti iṣipopada "irin kiri" ni ijinle ti ko ni oye, a le darapọ awọn ọna meji ti tẹlẹ. Tabi, ti a ko ba fẹ lati ṣe atilẹyin fun awọn aaye ti ko wulo, ṣe afikun ipo fun lilọsiwaju atunṣe pẹlu iṣiro ti ipari ọna:

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
)

Yan ọna kan si itọwo rẹ!

orisun: www.habr.com

PostgreSQL Antipatterns: “Ailopin kii ṣe opin!”, Tabi Diẹ nipa iṣipopada