🥇PostgreSQL Antipatterns: "Bêdawî ne sînor e!", An Piçek di derbarê vegerandinê de

Recursion - Ger heman çalakiyên "kûr" li ser daneyên têkildar têne kirin, mekanîzmayek pir hêzdar û hêsan e. Lê vegerandina bêkontrol xirabiyek e ku dikare bibe sedema her duyan îdamkirina bêdawî pêvajoyê, an jî (ku pir caran diqewime). "xwarin" hemî bîranîna berdest.

DBMS di vî warî de li ser heman prensîban dixebitin - "Ji min re gotin bikole, loma ez dikolim". Daxwaza we ne tenê dikare pêvajoyên cîran hêdî bike, bi berdewamî çavkaniyên pêvajoyê digire, lê di heman demê de hemî databasê jî "davêje", hemî bîranîna berdest "xwar". Ji ber vê yekê parastina li dijî vegerandina bêdawî - berpirsiyariya pêşdebir bi xwe.

Di PostgreSQL de, şiyana karanîna pirsên vegerî bi riya WITH RECURSIVE di demên kevnare yên guhertoya 8.4 de xuya bû, lê hûn hîn jî bi rêkûpêk dikarin bi pirsên "bêparastinê" yên potansiyel ên xeternak re rû bi rû bibin. Meriv çawa xwe ji pirsgirêkên bi vî rengî xilas bike?

Pirsên paşverû nenivîsin

Û yên neveger binivîsin. Bi rêz û hurmet, K.O.

Bi rastî, PostgreSQL gelek fonksiyonên ku hûn dikarin bikar bînin peyda dike ne vegerandinê bicih bikin.

Ji bo pirsgirêkê nêzîkatiyek bingehîn a cûda bikar bînin

Carinan hûn dikarin tenê ji "aliyê cûda" li pirsgirêkê binêrin. Min di gotarê de mînakek ji rewşek weha da "SQL HowTo: 1000 û yek awayê kombûnê" - Zêdekirina komek hejmaran bêyî karanîna fonksiyonên berhevokê yên xwerû:

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;

Ev daxwaz dikare bi vebijarkek ji pisporên matematîkê were guheztin:

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

Li şûna xelekên generîne_series bikar bînin

Em bêjin em bi peywira çêkirina hemî pêşgiriyên gengaz ên ji bo rêzekê re rû bi rû ne '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;

Ma hûn guman dikin ku hûn li vir hewceyê vegerandinê ne?.. Ger hûn bikar bînin LATERAL и generate_series, wê hingê hûn ê ne hewce ne CTE jî:

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

Struktura databasê biguherînin

Mînakî, we tabloyek peyamên forumê yên bi girêdanên kê bersiv daye kê heye, an mijarek di nav de tora civakî:

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

Welê, daxwazek gelemperî ji bo dakêşana hemî peyaman li ser yek mijarê tiştek wusa xuya dike:

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;

Lê ji ber ku em her gav hewceyê tevahiya mijarê ji peyama bingehîn e, wê hingê çima em ne hewce ne nasnameya xwe li her têketinê zêde bike otomatîkî?

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

Naha tevahiya lêpirsîna meya vegerî dikare bi vê yekê were kêm kirin:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

"Sînorên" sepandî bikar bînin

Ger em ji ber hin sedeman nikaribin avahiya databasê biguhezînin, ka em bibînin ka em dikarin li ser çi bisekinin da ku tewra hebûna xeletiyek di daneyê de jî rê li ber vegerek bêdawî neke.

Berhevkarê kûrahiya vegerê

Em tenê di her gavê vegerandinê de jimarvan yek zêde dikin heya ku em bigihîjin sînorek ku em eşkere neguncaw dibînin:

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

Pro: Dema ku em hewl bidin ku biqedînin, em ê dîsa jî ji sînorê diyarkirî ya dubareyan "bi kûrahî" bêtir nekin.
Humberto: Garantî tune ku em ê dîsa heman tomarê nepêçin - mînakî, li kûrahiya 15 û 25, û paşê her +10. Û tu kesî tiştek li ser "firehiyê" soz neda.

Bi awayekî fermî, vegerandineke wiha dê bêsînor nebe, lê ger di her gavekê de hejmara tomaran qat bi qat zêde bibe, em hemî baş dizanin ka ew çawa bi dawî dibe...

Binêre "Pirsgirêka genimên li ser tabloya satrancê"

Parêzvanê "rê"

Em hemî nasnameyên tiştên ku me di riya vegerê de pê re rû bi rû maye bi awayekî din lê zêde dikin, ku ji wê re "rêyek" yekta ye:

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

Pro: Ger di daneyan de çerxek hebe, em ê bê guman heman tomarê di heman rêyê de dubare nekin.
Humberto: Lê di heman demê de, em dikarin bi rastî bêyî ku xwe dubare bikin, hemî tomaran derbas bikin.

Binêre "Pirsgirêka Tevgera Knight"

Rêya Length Limit

Ji bo ku em ji rewşa vegerê "gerê" ya di kûrahiyek nayê fêmkirin de dûr bisekinin, em dikarin her du rêbazên berê li hev bikin. An jî, heke em naxwazin qadên nepêwist piştgirî bikin, şertê berdewamkirina vegerê bi texmînek dirêjahiya rêyê temam bikin:

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
)

Rêbazek li gorî kêfa xwe hilbijêrin!

Source: www.habr.com

PostgreSQL Antipatterns: "Bêdawî ne sînor e!", An Piçek li ser vegerandinê