PostgreSQL Antipatterns៖ “Infinity is not the limit!”, or a little about recursion

ដំណើរឡើងវិញ - យន្តការដ៏មានឥទ្ធិពល និងងាយស្រួល ប្រសិនបើសកម្មភាព "ស៊ីជម្រៅ" ដូចគ្នាត្រូវបានអនុវត្តលើទិន្នន័យដែលពាក់ព័ន្ធ។ ប៉ុន្តែ​ការ​កើតឡើង​វិញ​ដោយ​មិន​អាច​គ្រប់គ្រង​បាន​គឺ​ជា​អំពើ​អាក្រក់​មួយ​ដែល​អាច​នាំ​ឱ្យ​មាន​ទាំង​អស់ ការប្រតិបត្តិគ្មានទីបញ្ចប់ ដំណើរការ ឬ (ដែលកើតឡើងញឹកញាប់ជាង) ទៅ "ញ៉ាំ" ការចងចាំដែលមានទាំងអស់។.

DBMS ក្នុងន័យនេះធ្វើការលើគោលការណ៍ដូចគ្នា - "គេ​ប្រាប់​ខ្ញុំ​ឲ្យ​ជីក ដូច្នេះ​ខ្ញុំ​ជីក"។ សំណើរបស់អ្នកមិនត្រឹមតែអាចបន្ថយដំណើរការដែលនៅជិតខាងនោះទេ ដោយទទួលយកធនធានរបស់ processor ឥតឈប់ឈរ ប៉ុន្តែថែមទាំង "ទម្លាក់" មូលដ្ឋានទិន្នន័យទាំងមូល "ញ៉ាំ" អង្គចងចាំដែលមានទាំងអស់។ ការការពារប្រឆាំងនឹងការកើតឡើងវិញគ្មានកំណត់ - ទំនួលខុសត្រូវរបស់អ្នកអភិវឌ្ឍន៍ខ្លួនឯង។

នៅក្នុង PostgreSQL សមត្ថភាពក្នុងការប្រើសំណួរដដែលៗតាមរយៈ WITH RECURSIVE បានបង្ហាញខ្លួននៅក្នុងជំនាន់ 8.4 ដែលមិនធ្លាប់មានពីមុនមក ប៉ុន្តែអ្នកនៅតែអាចជួបប្រទះសំណើ "គ្មានការការពារ" ដែលអាចងាយរងគ្រោះបានជាទៀងទាត់។ តើធ្វើដូចម្តេចដើម្បីកម្ចាត់បញ្ហាបែបនេះ?

កុំសរសេរសំណួរដដែលៗ

ហើយ​សរសេរ​ពាក្យ​ដែល​មិន​ប្រើ​ដដែលៗ។ ដោយក្តីគោរព K.O.

តាមពិត PostgreSQL ផ្តល់នូវមុខងារជាច្រើនដែលអ្នកអាចប្រើ មិនមាន អនុវត្តការហៅឡើងវិញ។

ប្រើវិធីសាស្រ្តផ្សេងគ្នាជាមូលដ្ឋានចំពោះបញ្ហា

ពេលខ្លះអ្នកអាចមើលបញ្ហាពី "ផ្នែកផ្សេងគ្នា" ។ ខ្ញុំបានផ្តល់ឧទាហរណ៍អំពីស្ថានភាពបែបនេះនៅក្នុងអត្ថបទ "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 ជំនួសឱ្យរង្វិលជុំ

ឧបមាថាយើងប្រឈមមុខនឹងភារកិច្ចបង្កើតបុព្វបទដែលអាចធ្វើបានទាំងអស់សម្រាប់ខ្សែអក្សរ '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;

តើ​អ្នក​ប្រាកដ​ថា​អ្នក​ត្រូវ​ការ​ការ​ហៅ​ឡើងវិញ​នៅ​ទីនេះ​?.. ប្រសិនបើអ្នក​ប្រើ 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;

ប៉ុន្តែដោយសារយើងតែងតែត្រូវការប្រធានបទទាំងមូលពីសារជា root ដូច្នេះហេតុអ្វីបានជាយើងមិនធ្វើ បន្ថែមលេខសម្គាល់របស់វាទៅធាតុនីមួយៗ ស្វ័យប្រវត្តិ?

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

