PostgreSQL中能否通过pg_stat_activity视图触发器追踪用户最后连接时间？

不能直接在pg_stat_activity视图上创建触发器实现这个需求，原因如下：

• pg_stat_activity是PostgreSQL的只读系统视图，其数据是动态实时生成的，并非存储在磁盘的物理表中；
• PostgreSQL不允许在这类系统视图上创建触发器，视图本身也无法触发INSERT/UPDATE/DELETE等DML事件。

可行的替代方案 #

要实现用户最后连接时间的追踪，推荐以下两种方法：

1. 实时追踪：利用会话事件触发器（PostgreSQL 14+）

通过session_start事件触发器，在用户建立会话时实时更新连接信息表：

首先创建存储追踪数据的表：

CREATE TABLE user_connection_tracking (
    username text PRIMARY KEY,
    last_connection_time timestamptz NOT NULL,
    last_client_addr inet,
    last_application_name text
);

然后创建处理连接事件的函数和触发器：

-- 创建会话连接处理函数
CREATE OR REPLACE FUNCTION track_user_connection()
RETURNS event_trigger AS $$
BEGIN
    INSERT INTO user_connection_tracking (username, last_connection_time, last_client_addr, last_application_name)
    SELECT current_user, now(), inet_client_addr(), current_setting('application_name', true)
    ON CONFLICT (username) DO UPDATE
    SET last_connection_time = EXCLUDED.last_connection_time,
        last_client_addr = EXCLUDED.last_client_addr,
        last_application_name = EXCLUDED.last_application_name;
END;
$$ LANGUAGE plpgsql;

-- 创建会话开始事件触发器
CREATE EVENT TRIGGER track_user_session_start
ON session_start
EXECUTE FUNCTION track_user_connection();

如果需要追踪用户断开连接的时间，还可以创建session_end事件触发器来更新对应字段。

2. 定时同步：利用pg_cron定时查询pg_stat_activity

如果无法使用事件触发器（比如PostgreSQL版本低于14），可以通过定时任务定期从pg_stat_activity同步数据：

先安装pg_cron扩展（需超级用户权限）：

CREATE EXTENSION IF NOT EXISTS pg_cron;

创建定时任务（每分钟同步一次活跃用户的连接信息）：

SELECT cron.schedule(
    'sync-user-connection-data',
    '* * * * *',
    $$
        INSERT INTO user_connection_tracking (username, last_connection_time, last_client_addr, last_application_name)
        SELECT usename, now(), client_addr, application_name
        FROM pg_stat_activity
        WHERE usename IS NOT NULL AND state = 'active'
        ON CONFLICT (username) DO UPDATE
        SET last_connection_time = EXCLUDED.last_connection_time,
            last_client_addr = EXCLUDED.last_client_addr,
            last_application_name = EXCLUDED.last_application_name;
    $$
);

注意事项 #

• 两种方法都需要超级用户权限来创建触发器、扩展或定时任务；
• 若要追踪所有用户（包括超级用户），需确保查询或触发器能访问对应会话数据；
• 定时同步方案存在一定延迟，适合对实时性要求不高的场景。

内容的提问来源于stack exchange，提问作者Raju