PostgreSQL 9.5更新Features表触发Events多条记录插入问题

看起来你的问题出在触发器的触发逻辑和第三方软件的更新方式上——要么是触发器没判断字段是否真的发生变化，要么是软件对同一行执行了多次独立的UPDATE操作，导致行级触发器每次都触发，生成多条事件记录。下面分步骤给出优化方案：

1. 先排查触发器的基础配置 #

首先确认你是不是为name、lon、lat每个字段单独创建了触发器？如果是，那每次更新一个字段就会触发一次对应的触发器，自然会生成3条记录。这种情况直接删掉多余的触发器，只保留一个针对整个表的UPDATE触发器即可。

2. 仅在字段实际变化时插入事件 #

默认的行级触发器只要执行了UPDATE语句就会触发，哪怕你设置的字段值和原来完全一致（比如UPDATE features SET name = name WHERE id=1）。我们可以在触发器函数里对比OLD和NEW行的字段值，只有当监控的字段发生真实变化时才插入事件：

CREATE OR REPLACE FUNCTION log_feature_event()
RETURNS TRIGGER AS $$
BEGIN
    -- 仅当name、lon、lat中至少一个字段值变化时，才生成事件
    IF OLD.name IS DISTINCT FROM NEW.name 
       OR OLD.lon IS DISTINCT FROM NEW.lon 
       OR OLD.lat IS DISTINCT FROM NEW.lat THEN
        INSERT INTO events (name, date, feature_id)
        VALUES ('update', NOW(), NEW.id);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 确保触发器是针对整表的UPDATE操作，行级触发
CREATE TRIGGER features_update_trigger
AFTER UPDATE ON features
FOR EACH ROW
EXECUTE FUNCTION log_feature_event();

这个修改能过滤掉无意义的更新操作，避免生成不必要的事件记录。

3. 合并同一事务内的多次更新 #

如果第三方软件确实是对同一行执行了三次独立的UPDATE（比如分别修改name、lon、lat），那即使做了上面的优化，同一事务内还是会生成3条事件。这时候我们可以用临时表+事务提交触发的方式，把同一事务内同一feature的多次更新合并成一条记录：

-- 创建临时表，用于暂存事务内的更新事件，事务提交时自动合并插入到Events
CREATE TEMP TABLE IF NOT EXISTS temp_feature_events (
    feature_id INT PRIMARY KEY,
    event_date TIMESTAMP DEFAULT NOW()
) ON COMMIT DO INSERT INTO events (name, date, feature_id)
SELECT 'update', event_date, feature_id FROM temp_feature_events;

-- 修改触发器函数，把事件写入临时表（同一feature_id只保留一条）
CREATE OR REPLACE FUNCTION log_feature_event()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.name IS DISTINCT FROM NEW.name 
       OR OLD.lon IS DISTINCT FROM NEW.lon 
       OR OLD.lat IS DISTINCT FROM NEW.lat THEN
        -- 存在则更新时间戳，不存在则插入，确保同一feature在事务内只有一条记录
        INSERT INTO temp_feature_events (feature_id)
        VALUES (NEW.id)
        ON CONFLICT (feature_id) DO UPDATE SET event_date = NOW();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

原理是：临时表是会话级的，每个数据库连接都会有自己的临时表，不会互相干扰；当事务结束时，临时表会自动把里面的记录插入到正式的Events表，而且因为临时表用feature_id作为主键，同一事务内多次更新同一feature只会保留一条记录，最终只生成一条update事件。

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