🥇PostgreSQL Antipatterns: "Infinity ba iyaka ba!", Ko kaɗan game da maimaitawa

Maimaitawa - wani tsari mai ƙarfi da dacewa idan an aiwatar da ayyukan "zurfin-zurfin" iri ɗaya akan bayanai masu alaƙa. Amma sake dawowar da ba a kula da shi ba sharri ne da zai iya kaiwa ga ko wannensu kisa mara iyaka tsari, ko (wanda ke faruwa sau da yawa) zuwa "cin" duk abin da ke cikin ƙwaƙwalwar ajiya.

DBMS a wannan batun yana aiki akan ka'idodin guda ɗaya - "Suka ce in tona, sai na tonaBuƙatunku ba kawai zai rage tafiyar matakai na kusa ba, koyaushe yana ɗaukar albarkatun sarrafa kayan masarufi, amma har ma “zubar da” duk bayanan, “ci” duk ƙwaƙwalwar ajiyar da ke akwai. kariya daga sake dawowa mara iyaka - alhakin mai haɓakawa kansa.

A cikin PostgreSQL, ikon yin amfani da maimaita tambayoyin ta hanyar WITH RECURSIVE ya bayyana a cikin tarihi na sigar 8.4, amma har yanzu kuna iya saduwa da buƙatun "marasa tsaro" a kai a kai. Yadda za a kawar da kanku daga matsalolin irin wannan?

Kar a rubuta maimaita tambayoyin

Kuma rubuta waɗanda ba masu maimaitawa ba. Gaisuwa, K.O.

A zahiri, PostgreSQL yana ba da ayyuka da yawa waɗanda zaku iya amfani da su ba yi maimaitawa.

Yi amfani da wata hanya ta asali daban don matsalar

Wani lokaci zaka iya kallon matsalar kawai daga "bangaren daban". Na ba da misalin irin wannan yanayin a cikin labarin "SQL HowTo: 1000 da kuma hanyar tarawa ɗaya" - ninka saitin lambobi ba tare da amfani da ayyuka tara na al'ada ba:

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;

Ana iya maye gurbin wannan buƙatar tare da zaɓi daga masana ilimin lissafi:

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

Yi amfani dagene_series maimakon madaukai

Bari mu ce muna fuskantar aikin samar da duk yuwuwar prefixes don kirtani '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;

Shin kun tabbata kuna buƙatar maimaitawa anan?.. Idan kuna amfani LATERAL и generate_series, to ba ma za ku buƙaci CTE:

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

Canja tsarin bayanai

Misali, kuna da tebur na saƙonnin dandalin tattaunawa tare da haɗin kai daga wanda ya amsa ga wane, ko zaren ciki sadarwar zamantakewa:

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

To, buƙatu na yau da kullun don zazzage duk saƙonni akan jigo ɗaya yayi kama da haka:

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;

Amma tunda koyaushe muna buƙatar ɗaukacin batun daga tushen saƙon, to me yasa ba mu ƙara ID zuwa kowane shigarwa atomatik?

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

Yanzu duk tambayar mu na maimaitawa za a iya rage ta zuwa haka:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Yi amfani da aikace-aikacen "limiters"

Idan ba za mu iya canza tsarin bayanan bayanan ba saboda wasu dalilai, bari mu ga abin da za mu iya dogara da shi don ko da kasancewar kuskure a cikin bayanan ba zai haifar da sake dawowa ba.

Recursion zurfin counter

Muna ƙara ƙima da ɗaya a kowane mataki na maimaitawa har sai mun kai iyaka wanda muke ganin bai isa ba:

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

Pro: Lokacin da muka yi ƙoƙarin yin madauki, har yanzu ba za mu yi fiye da ƙayyadadden ƙayyadadden ƙayyadaddun abubuwa ba “a cikin zurfafa”.
fursunoni: Babu tabbacin cewa ba za mu sake aiwatar da rikodin iri ɗaya ba - alal misali, a zurfin 15 da 25, sannan kowane +10. Kuma babu wanda ya yi alkawarin wani abu game da "busa".

A bisa ka'ida, irin wannan maimaitawa ba zai zama marar iyaka ba, amma idan a kowane mataki adadin bayanan ya karu da yawa, duk mun san yadda ya ƙare ...

duba "Matsalar hatsi a kan chessboard"

Mai gadin "hanyar"

Muna ƙara duk abubuwan gano abubuwan da muka ci karo da su tare da hanyar komawa zuwa cikin tsararru, wanda ke da “hanyar” ta musamman zuwa gare ta:

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

Pro: Idan akwai sake zagayowar a cikin bayanan, ba za mu aiwatar da rikodin iri ɗaya akai-akai a cikin hanya ɗaya ba.
fursunoni: Amma a lokaci guda, za mu iya a zahiri ketare duk bayanan ba tare da maimaita kanmu ba.

duba "Matsalar Motsi ta Knight"

Iyakar Tsawon Hanya

Don kauce wa halin da ake ciki na sake dawowa "yawo" a zurfin da ba za a iya fahimta ba, za mu iya haɗa hanyoyin biyu na baya. Ko, idan ba ma so mu goyi bayan filayen da ba dole ba, ƙara yanayin ci gaba da maimaitawa tare da kimanta tsawon hanyar:

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
)

Zaɓi hanyar da za ku dandana!

source: www.habr.com

PostgreSQL Antipatterns: "Infinity ba iyaka ba!", Ko kadan game da sake dawowa