🥇PostgreSQL Antipatterns: "Infinity's not the limit!", Ama Wax yar oo ku saabsan soo noqoshada

Soo noqnoqoshada - farsamo aad u xoog badan oo ku habboon haddii isla ficilada "qoto dheer" lagu sameeyo xogta la xidhiidha. Laakin soo noq-noqoshada aan la xakameynin waa shar keeni karta midna dil aan dhamaad lahayn habka, ama (taas oo marar badan dhacda) si "cunida" dhammaan xusuusta la heli karo.

DBMS arrintan la xiriira waxay ku shaqeeyaan mabaadi'da isku mid ah - "Waxay igu yidhaahdeen qodo, markaa waan qodayCodsigaagu ma aha oo kaliya inuu hoos u dhigo geeddi-socodka deriska, si joogto ah u qaadanaya agabka processor-ka, laakiin sidoo kale wuxuu "dhigi karaa" keydka xogta oo dhan, "cunida" dhammaan xusuusta la heli karo. ka ilaalinta soo noqoshada aan dhammaadka lahayn - mas'uuliyadda horumariyaha laftiisa.

Gudaha PostgreSQL, awooda isticmaalka su'aalaha soo noqnoqda iyada oo loo marayo WITH RECURSIVE wuxuu soo muuqday waqtigii hore ee nooca 8.4, laakiin waxaad wali si joogto ah ula kulmi kartaa su'aalo "difaac la'aan" oo nugul. Sidee naftaada uga takhalusi kartaa dhibaatooyinka noocaan ah?

Ha qorin weydiimaha soo noqnoqda

Oo qor kuwa aan soo noqnoqoneynin. Daacadnimo, K.O.

Dhab ahaantii, PostgreSQL waxay bixisaa hawlo badan oo aad isticmaali karto ma codsan soo noqnoqoshada.

Isticmaal hab asal ahaan ka duwan dhibaatada

Mararka qaarkood waxaad dhibaatada ka eegi kartaa "dhinaca kala duwan". Waxaan tusaale u soo qaatay xaaladdan oo kale maqaalka "SQL HowTo: 1000 iyo hal hab oo isku-dar ah" - Isku dhufashada tirooyin tirooyin ah iyada oo aan la isticmaalin hawlaha guud ee caadiga ah:

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;

Codsigan waxaa lagu beddeli karaa ikhtiyaarka khubarada xisaabta:

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

Isticmaal gene_taxane halkii aad ka isticmaali lahayd wareegyada

Aynu nidhaahno waxa ina hor yaalla hawsha soo saarista dhammaan horgalayaasha suurtagalka ah ee xadhigga '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 hubtaa inaad u baahan tahay soo noqnoqoshada halkan?.. Haddii aad isticmaasho LATERAL и generate_series, markaa xitaa uma baahnid CTE:

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

Beddel qaabdhismeedka xogta

Tusaale ahaan, waxa aad haysaa jadwal fariimo gole ah oo xidhiidho ka yimi cidda u jawaabtay, ama dun ku jirta shabakadda bulshada:

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

Hagaag, codsiga caadiga ah ee lagu soo dejiyo dhammaan fariimaha hal mowduuc ayaa u eg sidan:

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;

Laakiin maadaama aan had iyo jeer u baahanahay mawduuca oo dhan fariinta xididka, markaa sababta annagu ma samayno ku dar aqoonsigiisa gelitaan kasta si toos ah?

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

Hadda dhammaan su'aalahayaga soo noqnoqda waxaa lagu soo koobi karaa sidan:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Isticmaal "limiters" lagu dabaqay

Haddii aanaan awoodin inaan bedelno qaab dhismeedka xogta sababo qaar, aan aragno waxa aan ku tiirsanaan karno si xitaa jiritaanka qaladka xogta uusan u horseedin soo noqnoqosho aan dhammaad lahayn.

Moolka qoto dheer ee soo noqnoqda

Waxaan si fudud u kordhineynaa hal hal talaabo kasta oo dib u soo noqoshada ilaa aan ka gaarno xadka aan u aragno mid aan ku filneyn:

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

Pro: Markaan isku dayno inaan duubno, wali ma samayn doono wax ka badan xadka la cayimay ee ku celcelinta "si qoto dheer".
qasaarooyinka: Ma jirto wax dammaanad qaad ah oo ah inaanaan dib u habeyn doonin isla rikoorkii - tusaale ahaan, qoto dheer oo ah 15 iyo 25, ka dibna +10 kasta. Qofna ma ballan qaadin wax ku saabsan "ballaaran".

Si rasmi ah, soo noqoshada noocan oo kale ah ma noqon doonto mid aan dhammaad lahayn, laakiin haddii tillaabo kasta tirada diiwaanadu ay u korodho si xad dhaaf ah, dhammaanteen si fiican ayaan u ognahay sida ay u dhammaato ...

arag "Dhibaatada badarka ee shatashka"

ilaaliyaha "dariiqa"

Waxa aanu si beddel ah ugu darnaa dhammaan tilmaamayaasha shayga aanu kula kulanay dariiqa soo noqnoqoshada oo aanu galnay habayn, kaas oo u gaar ah “waddo”

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

Pro: Haddii uu jiro wareeg ah xogta, gabi ahaanba ma socodsiin doono diiwaan isku mid ah si ku celcelis ah isla jidka dhexdiisa.
qasaarooyinka: Laakiin isla mar ahaantaana, waxaan si dhab ah u dhaafi karnaa dhammaan diiwaannada innagoo aan nafteena ku celin.

eeg "Dhibaatada Dhaqdhaqaaqa Knight"

Xadka Dhererka Waddada

Si looga fogaado xaaladda soo noqnoqoshada "wareejinta" qoto dheer oo aan la fahmi karin, waxaan isku dari karnaa labadii hab ee hore. Ama, haddii aynaan doonayn in aan taageerno meelaha aan loo baahnayn, ku kabo xaaladda sii wadida soo noqoshada qiyaasta dhererka wadada:

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
)

Dooro hab dhadhankaaga!

Source: www.habr.com

PostgreSQL Antipatterns: "Infinity maaha xadka!", Ama wax yar oo ku saabsan soo noqoshada