Mplan
13f7c1326a
- RESTful API: POST /heartbeat, POST /checkpoints, GET /status, GET /summaries - State-change-only checkpoint model with extensible StateType enum - PostgreSQL backend with sqlx, auto-migration on startup - pg_cron scheduled aggregation (state_summaries) and offline detection - Heartbeat-based liveness with 60s timeout auto-offline - LEAD() window function for state duration calculation - JSONB content field for extensible checkpoint metadata BREAKING CHANGE: Complete rewrite from Hello World to full API service.
66 lines
2.2 KiB
PL/PgSQL
66 lines
2.2 KiB
PL/PgSQL
-- 003_sessions.sql
|
|
-- 用户会话表 + 心跳管理 + 离线检测 + pg_cron 调度
|
|
|
|
-- ============================================================
|
|
-- 1. 用户会话表（跟踪当前状态和心跳）
|
|
-- ============================================================
|
|
CREATE TABLE IF NOT EXISTS user_sessions (
|
|
user_id VARCHAR(128) PRIMARY KEY,
|
|
current_state VARCHAR(64) NOT NULL DEFAULT 'Offline',
|
|
last_heartbeat BIGINT NOT NULL,
|
|
last_state_change BIGINT NOT NULL,
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- ============================================================
|
|
-- 2. 离线检测函数：心跳超时 60s 的用户自动补一条 Offline 检查点
|
|
-- pg_cron 每 1 分钟执行一次
|
|
-- ============================================================
|
|
CREATE OR REPLACE FUNCTION detect_offline_users(
|
|
timeout_secs BIGINT DEFAULT 60
|
|
) RETURNS SETOF BIGINT AS $$
|
|
DECLARE
|
|
r RECORD;
|
|
now_ts BIGINT;
|
|
BEGIN
|
|
now_ts := EXTRACT(EPOCH FROM now())::BIGINT;
|
|
|
|
FOR r IN
|
|
SELECT user_id, last_heartbeat
|
|
FROM user_sessions
|
|
WHERE current_state != 'Offline'
|
|
AND (now_ts - last_heartbeat) > timeout_secs
|
|
LOOP
|
|
-- 插入离线检查点（时间戳 = 最后心跳 + 超时时间）
|
|
INSERT INTO checkpoints (user_id, state, timestamp)
|
|
VALUES (r.user_id, 'Offline', r.last_heartbeat + timeout_secs);
|
|
|
|
-- 更新会话状态
|
|
UPDATE user_sessions
|
|
SET current_state = 'Offline',
|
|
last_state_change = r.last_heartbeat + timeout_secs,
|
|
updated_at = now()
|
|
WHERE user_id = r.user_id;
|
|
|
|
RETURN NEXT 1;
|
|
END LOOP;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- ============================================================
|
|
-- 3. pg_cron 调度（需要超级用户手动执行一次）
|
|
-- ============================================================
|
|
--
|
|
-- -- 安装扩展（仅需一次）
|
|
-- CREATE EXTENSION IF NOT EXISTS pg_cron;
|
|
--
|
|
-- -- 每 1 分钟检测离线用户
|
|
-- SELECT cron.schedule(
|
|
-- 'detect-offline',
|
|
-- '* * * * *',
|
|
-- $$ SELECT detect_offline_users(60); $$
|
|
-- );
|
|
--
|
|
-- -- 查看状态
|
|
-- SELECT * FROM cron.job;
|