ProHoster > Блог > Whakahaerenga > PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursion

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursion

Recursion - he tikanga tino kaha me te ngawari mena ka mahia nga mahi "hohonu" ki nga raraunga e pa ana. Engari ko te recursed recursion he kino tera ka arahi ki tetahi te whakamate mutunga kore tukanga, ranei (he nui ake te tupu) ki "kai" nga mahara katoa e waatea ana.

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursion
Ko te DBMS mo tenei kaupapa e mahi ana i runga i nga kaupapa kotahi - "сказали копать, я и копаю". Kaore e taea e to tono te whakaroa noa i nga mahi tata, me te tango i nga rauemi tukatuka, engari ka "whakataka" te katoa o te papaa raraunga, "kai" nga mahara katoa e waatea ana. te whakamarumaru ki te recursion mutunga kore - te kawenga a te kaiwhakawhanake ake.

I roto i te PostgreSQL, te kaha ki te whakamahi i nga patai recursive ma WITH RECURSIVE I puta mai i nga wa o mua o te putanga 8.4, engari ka taea tonu e koe te pa atu ki nga patai "kore" whakaraerae. Me pehea te whakakore i a koe i nga raruraru penei?

Kaua e tuhia nga patai recursive

A ka tuhi i nga mea kore-recursive. Tena koe, To K.O.

Inaa, he maha nga mahi a PostgreSQL ka taea e koe te whakamahi e kore tono hokinga.

Whakamahia he huarahi tino rereke ki te raruraru

I etahi wa ka taea e koe te titiro noa ki te raruraru mai i te "taha rereke". I hoatu e ahau he tauira o taua ahuatanga i roto i te tuhinga "SQL HowTo: 1000 me tetahi ara o te whakahiato" — te whakareatanga o te huinga tau me te kore e whakamahi i nga mahi whakahiato ritenga:

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;

Ka taea te whakakapi i tenei tono ki tetahi whiringa mai i nga tohunga pangarau:

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

Whakamahia te whakaputa_raupapa hei utu mo nga koropiko

Me kii kei te anga tatou ki te mahi ki te whakaputa i nga tohu tuatahi mo te aho '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;

Kei te tino hiahia koe ki te whakahoki mai i konei?.. Ki te whakamahi koe LATERAL и generate_series, ka kore koe e hiahia CTE:

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

Hurihia te hanganga pātengi raraunga

Hei tauira, kei a koe he ripanga o nga karere huinga me nga hononga mai i a wai i whakautu ki a wai, he miro ranei kei roto whatunga pāpori:

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

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursion
Ana, he ahua penei te ahua o te tono mo te tango i nga karere katoa mo te kaupapa kotahi:

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;

Engari i te mea ka hiahia tonu tatou i te kaupapa katoa mai i te karere pakiaka, he aha tatou ka kore ai tāpirihia tana ID ki ia urunga aunoa?

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

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursion
Inaianei ka taea te whakaiti i a maatau patai recursive ki tenei:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Whakamahia "whakatika"

Mena kaore e taea e tatou te whakarereke i te hanganga o te papaaarangi mo etahi take, kia kite tatou he aha ta tatou e whakawhirinaki ai na te mea ko te hapa i roto i nga raraunga kaore e arahi ki te hokinga mutunga kore.

He porotiti hohonu recursion

Ka whakanuia noa e matou te porotiti ma te kotahi i ia taahiraa whakamuri kia tae ra ano ki te tepe e whakaaro ana matou he koretake:

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

Pro: Ka ngana ana tatou ki te kopikopiko, ka kore e neke ake i te tepe o nga whitiwhitinga "hohonu".
raruraru: Karekau he taurangi ka kore matou e whakahaere ano i taua rekoata - hei tauira, i te hohonutanga o te 15 me te 25, katahi ka +10. A kaore tetahi i oati i tetahi mea mo te "whanui".

I runga i nga tikanga, kare he mutunga o taua recursion, engari mena ka piki haere te maha o nga rekoata i ia taahiraa, ka tino mohio tatou me pehea te mutunga...

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursiontirohia "Ko te raruraru o te witi i runga i te papa chessboard"

Kaitiaki o te "ara"

Ka taapirihia e matou nga kaitautuhi ahanoa katoa i tutaki ki a matou i te ara whakamuri ki roto i tetahi huinga, he "ara" ahurei ki reira:

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

Pro: Mena he huringa kei roto i nga raraunga, kare rawa matou e tukatuka i te rekoata kotahi i roto i te huarahi kotahi.
raruraru: Engari i te wa ano, ka taea e tatou te karo i nga rekoata katoa me te kore e whakahoki ano.

PostgreSQL Antipatterns: "Ehara te mutunga kore te rohe!", He iti ranei mo te recursiontirohia "Raruraru Nekehanga a Knight"

Tepete roa o te ara

Hei karo i te ahuatanga o te recursion "kopikopiko" i te hohonu e kore e taea te mohio, ka taea e tatou te whakakotahi i nga tikanga e rua o mua. Ranei, ki te kore matou e hiahia ki te tautoko i nga mara koretake, taapirihia te tikanga mo te haere tonu o te hokinga mai me te whakatau tata mo te roa o te huarahi:

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
)

Whiriwhiria he tikanga ki to reka!

Source: will.com

Tāpiri i te kōrero