Antipatterns PostgreSQL: “Nid anfeidredd yw’r terfyn!”, neu Ychydig am ddychwelyd

Dychweliad - mecanwaith pwerus a chyfleus iawn os cyflawnir yr un gweithredoedd “manwl” ar ddata cysylltiedig. Ond mae dychwelyd heb ei reoli yn ddrwg a all arwain at y naill neu'r llall dienyddiad diddiwedd broses, neu (sy'n digwydd yn amlach) i "bwyta" pob cof sydd ar gael.

Mae DBMS yn hyn o beth yn gweithio ar yr un egwyddorion - "Fe ddywedon nhw wrtha i am gloddio, felly dwi'n cloddio". Gall eich cais nid yn unig arafu prosesau cyfagos, gan ddefnyddio adnoddau prosesydd yn gyson, ond hefyd "gollwng" y gronfa ddata gyfan, "bwyta" yr holl gof sydd ar gael. Felly amddiffyniad rhag dychweliad anfeidrol - cyfrifoldeb y datblygwr ei hun.

Yn PostgreSQL, y gallu i ddefnyddio ymholiadau ailadroddus trwy WITH RECURSIVE ymddangos yn y cyfnod cyn cof o fersiwn 8.4, ond gallwch ddod ar draws ymholiadau “diamddiffyn” a allai fod yn agored i niwed yn rheolaidd. Sut i gael gwared ar broblemau o'r fath?

Peidiwch ag ysgrifennu ymholiadau ailadroddus

Ac ysgrifennu rhai nad ydynt yn ailadroddus. Yn gywir, Eich K.O.

Mewn gwirionedd, mae PostgreSQL yn darparu cryn dipyn o ymarferoldeb y gallwch ei ddefnyddio dim cymhwyso recursion.

Defnyddiwch ymagwedd sylfaenol wahanol i'r broblem

Weithiau gallwch chi edrych ar y broblem o'r “ochr wahanol”. Rhoddais enghraifft o sefyllfa o'r fath yn yr erthygl "SQL HowTo: 1000 ac un ffordd o agregu" — lluosi set o rifau heb ddefnyddio swyddogaethau agregau wedi'u teilwra:

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;

Gall y cais hwn gael ei ddisodli gan opsiwn gan arbenigwyr mathemateg:

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

Defnyddiwch Generation_series yn lle dolenni

Gadewch i ni ddweud ein bod yn wynebu'r dasg o gynhyrchu pob rhagddodiad posibl ar gyfer llinyn '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;

Ydych chi'n siŵr eich bod angen dychwelyd yma?.. Os ydych yn defnyddio LATERAL и generate_series, yna ni fydd angen CTE arnoch hyd yn oed:

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

Newid strwythur cronfa ddata

Er enghraifft, mae gennych dabl o negeseuon fforwm gyda chysylltiadau gan bwy a ymatebodd i bwy, neu edefyn i mewn rhwydwaith cymdeithasol:

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

Wel, mae cais nodweddiadol i lawrlwytho pob neges ar un pwnc yn edrych fel hyn:

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;

Ond gan ein bod bob amser angen y pwnc cyfan o'r neges gwraidd, yna pam na wnawn ni ychwanegu ei ID at bob cofnod awtomatig?

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

Nawr gellir lleihau ein hymholiad ailadroddus cyfan i hyn yn unig:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Defnyddio "cyfyngwyr" cymhwysol

Os na allwn newid strwythur y gronfa ddata am ryw reswm, gadewch i ni weld beth y gallwn ddibynnu arno fel nad yw hyd yn oed presenoldeb gwall yn y data yn arwain at ailadrodd diddiwedd.

Cownter dyfnder dychweliad

Yn syml, rydym yn cynyddu’r rhifydd fesul un ym mhob cam dychwelyd nes i ni gyrraedd terfyn sy’n amlwg yn annigonol yn ein barn ni:

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

Pro: Pan geisiwn ddolenni, ni fyddwn yn gwneud mwy na'r terfyn penodedig o iteriadau “mewn dyfnder”.
anfanteision: Nid oes unrhyw sicrwydd na fyddwn yn prosesu'r un cofnod eto - er enghraifft, ar ddyfnder o 15 a 25, ac yna bob +10. A doedd neb yn addo dim am “ehangder”.

Yn ffurfiol, ni fydd dychweliad o'r fath yn ddiddiwedd, ond os bydd nifer y cofnodion yn cynyddu'n esbonyddol ar bob cam, rydym i gyd yn gwybod yn iawn sut y daw i ben ...

gweler “Problem grawn ar fwrdd gwyddbwyll”

Gwarcheidwad y "llwybr"

Bob yn ail, rydym yn ychwanegu’r holl ddynodwyr gwrthrych y daethom ar eu traws ar hyd y llwybr dychwelyd i mewn i arae, sy’n “lwybr” unigryw iddo:

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

Pro: Os oes cylchred yn y data, ni fyddwn yn prosesu'r un cofnod dro ar ôl tro o fewn yr un llwybr.
anfanteision: Ond ar yr un pryd, gallwn yn llythrennol osgoi'r holl gofnodion heb ailadrodd ein hunain.

gweler "Problem Symud Marchog"

Terfyn Hyd y Llwybr

Er mwyn osgoi'r sefyllfa o "crwydro" yn ôl ar ddyfnder annealladwy, gallwn gyfuno'r ddau ddull blaenorol. Neu, os nad ydym am gefnogi meysydd diangen, ychwanegwch amcangyfrif o hyd y llwybr at yr amod ar gyfer parhau â’r ailddigwyddiad:

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
)

Dewiswch ddull at eich dant!

Ffynhonnell: hab.com