PostgreSQL 12.4生成列generated_status未实时更新问题求助

可能原因 #

1. PostgreSQL 12.4版本已知bug：旧版本的PostgreSQL在并发更新存储生成列依赖的多个字段时，存在生成列未正确使用最新字段值计算的情况。由于存储生成列在每行更新时触发计算，若多个事务先后更新不同依赖字段，可能出现计算逻辑读取旧值的异常。
2. 竞态条件下的更新顺序问题：当多个事务分别更新picked_count和packed_count时，若事务执行顺序导致生成列计算时，某个依赖字段的最新值未被正确读取（例如事务B更新packed_count后，事务A的picked_count更新覆盖了生成列的正确值），会出现最终状态不匹配的情况。
3. 函数稳定性标记的潜在影响：尽管calculate_status函数逻辑符合IMMUTABLE定义（纯输入依赖），但PostgreSQL对IMMUTABLE函数的优化在极端并发场景下可能引发缓存逻辑异常，不过该概率极低。

解决方案 #

1. 修复现有异常数据 #

对已存在的异常行强制触发生成列重新计算：

UPDATE table1 
SET placed_count = placed_count  -- 更新字段为原值，触发生成列重算
WHERE generated_status != calculate_status(placed_count, picked_count, packed_count);

2. 升级PostgreSQL版本 #

PostgreSQL 12系列后续版本（如12.5及以上）修复了多个并发更新相关的存储生成列问题，建议升级到当前12分支的最新稳定版本，从根源避免此类bug。

3. 优化更新逻辑 #

尽量在单个事务中完成相关字段的更新，减少并发更新同一行的场景：

-- 示例：同时更新picked_count和packed_count，确保生成列一次计算正确
UPDATE table1 
SET picked_count = 3, packed_count = 3 
WHERE id = 1;

4. 替代方案：改用视图计算状态 #

若存储生成列的一致性仍无法保障，可创建视图实时计算状态，避免存储值过期：

CREATE VIEW table1_with_status AS
SELECT 
    id,
    placed_count,
    picked_count,
    packed_count,
    calculate_status(placed_count, picked_count, packed_count) AS generated_status
FROM table1;

此方案的缺点是查询时需实时计算状态，适合对写入性能要求不高但状态一致性要求严格的场景。

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