PostgreSQL Antipatterns: "Infinity ບໍ່ແມ່ນຂອບເຂດຈໍາກັດ!", ຫຼືເລັກນ້ອຍກ່ຽວກັບ recursion

ຊ້ຳ - ກົນໄກທີ່ມີປະສິດທິພາບແລະສະດວກຫຼາຍຖ້າຫາກວ່າການປະຕິບັດ "ໃນຄວາມເລິກ" ດຽວກັນກັບຂໍ້ມູນທີ່ກ່ຽວຂ້ອງ. ແຕ່ recursion uncontrolled ແມ່ນຄວາມຊົ່ວຮ້າຍທີ່ສາມາດນໍາໄປສູ່ການບໍ່ວ່າຈະ ການປະຕິບັດທີ່ບໍ່ມີທີ່ສິ້ນສຸດ ຂະບວນການ, ຫຼື (ທີ່ເກີດຂື້ນເລື້ອຍໆ) ກັບ "ກິນ" ຄວາມຊົງຈໍາທີ່ມີຢູ່ທັງຫມົດ.

DBMS ໃນເລື່ອງນີ້ເຮັດວຽກຢູ່ໃນຫຼັກການດຽວກັນ - "ພວກເຂົາບອກຂ້ອຍໃຫ້ຂຸດ, ສະນັ້ນຂ້ອຍຂຸດ". ການຮ້ອງຂໍຂອງທ່ານບໍ່ພຽງແຕ່ສາມາດຊ້າລົງຂະບວນການໃກ້ຄຽງ, ສືບຕໍ່ໃຊ້ຊັບພະຍາກອນຂອງໂປເຊດເຊີ, ແຕ່ຍັງ "ລຸດລົງ" ຖານຂໍ້ມູນທັງຫມົດ, "ກິນ" ຫນ່ວຍຄວາມຈໍາທີ່ມີຢູ່ທັງຫມົດ. ການ​ປົກ​ປັກ​ຮັກ​ສາ​ຕ້ານ​ການ recursion infinite​ - ຄວາມຮັບຜິດຊອບຂອງຜູ້ພັດທະນາເອງ.

ໃນ PostgreSQL, ຄວາມສາມາດໃນການໃຊ້ການສອບຖາມ recursive ຜ່ານ WITH RECURSIVE ປະກົດຢູ່ໃນເວລາບໍ່ດົນນານຂອງເວີຊັນ 8.4, ແຕ່ທ່ານຍັງສາມາດພົບກັບການສອບຖາມ "defenseless" ທີ່ມີຄວາມສ່ຽງເປັນປະຈໍາ. ວິທີການກໍາຈັດຕົວທ່ານເອງຈາກບັນຫາປະເພດນີ້?

ຢ່າຂຽນແບບສອບຖາມຊ້ຳໆ

ແລະຂຽນສິ່ງທີ່ບໍ່ຊໍ້າກັນ. ດ້ວຍຄວາມນັບຖື, K.O.

ໃນຄວາມເປັນຈິງ, PostgreSQL ສະຫນອງການເຮັດວຽກຫຼາຍຢ່າງທີ່ທ່ານສາມາດນໍາໃຊ້ໄດ້ ບໍ່ ນຳໃຊ້ recursion.

ໃຊ້ວິທີການທີ່ແຕກຕ່າງກັນໂດຍພື້ນຖານກັບບັນຫາ

ບາງຄັ້ງທ່ານພຽງແຕ່ສາມາດເບິ່ງບັນຫາຈາກ "ດ້ານທີ່ແຕກຕ່າງກັນ". ຂ້າພະເຈົ້າໄດ້ຍົກຕົວຢ່າງຂອງສະຖານະການດັ່ງກ່າວໃນບົດຄວາມ "SQL HowTo: 1000 ແລະວິທີຫນຶ່ງຂອງການລວບລວມ" — ການ​ຄູນ​ຂອງ​ຊຸດ​ຂອງ​ຕົວ​ເລກ​ໂດຍ​ບໍ່​ມີ​ການ​ນໍາ​ໃຊ້​ຫນ້າ​ທີ່​ລວມ​ທີ່​ກໍາ​ນົດ​ເອງ​:

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;

ການຮ້ອງຂໍນີ້ສາມາດຖືກແທນທີ່ດ້ວຍທາງເລືອກຈາກຜູ້ຊ່ຽວຊານດ້ານຄະນິດສາດ:

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

ໃຊ້ generate_series ແທນ loops

ສົມມຸດວ່າພວກເຮົາກໍາລັງປະເຊີນກັບວຽກງານຂອງການສ້າງຄໍານໍາຫນ້າທີ່ເປັນໄປໄດ້ທັງຫມົດສໍາລັບ string '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;

ທ່ານແນ່ໃຈບໍ່ວ່າຕ້ອງການ recursion ຢູ່ທີ່ນີ້?.. ຖ້າເຈົ້າໃຊ້ LATERAL и generate_seriesຫຼັງຈາກນັ້ນ, ທ່ານຈະບໍ່ຕ້ອງການ CTE:

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

ປ່ຽນໂຄງສ້າງຖານຂໍ້ມູນ

ຕົວຢ່າງ, ທ່ານມີຕາຕະລາງຂໍ້ຄວາມຂອງເວທີສົນທະນາທີ່ມີການເຊື່ອມຕໍ່ຈາກຜູ້ທີ່ຕອບສະຫນອງກັບໃຜ, ຫຼືຫົວຂໍ້ໃນ ເຄືອ​ຂ່າຍ​ສັງ​ຄົມ:

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

ດີ, ການຮ້ອງຂໍປົກກະຕິເພື່ອດາວໂຫລດຂໍ້ຄວາມທັງຫມົດໃນຫົວຂໍ້ຫນຶ່ງເບິ່ງຄືວ່າບາງສິ່ງບາງຢ່າງເຊັ່ນນີ້:

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;

