PostgreSQL更新触发器异常：添加无用查询后结果恢复正确

1. 核心代码错误 #

你的问题并非PostgreSQL的bug，而是代码本身存在多处逻辑与语法错误：

• UPDATE语句别名错误：原代码中WHERE table2.id = user_av.id_2里的user_av是不存在的CTE别名，正确应该是avr.id_2。这个笔误会导致WHERE条件匹配失败，更新结果完全不符合预期。
• rows CTE逻辑冗余且错误：
  • 多余的DISTINCT会干扰窗口函数的排序逻辑，导致排名计算异常；
  • 从new_table LEFT JOIN table1的逻辑不合理——new_table仅需用来获取需要更新的id_2列表，而非作为关联主表来拉取table1的数据。

2. 为什么添加无意义SELECT后结果"正确"？ #

这是巧合：添加额外CTE后，PostgreSQL查询优化器的执行计划发生了变化，偶然绕过了部分错误逻辑，但这种行为完全不可靠，后续仍会出现数据异常。

3. 修复后的触发器代码 #

以下是优化后的实现，逻辑更清晰且保证数据准确性：

CREATE OR REPLACE FUNCTION public.update_table2_vec()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    WITH updated_id2 AS (
        -- 获取本次更新涉及的所有id_2，去重避免重复处理
        SELECT DISTINCT id_2 FROM new_table
    ),
    top10_rows AS (
        -- 针对每个id_2，取最新的10条记录（用ROW_NUMBER确保严格取前10）
        SELECT 
            t.id_2,
            t.vec,
            ROW_NUMBER() OVER (PARTITION BY t.id_2 ORDER BY t.date DESC) AS rn
        FROM table1 t
        JOIN updated_id2 u ON t.id_2 = u.id_2
    ),
    vector_elements AS (
        -- 展开向量并保留每个元素的位置序号
        SELECT 
            id_2,
            unnest(vec) AS val,
            ordinality AS pos
        FROM top10_rows
        WHERE rn <= 10
    ),
    avg_elements AS (
        -- 按id_2和元素位置计算平均值
        SELECT 
            id_2,
            pos,
            AVG(val) AS avg_val
        FROM vector_elements
        GROUP BY id_2, pos
        ORDER BY id_2, pos
    ),
    avg_vectors AS (
        -- 将平均值按位置重新聚合为向量
        SELECT 
            id_2,
            array_agg(avg_val ORDER BY pos) AS avg_vec
        FROM avg_elements
        GROUP BY id_2
    )
    -- 正确更新table2对应行的向量
    UPDATE table2 t2
    SET vec = av.avg_vec
    FROM avg_vectors av
    WHERE t2.id = av.id_2;

    RETURN NULL;
END;
$function$;

CREATE TRIGGER update_table2_trigger
AFTER UPDATE ON public.table1
REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT
EXECUTE FUNCTION public.update_table2_vec();

关键修复点： #

• 用updated_id2 CTE过滤需要更新的id_2，避免全表扫描，提升性能；
• 用ROW_NUMBER()替代DENSE_RANK()，确保严格取每个id_2的前10条记录（若存在相同date的记录，DENSE_RANK会返回相同排名，导致取到超过10条数据）；
• 修正了UPDATE语句的关联逻辑，确保table2.id与id_2正确匹配；
• 移除冗余的DISTINCT和错误的LEFT JOIN，保证数据计算的准确性。

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