🥇PostgreSQL Antipatterns: “Sonsuzluk sınır değildir!” veya Özyineleme hakkında biraz

Özyineleme - İlgili veriler üzerinde aynı "derinlemesine" eylemlerin gerçekleştirilmesi durumunda çok güçlü ve kullanışlı bir mekanizma. Ancak kontrolsüz özyineleme her ikisine de yol açabilecek bir kötülüktür. sonsuz infaz süreç veya (ki bu daha sık olur) mevcut tüm hafızayı "yemek".

Bu bağlamda DBMS aynı prensipler üzerinde çalışır - "Bana kazmamı söylediler, ben de kazdım". İsteğiniz yalnızca komşu işlemleri yavaşlatmakla kalmaz, sürekli olarak işlemci kaynaklarını tüketir, aynı zamanda tüm veritabanını "düşürerek" tüm kullanılabilir belleği "tüketebilir". Bu nedenle sonsuz yinelemeye karşı koruma - geliştiricinin sorumluluğundadır.

PostgreSQL'de özyinelemeli sorguları kullanma yeteneği WITH RECURSIVE sürüm 8.4'ten çok eski zamanlardan beri ortaya çıktı, ancak yine de potansiyel olarak savunmasız "savunmasız" isteklerle düzenli olarak karşılaşabilirsiniz. Bu tür sorunlardan kendinizi nasıl kurtarabilirsiniz?

Özyinelemeli sorgular yazmayın

Ve özyinelemeli olmayanları yazın. Saygılarımla, K.O.

Aslında PostgreSQL, kullanabileceğiniz oldukça fazla işlevsellik sağlar. hayır yinelemeyi uygulayın.

Soruna temelde farklı bir yaklaşım kullanın

Bazen soruna “farklı taraftan” bakabilirsiniz. Yazıda böyle bir duruma örnek verdim "SQL HowTo: 1000 ve toplamanın bir yolu" — özel toplama işlevlerini kullanmadan bir dizi sayının çarpılması:

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;

Bu istek, matematik uzmanlarından gelecek bir seçenekle değiştirilebilir:

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

Döngüler yerine created_series kullanın

Diyelim ki bir dize için olası tüm önekleri oluşturma göreviyle karşı karşıyayız '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;

Burada özyinelemeye ihtiyacınız olduğundan emin misiniz?.. Eğer kullanırsanız LATERAL и generate_series, o zaman CTE'ye bile ihtiyacınız olmayacak:

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

Veritabanı yapısını değiştir

Örneğin, kimin kime yanıt verdiğini gösteren bağlantıları içeren bir forum mesajları tablonuz veya bir ileti diziniz var. sosyal ağ:

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

Bir konudaki tüm mesajları indirmeye yönelik tipik bir istek şuna benzer:

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;

Ancak her zaman kök mesajdaki konunun tamamına ihtiyacımız olduğuna göre neden kimliğini her girişe ekle otomatik?

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

Artık özyinelemeli sorgumuzun tamamı şuna indirgenebilir:

SELECT
  *
FROM
  message
WHERE
  theme_id = $1;

Uygulanan "sınırlayıcıları" kullanın

Herhangi bir nedenle veritabanının yapısını değiştiremiyorsak neye güvenebileceğimize bir bakalım, böylece veride bir hatanın varlığı bile sonsuz yinelemeye yol açmaz.

Özyineleme derinlik sayacı

Açıkça yetersiz olduğunu düşündüğümüz bir sınıra ulaşana kadar her yineleme adımında sayacı birer birer artırırız:

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

Artılar: Döngü yapmaya çalıştığımızda, yine de belirtilen "derinlemesine" yineleme sınırından fazlasını yapmayacağız.
eksileri: Aynı kaydı tekrar işlemeyeceğimizin garantisi yoktur - örneğin 15 ve 25 derinlikte ve ardından her +10'da bir. Ve kimse “genişlik” konusunda bir şey vaat etmedi.

Resmen böyle bir yineleme sonsuz olmayacak ama her adımda kayıt sayısı katlanarak artıyorsa, bunun nasıl biteceğini hepimiz çok iyi biliyoruz...

bkz. “Satranç tahtasındaki taneler sorunu”

"Yol"un koruyucusu

Özyineleme yolu boyunca karşılaştığımız tüm nesne tanımlayıcılarını, benzersiz bir "yol" olan bir diziye dönüşümlü olarak ekliyoruz:

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

Artılar: Eğer veride bir döngü söz konusu ise kesinlikle aynı kaydı aynı yol içerisinde tekrar tekrar işlemeyeceğiz.
eksileri: Ancak aynı zamanda kendimizi tekrar etmeden tüm kayıtları kelimenin tam anlamıyla atlayabiliriz.

bkz. "Şövalyenin Hareket Sorunu"

Yol Uzunluğu Sınırı

Anlaşılmaz bir derinlikte yinelemenin “dolaşması” durumundan kaçınmak için önceki iki yöntemi birleştirebiliriz. Veya gereksiz alanları desteklemek istemiyorsak, özyinelemeye devam etme koşulunu yol uzunluğu tahminiyle tamamlayın:

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
)

Zevkinize göre bir yöntem seçin!

Kaynak: habr.com

PostgreSQL Antipatterns: “Sonsuzluk sınır değildir!” veya Özyineleme hakkında biraz