# 启用语句执行时间记录
log_min_duration_statement = 1000  # 记录执行时间超过1000ms的语句

# 记录所有语句（可选，用于调试）
# log_statement = 'all'

# 记录语句执行计划（可选）
# auto_explain.log_min_duration = 1000
# auto_explain.log_analyze = on

# 推荐的log_line_prefix配置
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '

# 日志配置
logging_collector = on
log_destination = 'stderr'
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
deadlock_timeout = 1s

SELECT 
    datname AS database,
    usename AS username,
    application_name AS app_name,
    client_addr AS client_address,
    state,
    query_start AS start_time,
    query
FROM pg_stat_activity 
WHERE state = 'active';

SELECT 
    pid,
    datname AS database,
    usename AS username,
    application_name AS app_name,
    client_addr AS client_address,
    state,
    query_start AS start_time,
    now() - query_start AS duration,
    query
FROM pg_stat_activity 
WHERE state = 'active'
  AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;

SELECT 
    pid,
    datname AS database,
    usename AS username,
    state,
    wait_event_type,
    wait_event,
    query
FROM pg_stat_activity 
WHERE wait_event_type IS NOT NULL
  AND state = 'active';

-- 创建慢查询日志表
CREATE TABLE slow_query_log (
    id SERIAL PRIMARY KEY,
    log_time TIMESTAMP,
    username TEXT,
    database_name TEXT,
    duration INTERVAL,
    query_text TEXT,
    application_name TEXT,
    client_addr INET,
    collected_at TIMESTAMP DEFAULT NOW()
);

-- 创建函数解析慢查询日志并入库
CREATE OR REPLACE FUNCTION parse_slow_queries()
RETURNS void AS $$
BEGIN
    -- 这里可以使用外部工具如pgBadger解析日志文件
    -- 或者使用file_fdw外部表直接读取日志
    -- 简化示例：手动插入示例数据
    INSERT INTO slow_query_log 
    (log_time, username, database_name, duration, query_text, application_name, client_addr)
    SELECT 
        now() - (random() * 3600 * 24 || ' seconds')::interval,
        'user' || (random() * 10)::integer,
        'db' || (random() * 5)::integer,
        (random() * 10000 || ' milliseconds')::interval,
        'SELECT * FROM table_' || (random() * 20)::integer,
        'app_' || (random() * 3)::integer,
        '192.168.1.' || (random() * 255)::integer
    FROM generate_series(1, 100);
END;
$$ LANGUAGE plpgsql;

-- 1. 当前活动查询统计
SELECT 
    state,
    COUNT(*) as connection_count,
    COUNT(*) FILTER (WHERE now() - query_start > interval '1 minute') as long_running_count
FROM pg_stat_activity 
WHERE datname = current_database()
GROUP BY state;

-- 2. 按应用统计活动连接
SELECT 
    application_name,
    COUNT(*) as total_connections,
    COUNT(*) FILTER (WHERE state = 'active') as active_connections
FROM pg_stat_activity 
WHERE application_name IS NOT NULL
GROUP BY application_name
ORDER BY active_connections DESC;

-- 3. 识别可能的锁等待
SELECT 
    blocked.pid AS blocked_pid,
    blocked.query AS blocked_query,
    blocking.pid AS blocking_pid,
    blocking.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked ON blocked_locks.pid = blocked.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.GRANTED;