🥇PostgreSQL Antipatterns: "Enweghi ngwụcha abụghị oke!", ma ọ bụ ntakịrị gbasara nlọghachi azụ

Ntughari - usoro dị ike ma dị mma ma ọ bụrụ na a na-eme otu omume "n'ime omimi" na data metụtara ya. Mana nlọghachị nke a na-achịkwaghị achịkwa bụ ihe ọjọọ nke nwere ike ibute ya ogbugbu na-adịghị agwụ agwụ usoro, ma ọ bụ (nke na-eme ugboro ugboro) na "iri" ebe nchekwa niile dị.

DBMS na nke a na-arụ ọrụ na otu ụkpụrụ - "Ha gwara m ka m gwuo ala, ya mere m gwuo ala"Arịrịọ gị nwere ike ọ bụghị naanị na-ebelata usoro ndị agbata obi, na-eburu ihe nrụpụta processor mgbe niile, kamakwa "dobe" nchekwa data dum, "eri" ebe nchekwa niile dị. nchedo megide enweghi ngwụcha - ọrụ nke onye mmepụta n'onwe ya.

Na PostgreSQL, ikike iji ajụjụ recursive site na WITH RECURSIVE pụtara n'oge ochie nke ụdị 8.4, mana ị ka nwere ike na-ezute arịrịọ "enweghị nchebe" mgbe niile. Kedu otu esi ewepụ onwe gị nsogbu ndị dị otú a?

Edela ajuju ajuju

Na dee ndị na-abụghị ndị na-emegharị emegharị. Ezi obi, gị K.O.

N'ezie, PostgreSQL na-enye ọtụtụ ọrụ ị nwere ike iji bụghị tinye recursion.

Jiri ụzọ dị iche iche isi dozie nsogbu ahụ

Mgbe ụfọdụ ị nwere ike lelee nsogbu ahụ site na "akụkụ dị iche". Enyere m ihe atụ nke ọnọdụ dị otú ahụ na isiokwu ahụ "SQL HowTo: 1000 na otu ụzọ nchịkọta" - ịba ụba nke usoro ọnụọgụgụ na-ejighi ọrụ mkpokọta omenala:

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;

Enwere ike iji nhọrọ sitere n'aka ndị ọkachamara mgbakọ na mwepụ dochie arịrịọ a:

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

Jiri genene_series kama loops

Ka anyị kwuo na anyị chere ọrụ nke imepụta prefixes niile nwere ike maka eriri '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;

Ị ji n'aka na ị chọrọ nlọghachi ebe a?... Ọ bụrụ na ị na-eji LATERAL и generate_series, mgbe ahụ ị gaghị achọ CTE:

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

Gbanwee usoro nchekwa data

Dịka ọmụmaatụ, ị nwere tebụl ozi ọgbakọ nwere njikọ sitere na onye zara ya, ma ọ bụ eriri banye netwọk mmekọrịta:

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

Ọ dị mma, arịrịọ a na-ahụkarị maka nbudata ozi niile n'otu isiokwu dị ka nke a:

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;

Ma ebe ọ bụ na anyị na-achọ mgbe niile isiokwu dum site na mgbọrọgwụ ozi, mgbe ahụ gịnị kpatara na anyị adịghị tinye ID ya na ntinye ọ bụla akpaka?

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

Ugbu a enwere ike ibelata ajụjụ anyị niile na-atụgharị ka ọ bụrụ nke a:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Jiri etinye "oke"

Ọ bụrụ na anyị enweghị ike ịgbanwe nhazi nke nchekwa data n'ihi ihe ụfọdụ, ka anyị hụ ihe anyị nwere ike ịdabere na ya ka ọbụna ọnụnọ nke njehie dị na data adịghị eduga na nlọghachi na-adịghị agwụ agwụ.

counter omimi nke nlọghachi azụ

Anyị na-ebuli counter otu n'otu n'otu n'otu n'otu n'otu n'otu ntabi anya ruo mgbe anyị ruru oke nke anyị chere na ezughị oke:

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

Pro: Mgbe anyị na-agbalị loop, anyị ka ga-eme ihe ọ bụla karịa kpọmkwem ókè iterations "na omimi".
ọghọm: Enweghị nkwa na anyị agaghị ahazi otu ndekọ ọzọ - dịka ọmụmaatụ, na omimi nke 15 na 25, mgbe ahụ kwa +10. Na ọ dịghị onye kwere nkwa ihe ọ bụla banyere "ugboro".

N'ezie, nlọghachite dị otú ahụ agaghị enwe njedebe, mana ọ bụrụ na n'ọkwa ọ bụla ọnụọgụ ndekọ na-abawanye ụba, anyị niile maara nke ọma ka ọ na-agwụ ...

lee "Nsogbu nke ọka na chessboard"

Onye nche nke "ụzọ"

Anyị na-agbakwunye ihe nchọpụta ihe niile anyị zutere n'okporo ụzọ nlọghachi azụ n'usoro, nke bụ "ụzọ" pụrụ iche na ya:

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

Pro: Ọ bụrụ na enwere okirikiri na data, anyị agaghị ahazi otu ndekọ ugboro ugboro n'ime otu ụzọ ahụ.
ọghọm: Ma n'otu oge ahụ, anyị nwere ike ịgafe ndekọ niile na-enweghị ikwughachi onwe anyị.

lee "Nsogbu Ntugharị Knight"

Oke Ogologo Ogologo Ụzọ

Iji zere ọnọdụ nke nlọghachi azụ "na-awagharị" na omimi na-enweghị nghọta, anyị nwere ike ijikọta ụzọ abụọ gara aga. Ma ọ bụ, ọ bụrụ na anyị achọghị ịkwado ubi ndị na-adịghị mkpa, tinyekwuo ọnọdụ maka ịga n'ihu na nlọghachi azụ na atụmatụ nke ogologo ụzọ:

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
)

Họrọ usoro masịrị gị!

isi: www.habr.com

PostgreSQL Antipatterns: "Infinity abụghị oke!", Ma ọ bụ ntakịrị gbasara nlọghachi azụ