PostgreSQL 13触发器函数内查询返回NULL但单独执行正常是什么原因？

核心问题表现 #

触发器函数内执行SELECT语句为_srid_puwg2000赋值后得到NULL，但相同语句单独执行可以返回正确结果。

可能原因 #

• 触发时机与数据可见性不匹配
  这是该场景下最常见的原因：如果触发器绑定在geodesies表的BEFORE INSERT/UPDATE事件上，触发器执行时本次操作的新行还未正式写入geodesies表，查询语句中g.well_id = NEW.well_id的条件无法匹配到未提交的新行，因此返回空结果。单独执行查询时，目标行已经写入表中，所以可以正常返回。
• 空间查询精度匹配失败
  ST_Contains对几何对象的精度要求极高，如果NEW记录中的geom_wgs84值和单独查询时使用的几何值存在浮点精度差异，就会导致空间匹配失败，查询不到locations表的对应记录。
• 查询返回多行导致赋值异常
  当查询匹配到多条符合条件的记录时，SELECT ... INTO语句在PL/pgSQL中会抛出错误，但如果配置了忽略该类异常，变量也会被赋值为NULL。
• 事务隔离级别影响
  如果当前事务隔离级别设置为REPEATABLE READ及以上，且locations或geodesies表的对应记录是在当前事务外刚提交的，事务内的查询可能看不到最新修改的记录。

解决方案 #

1. 调整触发器触发时机：如果触发器绑定在geodesies表，将触发事件从BEFORE INSERT/UPDATE修改为AFTER INSERT/UPDATE，确保查询时新行已经写入表中。
2. 优化查询逻辑，增加异常判断：

-- 改用显式JOIN，增加LIMIT 1和结果存在性判断
SELECT l.srid_puwg2000 INTO _srid_puwg2000 
FROM geodesies g
INNER JOIN locations l ON ST_Contains(l.geom_wgs84, g.geom_wgs84)
WHERE g.well_id = NEW.well_id 
  AND l.ancestry_depth = 1
LIMIT 1;

-- 主动判断查询是否命中结果
IF NOT FOUND THEN
  RAISE EXCEPTION 'well_id: % 未匹配到对应的srid_puwg2000', NEW.well_id;
END IF;

3. 优化空间匹配逻辑，增加容差处理：
   如果存在精度问题，可以改用ST_DWithin设置合理容差代替严格的ST_Contains匹配，示例如下：

-- 容差设置为0.00001度，对应WGS84坐标系下约1米的误差范围
INNER JOIN locations l ON ST_DWithin(l.geom_wgs84, g.geom_wgs84, 0.00001)

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