使用 SQL 查詢停滯的後台作業

WITH started AS (
  SELECT
    job_id,
    job_name,
    queue_name,
    MIN(timestamp_utc) AS started_at
  FROM job_events
  WHERE event_name = 'job_started'
    AND timestamp_utc >= now() - INTERVAL '24 hours'
  GROUP BY job_id, job_name, queue_name
),
finished AS (
  SELECT
    job_id,
    MAX(timestamp_utc) AS finished_at
  FROM job_events
  WHERE event_name IN ('job_completed', 'job_failed')
    AND timestamp_utc >= now() - INTERVAL '24 hours'
  GROUP BY job_id
)
SELECT
  s.job_id,
  s.job_name,
  s.queue_name,
  s.started_at
FROM started s
LEFT JOIN finished f ON s.job_id = f.job_id
WHERE f.job_id IS NULL
  AND s.started_at < now() - INTERVAL '15 minutes'
ORDER BY s.started_at ASC;