ແຕ່ເນື່ອງຈາກວ່າພວກເຮົາສະເຫມີຕ້ອງການຫົວຂໍ້ທັງຫມົດຈາກຂໍ້ຄວາມຮາກ, ແລ້ວເປັນຫຍັງພວກເຮົາບໍ່ ເພີ່ມ ID ຂອງມັນໃສ່ແຕ່ລະລາຍການ ອັດຕະໂນມັດ?

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

ໃນປັດຈຸບັນການສອບຖາມ recursive ທັງຫມົດຂອງພວກເຮົາສາມາດຫຼຸດລົງພຽງແຕ່ນີ້:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

ໃຊ້ "ຂີດຈໍາກັດ" ທີ່ໃຊ້ແລ້ວ

ຖ້າພວກເຮົາບໍ່ສາມາດປ່ຽນໂຄງສ້າງຂອງຖານຂໍ້ມູນໄດ້ດ້ວຍເຫດຜົນບາງຢ່າງ, ໃຫ້ເບິ່ງສິ່ງທີ່ພວກເຮົາສາມາດອີງໃສ່ເພື່ອວ່າເຖິງແມ່ນວ່າການປະກົດຕົວຂອງຂໍ້ຜິດພາດໃນຂໍ້ມູນກໍ່ບໍ່ນໍາໄປສູ່ການເອີ້ນຄືນທີ່ບໍ່ສິ້ນສຸດ.

ຕົວນັບຄວາມເລິກ Recursion

ພວກເຮົາພຽງແຕ່ເພີ່ມການນັບຖອຍຫຼັງເທື່ອລະອັນໃນແຕ່ລະຂັ້ນຕອນ recursion ຈົນກ່ວາພວກເຮົາບັນລຸຂອບເຂດຈໍາກັດທີ່ພວກເຮົາຖືວ່າບໍ່ພຽງພໍຢ່າງຈະແຈ້ງ:

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

Pro: ໃນເວລາທີ່ພວກເຮົາພະຍາຍາມ loop, ພວກເຮົາຍັງຈະເຮັດບໍ່ເກີນຂອບເຂດຈໍາກັດຂອງ iterations "ໃນຄວາມເລິກ".
cons: ບໍ່ມີການຮັບປະກັນວ່າພວກເຮົາຈະບໍ່ດໍາເນີນການບັນທຶກດຽວກັນອີກເທື່ອຫນຶ່ງ - ຕົວຢ່າງ, ຢູ່ທີ່ຄວາມເລິກ 15 ແລະ 25, ແລະຫຼັງຈາກນັ້ນທຸກໆ +10. ແລະບໍ່ມີໃຜສັນຍາຫຍັງກ່ຽວກັບ "ຄວາມກວ້າງ".

ຢ່າງເປັນທາງການ, ການເອີ້ນຄືນດັ່ງກ່າວຈະບໍ່ເປັນອັນເປັນນິດ, ແຕ່ຖ້າແຕ່ລະຂັ້ນຕອນຈໍານວນບັນທຶກເພີ່ມຂຶ້ນເປັນເລກເລກ, ພວກເຮົາທຸກຄົນຮູ້ດີວ່າມັນສິ້ນສຸດລົງແນວໃດ ...

ເບິ່ງ “ບັນຫາຂອງເມັດພືດຢູ່ໃນກະດານໝາກຮຸກ”

ຜູ້ປົກຄອງຂອງ "ເສັ້ນທາງ"

ພວກເຮົາສະຫຼັບກັນເພີ່ມຕົວລະບຸວັດຖຸທັງໝົດທີ່ພວກເຮົາພົບຢູ່ຕາມເສັ້ນທາງການເອີ້ນຄືນເຂົ້າໃນອາເຣ, ເຊິ່ງເປັນ “ເສັ້ນທາງ” ທີ່ເປັນເອກະລັກສະເພາະ:

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

Pro: ຖ້າມີວົງຈອນໃນຂໍ້ມູນ, ພວກເຮົາຈະບໍ່ດໍາເນີນການບັນທຶກດຽວກັນຊ້ໍາຊ້ອນຢູ່ໃນເສັ້ນທາງດຽວກັນ.
cons: ແຕ່ໃນເວລາດຽວກັນ, ພວກເຮົາສາມາດ bypass ບັນທຶກທັງຫມົດຢ່າງແທ້ຈິງໂດຍບໍ່ມີການເຮັດຊ້ໍາຕົວເຮົາເອງ.

ເບິ່ງ "ບັນຫາການເຄື່ອນຍ້າຍຂອງ Knight"

ຈຳກັດຄວາມຍາວຂອງເສັ້ນທາງ

ເພື່ອຫຼີກລ້ຽງສະຖານະການຂອງ recursion "wandering" ຢູ່ໃນຄວາມເລິກທີ່ບໍ່ສາມາດເຂົ້າໃຈໄດ້, ພວກເຮົາສາມາດສົມທົບສອງວິທີການທີ່ຜ່ານມາ. ຫຼື, ຖ້າພວກເຮົາບໍ່ຕ້ອງການສະຫນັບສະຫນູນຊ່ອງຂໍ້ມູນທີ່ບໍ່ຈໍາເປັນ, ເສີມເງື່ອນໄຂສໍາລັບການສືບຕໍ່ recursion ດ້ວຍການຄາດຄະເນຂອງຄວາມຍາວຂອງເສັ້ນທາງ:

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
)

ເລືອກວິທີການເພື່ອລົດຊາດຂອງທ່ານ!

ແຫຼ່ງຂໍ້ມູນ: www.habr.com