postgresql - 为使用批量插入的表使用插入时触发更新旧记录

Question

我必须更新大约 28 天前的记录:我需要从记录中删除一些数据，而不实际删除记录本身。我决定使用触发器来执行此操作，如下所示:

CREATE
    FUNCTION my_table_update_old_data() 
    RETURNS TRIGGER LANGUAGE plpgsql AS 
    $$ 
        BEGIN 
            UPDATE my_table
            SET identity = NULL, comment = NULL
            WHERE
                created_at < NOW()- INTERVAL '28 DAYS';
        RETURN NEW;
        END;
    $$;

CREATE
    TRIGGER my_table_update_old_data_trigger 
    AFTER INSERT ON my_table
    EXECUTE PROCEDURE my_table_update_old_data();

这工作正常(在我的测试中)但是我对 AFTER INSERT 机制有一些担忧。有问题的表 my_table 每小时更新一次；记录是从另一个临时表中批量插入的，就像这样(简化):

INSERT INTO my_table
    SELECT DISTINCT ON (1)
        temp.table.id 
        temp_table.identity,
        temp_table.comment
    FROM temp_table
ON CONFLICT DO NOTHING;

触发器是在批量插入之后执行一次，还是在插入每条记录时执行一次？这种方法还有其他问题吗？

Answer 1

来自 the documentation:

FOR EACH ROW FOR EACH STATEMENT

This specifies whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default.

触发器声明中没有FOR EACH子句，所以默认是FOR EACH STATEMENT。

您应该向 UPDATE 语句添加一个附加条件，这样它就不会更新已经为空的行:

        UPDATE my_table
        SET identity = NULL, comment = NULL
        WHERE
            created_at < NOW()- INTERVAL '28 DAYS'
        AND
            identity IS NOT NULL;