ឥឡូវ​នេះ​សំណួរ​ដដែលៗ​របស់​យើង​ទាំង​មូល​អាច​ត្រូវ​បាន​កាត់​បន្ថយ​មក​ត្រឹម​តែ​នេះ៖

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

ប្រើ "ដែនកំណត់" ដែលបានអនុវត្ត

ប្រសិនបើយើងមិនអាចផ្លាស់ប្តូររចនាសម្ព័ន្ធនៃមូលដ្ឋានទិន្នន័យដោយហេតុផលមួយចំនួន សូមមើលអ្វីដែលយើងអាចពឹងផ្អែកលើ ដូច្នេះសូម្បីតែវត្តមាននៃកំហុសនៅក្នុងទិន្នន័យក៏មិននាំឱ្យមានការកើតឡើងដដែលៗគ្មានទីបញ្ចប់ដែរ។

ការគណនាជំរៅឡើងវិញ

យើងគ្រាន់តែបង្កើនការរាប់ម្តងក្នុងមួយជំហាននៃការបង្កើតឡើងវិញនីមួយៗ រហូតដល់យើងឈានដល់ដែនកំណត់ដែលយើងចាត់ទុកថាមិនគ្រប់គ្រាន់៖

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

គាំទ្រ: នៅពេលដែលយើងព្យាយាមរង្វិលជុំ យើងនឹងនៅតែធ្វើមិនលើសពីដែនកំណត់ដែលបានបញ្ជាក់នៃការធ្វើឡើងវិញ "នៅក្នុងជម្រៅ" ។
គុណវិបត្តិ: មិនមានការធានាថាយើងនឹងមិនដំណើរការកំណត់ត្រាដដែលម្តងទៀតទេ - ឧទាហរណ៍នៅជម្រៅ 15 និង 25 ហើយបន្ទាប់មករៀងរាល់ +10 ។ ហើយគ្មាននរណាម្នាក់សន្យាអ្វីអំពី "ទទឹង" ទេ។

ជា​ផ្លូវការ ការ​បង្កើត​ឡើងវិញ​បែប​នេះ​នឹង​មិន​មាន​កំណត់​ទេ ប៉ុន្តែ​ប្រសិនបើ​នៅ​ជំហាន​នីមួយៗ​ចំនួន​នៃ​កំណត់ត្រា​កើនឡើង​ជា​និទស្សន្ត យើង​ទាំងអស់​គ្នា​ដឹង​ច្បាស់​ថា​វា​បញ្ចប់​ដោយ​របៀប​ណា...

សូមមើល "បញ្ហាគ្រាប់ធញ្ញជាតិនៅលើក្តារអុក"

អាណាព្យាបាលនៃ "ផ្លូវ"

យើងឆ្លាស់គ្នាបន្ថែមឧបករណ៍កំណត់អត្តសញ្ញាណវត្ថុទាំងអស់ដែលយើងជួបប្រទះនៅតាមបណ្តោយផ្លូវបង្កើតឡើងវិញទៅក្នុងអារេមួយ ដែលជា "ផ្លូវ" តែមួយគត់ទៅវា៖

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

គាំទ្រ: ប្រសិនបើមានវដ្ដនៅក្នុងទិន្នន័យ យើងនឹងមិនដំណើរការកំណត់ត្រាដដែលដដែលៗនៅក្នុងផ្លូវដដែលនោះទេ។
គុណវិបត្តិ: ប៉ុន្តែក្នុងពេលជាមួយគ្នានេះ យើងអាចរំលងកំណត់ត្រាទាំងអស់ដោយព្យញ្ជនៈដោយមិនធ្វើម្តងទៀតដោយខ្លួនឯង។

សូមមើល "បញ្ហាផ្លាស់ទីរបស់ Knight"

ដែនកំណត់ប្រវែងផ្លូវ

ដើម្បីជៀសវាងស្ថានភាពនៃការនិយាយឡើងវិញ "វង្វេង" នៅជម្រៅដែលមិនអាចយល់បាន យើងអាចបញ្ចូលគ្នានូវវិធីពីរមុននេះ។ ឬប្រសិនបើយើងមិនចង់គាំទ្រវាលដែលមិនចាំបាច់ សូមបន្ថែមលក្ខខណ្ឌសម្រាប់ការបន្តការហៅឡើងវិញដោយប៉ាន់ស្មានប្រវែងផ្លូវ៖

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