如何共享PostgreSQL长事务数据或监控其运行进度？

由于整个验证流程在单个事务中执行，默认情况下外部会话无法看到未提交的验证结果行，但可以通过以下几种方案监控进度：

通用核心思路 #

借助独立的进度存储（专用进度表、临时表）或数据库系统视图，在存储过程执行过程中主动记录/暴露进度数据，外部会话通过特定方式读取这些数据。

1. 专用进度表方案（适用于所有主流数据库） #

提前创建一个用于记录操作进度的表，在存储过程的关键执行节点（比如批量处理的循环内）更新已处理的行数，外部会话通过调整隔离级别或读未提交的方式读取进度。

示例（以SQL Server为例）

-- 提前创建进度表（只需创建一次）
CREATE TABLE OperationProgress (
    SessionID INT PRIMARY KEY,
    ProcessedRows BIGINT NOT NULL,
    LastUpdate DATETIME NOT NULL DEFAULT GETDATE()
)

-- 存储过程内的进度更新逻辑
CREATE PROCEDURE RunValidation
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION;

    DECLARE @ProcessedRows BIGINT = 0;
    -- 初始化当前会话的进度记录
    INSERT INTO OperationProgress (SessionID, ProcessedRows)
    VALUES (@@SPID, 0);

    -- 批量处理验证逻辑
    WHILE EXISTS (SELECT 1 FROM SourceData WHERE IsValidated = 0)
    BEGIN
        -- 批量插入验证结果
        INSERT INTO ValidationResults (Col1, Col2, IsValid)
        SELECT Col1, Col2, dbo.ValidateRow(Col1, Col2)
        FROM SourceData 
        WHERE IsValidated = 0
        TOP 1000; -- 每次处理1000条

        -- 更新已处理行数
        SET @ProcessedRows = @ProcessedRows + @@ROWCOUNT;
        UPDATE OperationProgress
        SET ProcessedRows = @ProcessedRows, LastUpdate = GETDATE()
        WHERE SessionID = @@SPID;

        -- 标记已处理的源数据
        UPDATE SourceData SET IsValidated = 1
        WHERE Col1 IN (SELECT Col1 FROM ValidationResults WHERE IsValidated = 0);
    END

    COMMIT TRANSACTION;
    -- 清理当前会话的进度记录
    DELETE FROM OperationProgress WHERE SessionID = @@SPID;
END

外部会话查询进度：

-- 使用NOLOCK读取未提交的进度数据
SELECT ProcessedRows, LastUpdate
FROM OperationProgress WITH (NOLOCK)
WHERE SessionID = <目标会话ID> -- 可通过sys.dm_exec_requests找到对应会话ID

不同数据库的注意事项

• MySQL：外部会话需设置SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;才能读到未提交的进度更新，或者进度表使用MyISAM引擎（不支持事务，更新即时可见）。
• PostgreSQL：可在存储过程中使用RAISE NOTICE '已处理 % 行', @ProcessedRows;输出进度，外部客户端通过捕获NOTICE消息监控；若用进度表，需结合READ UNCOMMITTED隔离级别或使用UNLOGGED表。

2. 利用数据库系统视图监控 #

部分数据库提供系统视图可查看长运行任务的执行状态：

• SQL Server：查询sys.dm_exec_requests，找到对应会话的percent_complete列（仅对备份、索引重建等特定操作有效，普通DML操作可能无法返回准确值），同时可通过sys.dm_exec_sql_text查看当前执行的SQL片段。
• MySQL：查询INFORMATION_SCHEMA.PROCESSLIST，找到存储过程的执行会话，通过Time列查看已运行时长，Info列查看当前执行的语句。
• PostgreSQL：查询pg_stat_activity，查看会话的state（如active）、query字段，以及wait_event判断是否在等待资源。

注意事项 #

• 进度更新的频率要合理：避免每处理1条就更新一次，建议每处理1000-10000条更新一次，减少事务开销。
• 进度表的清理：事务完成后要及时删除对应会话的进度记录，避免表数据冗余。
• 不要在进度表操作中引入额外事务：所有进度更新要包含在主事务内，避免破坏验证流程的原子性。

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