PostgreSQL受限用户下慢查询进度及完成状态监控实现咨询

问题背景 #

我们的PostgreSQL数据库存在一位权限受限的用户，该用户仅能查看pg_stat_activity表中的进程，但无法获取进程当前状态或正在执行的查询信息。需要运行一条耗时极长的删除慢查询，并提供REST API监控其进度（判断查询已完成还是仍在运行），计划通过进程ID（PID）在pg_stat_activity表中查询对应进程的方式实现。

REST API设计（中文翻译） #

• DELETE /products/{id}：执行耗时较长的删除操作，返回用于监控的操作ID，应用需持久化该操作对应的PostgreSQL进程PID
• GET /operations/{id}：通过关联的PID检查删除查询是否已完成

落地实现步骤 #

1. 慢查询执行与PID捕获 #

• 在DELETE /products/{id}接口逻辑中，启动删除查询前（或异步执行时），通过PostgreSQL内置函数pg_backend_pid()获取当前会话的PID；如果是通过连接池异步执行，需确保能捕获到对应查询进程的PID。
• 生成唯一操作ID（如UUID），将操作ID与PID的映射关系持久化到应用存储（如本地数据库、Redis缓存），确保后续查询能通过操作ID关联到PID。

2. 权限适配的进程状态判定 #

由于监控用户无法访问pg_stat_activity的state、query等字段，只能通过PID是否存在判断进程存活状态：

• 执行查询语句：

  SELECT pid FROM pg_stat_activity WHERE pid = :target_pid;
  

• 结果说明：
  • 返回非空结果：进程仍在运行，查询未完成
  • 返回空结果：进程已终止，查询完成（包括正常结束或异常终止）

3. API核心逻辑实现 #

DELETE /products/{id}

1. 生成全局唯一的操作ID
2. 以异步方式启动删除查询（避免接口长期阻塞）
3. 捕获该查询对应的PostgreSQL PID
4. 存储操作ID与PID的映射关系
5. 立即返回操作ID给客户端

GET /operations/{id}

1. 根据操作ID查询对应的PID
2. 使用权限受限的监控用户执行上述PID查询语句
3. 根据查询结果返回对应状态：
   • 存在PID：返回{"status": "running"}
   • 无PID记录：返回{"status": "completed"}
   • 操作ID不存在：返回{"status": "not_found"}

4. 细节优化与异常处理 #

• PID复用问题：PostgreSQL的PID可能被新进程复用，需在存储映射时额外记录查询启动时间，检查pg_stat_activity中对应PID的backend_start字段是否与记录的启动时间匹配，避免误判。
• 异常终止区分：如果需要区分查询正常完成和异常终止，需在异步执行逻辑中捕获查询异常，更新操作ID对应的状态标记，GET接口优先返回该标记状态。

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