如何在PGSQL中批量捕获所有表的最后修改时间（无需逐表触发器）

问题背景 #

需要捕获所有表的INSERT/UPDATE/DELETE操作的修改时间，希望避免为每个表手动编写数据触发器，尝试过基于table_rewrite的事件触发器，但只能捕获DDL变更，无法覆盖DML操作。

核心结论 #

PostgreSQL的事件触发器仅支持DDL事件（如CREATE/ALTER/DROP等），不支持捕获DML（INSERT/UPDATE/DELETE）操作，因此无法通过全局事件触发器直接实现DML修改时间的捕获。不过可以通过以下两种方案实现需求：

方案1：利用系统统计视图（轻量但近似） #

PostgreSQL自带的pg_stat_user_tables视图包含了表的统计信息，可近似获取表的最后活跃时间：

SELECT 
    schemaname, 
    relname AS table_name,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    last_autovacuum,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

• 注意：该视图的统计信息由autovacuum或手动ANALYZE更新，存在一定延迟，无法精确到每次DML操作的时间，适合仅需近似最后修改时间的场景。

方案2：批量创建通用触发器（精确捕获） #

通过动态SQL批量为所有用户表创建统一的DML触发器，同时结合DDL事件触发器自动为新表添加触发器，实现“一劳永逸”的效果：

步骤1：创建修改日志表

用于存储所有表的DML操作记录：

CREATE TABLE table_modification_log (
    log_id SERIAL PRIMARY KEY,
    schema_name TEXT NOT NULL,
    table_name TEXT NOT NULL,
    operation_type TEXT NOT NULL CHECK (operation_type IN ('INSERT', 'UPDATE', 'DELETE')),
    modification_time TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    query_text TEXT -- 可选：记录触发DML的SQL语句
);

步骤2：创建通用触发器函数

所有表的触发器将调用这个统一函数写入日志：

CREATE OR REPLACE FUNCTION log_table_modification()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO table_modification_log (schema_name, table_name, operation_type, query_text)
    VALUES (TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP, current_query());
    -- 针对行级触发器：INSERT返回NEW，UPDATE返回NEW，DELETE返回OLD；这里用语句级触发器，返回NULL不影响
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

步骤3：批量为现有表创建触发器

编写函数遍历所有用户表，自动创建触发器：

CREATE OR REPLACE FUNCTION create_all_table_triggers()
RETURNS VOID AS $$
DECLARE
    rec RECORD;
BEGIN
    -- 排除系统表和信息模式表
    FOR rec IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema') LOOP
        EXECUTE format(
            'CREATE TRIGGER trigger_%I_%I_modification
             AFTER INSERT OR UPDATE OR DELETE ON %I.%I
             FOR EACH STATEMENT EXECUTE FUNCTION log_table_modification();',
            rec.schemaname, rec.tablename, rec.schemaname, rec.tablename
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行批量创建
SELECT create_all_table_triggers();

步骤4：自动为新表添加触发器

通过DDL事件触发器，在创建新表时自动绑定日志触发器：

CREATE OR REPLACE FUNCTION add_trigger_to_new_table()
RETURNS EVENT_TRIGGER AS $$
DECLARE
    obj RECORD;
BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() WHERE command_tag = 'CREATE TABLE' LOOP
        EXECUTE format(
            'CREATE TRIGGER trigger_%I_%I_modification
             AFTER INSERT OR UPDATE OR DELETE ON %I.%I
             FOR EACH STATEMENT EXECUTE FUNCTION log_table_modification();',
            obj.schema_name, obj.object_name, obj.schema_name, obj.object_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE EVENT TRIGGER add_trigger_on_table_create
ON ddl_command_end
WHEN TAG IN ('CREATE TABLE')
EXECUTE FUNCTION add_trigger_to_new_table();

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