-- 开始事务
BEGIN;
-- 或
START TRANSACTION;

-- 执行操作
INSERT INTO accounts (user_id, balance) VALUES (1, 1000);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置事务特性
BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN READ ONLY;
BEGIN ISOLATION LEVEL READ COMMITTED READ WRITE;

-- 保存点（Savepoint）
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Bob');
-- 发现错误，回滚到保存点
ROLLBACK TO SAVEPOINT sp1;
-- Bob 的插入被回滚，Alice 保留
INSERT INTO users (name) VALUES ('Charlie');
COMMIT;

-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 基本用法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 显示实际执行信息
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- 完整选项
EXPLAIN (
    ANALYZE,      -- 实际执行并显示统计
    BUFFERS,      -- 显示缓冲区使用情况
    COSTS,        -- 显示成本估算（默认开启）
    TIMING,       -- 显示实际时间
    FORMAT TEXT   -- 输出格式：TEXT, XML, JSON, YAML
) SELECT * FROM users WHERE id = 1;

-- 不实际执行（只看计划）
EXPLAIN (COSTS, VERBOSE) SELECT * FROM users WHERE id = 1;

-- 基本语法
function_name(args) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression [ASC|DESC] [NULLS {FIRST|LAST}]]
    [frame_clause]
)

-- 窗口帧子句
ROWS BETWEEN frame_start AND frame_end
RANGE BETWEEN frame_start AND frame_end
GROUPS BETWEEN frame_start AND frame_end

-- frame_start / frame_end 选项
UNBOUNDED PRECEDING   -- 分区开始
N PRECEDING           -- 当前行前 N 行
CURRENT ROW          -- 当前行
N FOLLOWING          -- 当前行后 N 行
UNBOUNDED FOLLOWING  -- 分区结束

-- 默认窗口帧
-- 有 ORDER BY: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- 无 ORDER BY: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- 函数基本结构
CREATE [OR REPLACE] FUNCTION function_name(
    param1 data_type,
    param2 data_type DEFAULT default_value
)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
    -- 变量声明
    variable_name data_type;
BEGIN
    -- 函数体
    RETURN result;
END;
$$;

-- 存储过程（PostgreSQL 11+）
CREATE [OR REPLACE] PROCEDURE procedure_name(
    param1 data_type,
    INOUT param2 data_type
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- 过程体
END;
$$;

-- 调用函数
SELECT function_name(arg1, arg2);

-- 调用存储过程
CALL procedure_name(arg1, arg2);

-- 1. 检查系统整体状态
SELECT
    numbackends AS connections,
    xact_commit AS commits,
    xact_rollback AS rollbacks,
    blks_read AS disk_reads,
    blks_hit AS cache_hits,
    round(100.0 * blks_hit / NULLIF(blks_read + blks_hit, 0), 2) AS cache_hit_ratio,
    tup_returned AS rows_returned,
    tup_fetched AS rows_fetched,
    tup_inserted AS rows_inserted,
    tup_updated AS rows_updated,
    tup_deleted AS rows_deleted
FROM pg_stat_database
WHERE datname = current_database();

-- 2. 检查活动连接
SELECT
    state,
    COUNT(*) AS count,
    MAX(NOW() - query_start) AS max_duration
FROM pg_stat_activity
GROUP BY state;

-- 3. 检查慢查询
SELECT
    pid,
    NOW() - query_start AS duration,
    query,
    state
FROM pg_stat_activity
WHERE state != 'idle'
AND query_start < NOW() - INTERVAL '5 seconds'
ORDER BY duration DESC;

-- 4. 检查锁等待
SELECT
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON
    blocked_locks.locktype = blocking_locks.locktype
    AND blocked_locks.relation = blocking_locks.relation
    AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;