PostgreSQL更新布尔值的触发器失效而同逻辑整数触发器正常如何解决？

问题原因 #

你遇到的问题核心有2个：

1. 空间判断逻辑写反（两个触发器都存在该问题，status触发器正常是测试巧合）
   PostGIS的ST_Contains(A, B)函数的判断逻辑是「几何A完全包含几何B」时返回真，你现在的写法是ST_Contains(a.geom, ST_Centroid(new.geom))，也就是判断lowlevel表的点是否包含toplevel多边形的重心，只有两个点完全重合时才会命中，完全不符合你「lowlevel点属于toplevel多边形范围」的需求。
2. flag触发器触发条件不完善
   你当前只判断了new.flag is false，没有过滤旧值状态，虽然不会触发错误，但如果原flag已经是false时重复执行更新，会产生无效的触发器调用；极端场景下如果存在其他修改flag字段的逻辑，也可能出现非预期触发。
   另外你当前使用BEFORE触发器用于数据同步也不合理，BEFORE触发器的设计用途是修改即将写入本表的NEW值，跨表同步的逻辑更适合用AFTER触发器，避免和其他修改本表字段的触发器产生逻辑冲突。

修复方案 #

1. 先修改status触发器及函数（修正空间判断、改为AFTER触发器） #

-- 先删除旧的触发器和函数
DROP TRIGGER IF EXISTS status_update ON toplevel_table;
DROP FUNCTION IF EXISTS status_update();

-- 新建函数
CREATE OR REPLACE FUNCTION status_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN  
    IF NEW.status > OLD.status THEN 
        UPDATE lowlevel_table a 
        SET status = NEW.status 
        WHERE a.status < NEW.status  
        -- 修正空间判断：判断lowlevel的点是否在toplevel的多边形范围内
        AND ST_Contains(NEW.geom, a.geom);
    END IF;
    RETURN NEW;                                                                    
END;
$function$;

-- 新建AFTER触发器
CREATE TRIGGER status_update AFTER
UPDATE OF status ON toplevel_table 
FOR EACH ROW
WHEN (NEW.status > OLD.status) 
EXECUTE FUNCTION status_update();

2. 修改flag触发器及函数（修正空间判断、完善触发条件、改为AFTER触发器） #

-- 先删除旧的触发器和函数
DROP TRIGGER IF EXISTS flag_update ON toplevel_table;
DROP FUNCTION IF EXISTS flag_update();

-- 新建函数
CREATE OR REPLACE FUNCTION flag_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN  
    UPDATE lowlevel_table a 
    SET flag = NEW.flag 
    WHERE a.flag IS TRUE 
    -- 修正空间判断
    AND ST_Contains(NEW.geom, a.geom);
    RETURN NEW;                                                                    
END;
$function$;

-- 新建AFTER触发器，完善触发条件：只有flag从true改为false时才触发
CREATE TRIGGER flag_update AFTER
UPDATE OF flag ON toplevel_table 
FOR EACH ROW
WHEN (OLD.flag IS TRUE AND NEW.flag IS FALSE) 
EXECUTE FUNCTION flag_update();

3. 验证逻辑 #

执行你的更新语句测试即可：

UPDATE toplevel_table SET status = 5 WHERE id = 你的测试id;
UPDATE toplevel_table SET flag = false WHERE id = 你的测试id;

如果需要调试可以在函数中加入RAISE NOTICE语句输出参数值，确认触发器是否正常触发。

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