🥇PostgreSQL Antipatterns: "Infinity ha se moeli!", Kapa Hanyenyane mabapi le ho iphetetsa

Phetoho - mokhoa o matla haholo le o bonolo haeba liketso tse tšoanang "tse tebileng" li etsoa ho data e amanang. Empa ho iphetetsa ho sa laoleheng ke bobe bo ka lebisang ho tse ling phethahatso e sa feleng ts'ebetso, kapa (e etsahalang hangata) ho "ho ja" memori yohle e teng.

DBMS tabeng ena e sebetsa ka melao-motheo e tšoanang - "Ba ile ba mpolella hore ke cheke, kahoo kea cheka". Kopo ea hau e ke ke ea liehisa lits'ebetso tsa boahelani feela, ho nka lisebelisoa tsa processor khafetsa, empa hape "lahlela" database kaofela, "ho ja" mohopolo o teng. tshireletso kgahlanong le phetetso e sa feleng - boikarabelo ba moqapi ka boeena.

Ho PostgreSQL, bokhoni ba ho sebelisa lipotso tse iphetang ka WITH RECURSIVE e hlahile mehleng ea khale ea mofuta oa 8.4, empa u ntse u ka kopana le likopo tse ka bang kotsi tsa "ho hloka tšireletso". Joang ho tlosa mathata a mofuta ona?

Se ke oa ngola lipotso tse iphetang

Le ho ngola tse sa iphetang. Ka 'nete, K.O.

Ebile, PostgreSQL e fana ka lits'ebetso tse ngata tseo u ka li sebelisang ha etsa kopo ya boipheto.

Sebelisa mokhoa o fapaneng oa ho rarolla bothata

Ka linako tse ling u ka sheba feela bothata ho tloha "lehlakoreng le fapaneng". Ke fane ka mohlala oa boemo bo joalo sehloohong "SQL HowTo: 1000 le mokhoa o le mong oa ho kopanya" - katiso ea palo ea linomoro ntle le ho sebelisa mesebetsi e akaretsang e tloaelehileng:

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;

Kopo ena e ka nkeloa sebaka ke khetho e tsoang ho litsebi tsa lipalo:

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

Sebelisa generate_series sebakeng sa loops

Ha re re re tobane le mosebetsi oa ho hlahisa li-prefixes tsohle tsa khoele '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;

Na u na le bonnete ba hore u hloka ho pheta-pheta moo?.. Haeba u sebelisa LATERAL и generate_series, joale u ke ke ua hloka le CTE:

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

Fetola sebopeho sa database

Mohlala, o na le tafole ea melaetsa ea forum e nang le likhokahano ho tsoa ho mang ea arabileng, kapa khoele ho marang-rang a sechaba:

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

Che, kopo e tloaelehileng ea ho khoasolla melaetsa eohle sehloohong se le seng e shebahala tjena:

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;

Empa kaha re lula re hloka sehlooho sohle ho tsoa molaetseng oa motso, ke hobane'ng ha re sa etse joalo eketsa ID ea eona ho kenyo e 'ngoe le e 'ngoe ka ho iketsa?

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

Hona joale potso ea rona eohle e iphetang e ka fokotsoa ho ena feela:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Sebelisa "limiters" tse sebelisitsoeng

Haeba re sa khone ho fetola sebopeho sa database ka lebaka le itseng, a re boneng hore na re ka itšetleha ka eng e le hore esita le ho ba teng ha phoso ho data ho se ke ha lebisa ho khutla ho sa feleng.

Recursion deep counter

Re eketsa k'haonte ka bonngoe mohatong o mong le o mong oa ho ipheta ho fihlela re fihla moeling oo re nkang hore ha oa lekana:

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

Pro: Ha re leka ho loop, re ntse re ke ke ra etsa ho feta moeli o boletsoeng oa ho pheta-pheta "ka botebo".
tlhoka mesola: Ha ho na tiiso ea hore re ke ke ra sebetsana le rekoto e tšoanang hape - mohlala, botebong ba 15 le 25, ebe ka mor'a +10 ka 'ngoe. 'Me ha ho motho ea tšepisitseng letho ka "bophara".

Ha e le hantle, phetoho e joalo e ke ke ea e-ba e sa feleng, empa haeba mohato o mong le o mong palo ea lirekoto e eketseha ka potlako, bohle re tseba hantle hore na e qetella joang ...

bona "Bothata ba lijo-thollo papaling ea chess"

Mohlokomeli oa "tsela"

Ka mokhoa o mong, re kenyelletsa li-identifiers tsohle tseo re kopaneng le tsona tseleng ea recursion ka har'a sehlopha, e leng "tsela" e ikhethang ho eona:

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

Pro: Haeba ho na le potoloho ho data, re ke ke ra sebetsa rekoto e tšoanang khafetsa ka tsela e ts'oanang.
tlhoka mesola: Empa ka nako e tšoanang, re ka tlōla litlaleho tsohle ka ho toba ntle le ho ipheta.

bona "Knight's Move Problem"

Length Length Limit

Ho qoba boemo ba "ho lelera" hape ka botebo bo sa utloisiseheng, re ka kopanya mekhoa e 'meli e fetileng. Kapa, haeba re sa batle ho tšehetsa masimo a sa hlokahaleng, tlatselletsa boemo ba ho tsoela pele ho pheta-pheta ka khakanyo ea bolelele ba tsela:

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
)

Khetha mokhoa oo u o ratang!

Source: www.habr.com

PostgreSQL Antipatterns: "Infinity ha se moeli!", Kapa Hanyenyane ka ho khutla