การหลีกเลี่ยงลูปไม่รู้จบ

ปัญหาวงวนไม่สิ้นสุด

เมื่อใดวงวนจึงไม่สิ้นสุด

-- Simple recursive CTE that WOULD loop forever
-- (do NOT run this as-is; illustration only)
WITH RECURSIVE counter AS (
  SELECT 1 AS n          -- base case
  UNION ALL
  SELECT n + 1           -- recursive term
  FROM counter
  -- no WHERE clause to stop it!
)
SELECT n FROM counter;

การเพิ่มขีดจำกัดความลึก

WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM counter
  WHERE n < 10       -- stop at depth 10
)
SELECT n FROM counter;

ขีดจำกัดความลึกในคำสั่งสอบถามลำดับชั้น

CREATE TEMP TABLE employees (
  id   INT PRIMARY KEY,
  name TEXT,
  manager_id INT
);

INSERT INTO employees VALUES
  (1, 'Alice', NULL),
  (2, 'Bob',   1),
  (3, 'Carol', 2),
  (4, 'Dave',  3);

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL          -- root

  UNION ALL

  SELECT e.id, e.name, e.manager_id, h.depth + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.id
  WHERE h.depth < 5                 -- depth limit
)
SELECT id, name, depth FROM hierarchy ORDER BY depth, id;

การตรวจจับวงจรคืออะไร

CREATE TEMP TABLE edges (
  from_node INT,
  to_node   INT
);

-- Introduce a cycle: 1->2->3->1
INSERT INTO edges VALUES
  (1, 2),
  (2, 3),
  (3, 1),   -- cycle back to 1
  (1, 4);   -- also a non-cyclic branch

SELECT * FROM edges;

ติดตามโหนดที่เยี่ยมชมแล้วด้วยอาร์เรย์

WITH RECURSIVE traverse AS (
  -- Start from node 1
  SELECT from_node,
         to_node,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE NOT (e.from_node = ANY(t.visited))   -- skip visited nodes
)
SELECT from_node, to_node, visited
FROM traverse;

ส่วนคำสั่ง CYCLE (PostgreSQL 14+)

WITH RECURSIVE traverse AS (
  SELECT from_node, to_node
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node, e.to_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
)
CYCLE from_node SET is_cycle USING path
SELECT from_node, to_node, is_cycle, path
FROM traverse;

รวมขีดจำกัดความลึกและการตรวจจับวงจร

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10                           -- depth limit
    AND NOT (e.from_node = ANY(t.visited))     -- cycle guard
)
SELECT from_node, to_node, depth, visited
FROM traverse;

การสร้างเส้นทางเต็มเป็นข้อความ

WITH RECURSIVE traverse AS (
  SELECT from_node,
         to_node,
         1 AS depth,
         ARRAY[from_node] AS visited,
         from_node::TEXT AS path_str
  FROM edges
  WHERE from_node = 1

  UNION ALL

  SELECT e.from_node,
         e.to_node,
         t.depth + 1,
         t.visited || e.from_node,
         t.path_str || ' -> ' || e.from_node::TEXT
  FROM edges e
  JOIN traverse t ON e.from_node = t.to_node
  WHERE t.depth < 10
    AND NOT (e.from_node = ANY(t.visited))
)
SELECT from_node, to_node, path_str, depth
FROM traverse
ORDER BY depth;

การกำหนดจำนวนรอบการเรียกซ้ำสูงสุด

-- PostgreSQL: set a statement timeout as a safety net
SET statement_timeout = '5s';

-- Now any query that runs longer than 5 seconds is cancelled
WITH RECURSIVE counter AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 1000000
)
SELECT MAX(n) FROM counter;

-- Reset to default when done
SET statement_timeout = '0';

การเลือกขีดจำกัดความลึกที่เหมาะสม

-- Example: org chart with a generous but safe depth cap
WITH RECURSIVE org AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, o.depth + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
  WHERE o.depth < 20    -- realistic upper bound for an org chart
)
SELECT id, name, depth
FROM org
ORDER BY depth, name;

ขีดจำกัดความลึกเทียบกับการตรวจจับวงจร

ทบทวน: การทำให้คำสั่งสอบถามแบบเรียกซ้ำปลอดภัย