Antipatterns PostgreSQL: “Chan e Infinity an ìre as àirde!”, No Beagan mu dheidhinn tilleadh

Ath-chuairteachadh - inneal fìor chumhachdach agus goireasach ma thèid na h-aon ghnìomhan “doimhneachd” a dhèanamh air dàta co-cheangailte. Ach tha ath-chuairteachadh neo-riaghlaichte na olc a dh'fhaodas leantainn gu aon seach aon cur gu bàs gun chrìoch pròiseas, no (a thachras nas trice) gu "ag ithe" a h-uile cuimhne a tha ri fhaighinn.

Bidh DBMS a thaobh seo ag obair air na h-aon phrionnsapalan - "Thuirt iad rium a chladhach, agus mar sin tha mi a 'cladhachChan e a-mhàin gu bheil an t-iarrtas agad comasach air pròiseasan faisg air làimh a dhèanamh nas slaodaiche, a’ cleachdadh goireasan pròiseasar gu cunbhalach, ach cuideachd “leig às” an stòr-dàta gu lèir, “ag ithe” a h-uile cuimhne a tha ri fhaighinn. dìon an aghaidh ath-chuairteachadh gun chrìoch - uallach an leasaiche fhèin.

Ann am PostgreSQL, tha an comas air ceistean ath-chuairteachaidh a chleachdadh tro WITH RECURSIVE nochdadh anns a’ chuimhneachan ùine de dhreach 8.4, ach faodaidh tu fhathast coinneachadh gu cunbhalach ri ceistean “gun dìon” a dh’ fhaodadh a bhith so-leònte. Ciamar a gheibh thu cuidhteas de dhuilgheadasan mar seo?

Na sgrìobh ceistean ath-chuairteach

Agus sgrìobh feadhainn neo-ath-chuairteach. Le meas, tha do K.O.

Gu dearbh, tha PostgreSQL a ’toirt seachad tòrr fheartan as urrainn dhut a chleachdadh chan eil cuir an gnìomh ath-chuairteachadh.

Cleachd dòigh-obrach bunaiteach eadar-dhealaichte airson an duilgheadas

Uaireannan faodaidh tu dìreach coimhead air an duilgheadas bhon "taobh eadar-dhealaichte". Thug mi eisimpleir de leithid de shuidheachadh anns an artaigil "SQL HowTo: 1000 agus aon dòigh air cruinneachadh" - iomadachadh seata àireamhan gun a bhith a’ cleachdadh gnìomhan cruinneachaidh àbhaisteach:

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;

Faodar an t-iarrtas seo a chuir na àite le roghainn bho eòlaichean matamataig:

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

Cleachd Generation_series an àite lùban

Canaidh sinn gu bheil an obair romhainn a bhith a’ gineadh a h-uile ro-leasachan a dh’ fhaodadh a bhith ann airson sreang '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;

A bheil thu cinnteach gu bheil feum agad air ath-chuairteachadh an seo?.. Ma chleachdas tu LATERAL и generate_series, an uairsin cha bhith feum agad air CTE eadhon:

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

Atharraich structar stòr-dàta

Mar eisimpleir, tha clàr de theachdaireachdan fòraim agad le ceanglaichean bhon a fhreagair cò ris, no snàithlean a-steach lìonra sòisealta:

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

Uill, tha iarrtas àbhaisteach airson a h-uile teachdaireachd a luchdachadh sìos air aon chuspair a’ coimhead rudeigin mar seo:

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;

Ach leis gu bheil feum againn an-còmhnaidh air a’ chuspair gu lèir bhon teachdaireachd bhunaiteach, carson nach dèan sinn sin cuir a ID ri gach inntrigeadh fèin-ghluasadach?

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

A-nis faodar ar ceist ath-chuairteach gu lèir a lughdachadh gu dìreach seo:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Cleachd “crìochan” gnìomhaichte

Mura h-urrainn dhuinn structar an stòr-dàta atharrachadh airson adhbhar air choireigin, chì sinn dè as urrainn dhuinn a bhith an urra gus nach bi eadhon mearachd anns an dàta a’ leantainn gu ath-chuairtean gun chrìoch.

Cunntair doimhneachd ath-chuairteachaidh

Bidh sinn dìreach ag àrdachadh a’ chunntair le aon aig gach ceum ath-chuairteachaidh gus an ruig sinn crìoch a tha sinn a’ meas a tha follaiseach mì-fhreagarrach:

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

Pro: Nuair a dh’ fheuchas sinn ri lùb a dhèanamh, cha bhith sinn fhathast a’ dèanamh barrachd air a’ chrìoch ainmichte de dh’ itealain “ann an doimhneachd”.
dona: Chan eil gealltanas sam bith ann nach giullachd sinn an aon chlàr a-rithist - mar eisimpleir, aig doimhneachd 15 agus 25, agus an uairsin a h-uile +10. Agus cha do gheall duine dad mu dheidhinn “leud”.

Gu foirmeil, cha bhith an leithid de thilleadh gun chrìoch, ach ma tha an àireamh de chlàran a’ dol am meud gu h-obann aig gach ceum, tha fios againn uile gu math ciamar a thig e gu crìch...

faic “Trioblaid gràinean air bòrd tàileisg”

Neach-dìon na "slighe"

Air an làimh eile cuiridh sinn a h-uile aithnichear nì ris an do choinnich sinn air an t-slighe ath-chuairteachaidh ann an sreath, a tha na “slighe” gun samhail dha:

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

Pro: Ma tha cearcall anns an dàta, cha bhith sinn gu cinnteach a’ pròiseasadh an aon chlàr a-rithist agus a-rithist san aon shlighe.
dona: Ach aig an aon àm, is urrainn dhuinn gu litearra a dhol seachad air a h-uile clàr gun a bhith ag ath-aithris sinn fhìn.

faic "Trioblaid Gluasad Knight"

Crìochan fad slighe

Gus suidheachadh ath-chuairteachaidh a sheachnadh “a’ siubhal ”aig doimhneachd nach gabh a thuigsinn, is urrainn dhuinn an dà dhòigh a bh’ ann roimhe a chur còmhla. No, mura h-eil sinn airson taic a thoirt do raointean neo-riatanach, cuir ris a’ chumha airson leantainn air adhart leis an ath-chuairt le tuairmse air fad na slighe:

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
)

Tagh dòigh airson do bhlas!

Source: www.habr.com