通知计数触发器异常：批量标记已读时未读数递减结果不符预期

这个问题的本质是触发器内的计数器更新写法不符合PostgreSQL的MVCC快照规则，具体表现为：

• 你当前的计数器更新逻辑基于“先查当前值、算出差值、再覆盖回写”的非原子模式，对查询时的数据快照依赖极强。但PostgreSQL执行单条批量UPDATE语句时，整条语句会基于启动那一刻生成的全局数据快照，同语句内所有行级触发器，不管是BEFORE还是AFTER类型，都无法读到同语句内其他触发器对计数器表写入的中间值。
• 对应你批量标记600条通知已读的场景：600次触发器触发计数器更新时，大部分操作读到的unread值都是语句启动前的初始值2000，计算后都会把unread设置成1999，不存在逐行累加递减的效果。只有少部分触发操作因为行锁等待重新读取了最新的计数器值，最后累计下来只减少了100，就出现了初始2000、最后1900的异常结果。
  你之前把触发器从AFTER改成BEFORE没有效果，就是因为两种行级触发器在同一个批量SQL的执行上下文中，遵循完全相同的快照可见性规则，从根本上解决不了读到过期值的问题。

直接把计数器的更新逻辑改成无嵌套子查询的原子自增/自减写法，不要在触发器里嵌套查询计数器表的值：

CREATE OR REPLACE FUNCTION notifications.notify_seen_upd()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
  begin 
      if new.seen = true and old.seen = false then 
        -- 直接基于当前行的unread值做减法，不需要嵌套子查询读值
        update notifications.notifications_counter 
            set unread = unread - 1
            where notifications_counter.user_id = new.user_id;
      end if;
      if new.seen = false and old.seen = true then
        -- 直接基于当前行的unread值做加法，不需要嵌套子查询读值
        update notifications.notifications_counter 
            set unread = unread + 1
            where notifications_counter.user_id = new.user_id;
      end if;
  return new;        
  end;
$function$;

这种写法下，所有针对计数器表同一行的更新操作，会通过行锁串行化执行，每次拿到行锁后，都会基于当前行的最新值直接计算更新，完全不受语句初始快照的限制，600条通知触发的递减操作会准确累计，最终得到正确的unread值。

如果经常会一次更新成百上千条通知的seen状态，行级触发器会触发成百上千次对计数器表的单行更新，性能开销很高。可以将行级触发器调整为语句级触发器，在整条UPDATE语句执行完成后，一次性统计本次更新中从seen=false变为seen=true的行数，按用户维度批量更新计数器值，减少重复的行锁开销。

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