PostgreSQL通用跨表触发器及LISTEN/NOTIFY方案可行性咨询

嗨，你的思路完全正确！PostgreSQL的LISTEN/NOTIFY机制就是专门用来实现数据库内部事件通知的，非常适合你这种需要监听所有表事务操作的场景，完全可以实现你的需求。

下面给你一步步演示具体的实现方案：

1. 创建通用触发器函数 #

首先我们要写一个通用的PL/pgSQL函数，它会在触发器触发时自动收集操作类型、表名、变更数据等信息，打包成JSON格式的payload，然后发送到指定的通知频道：

CREATE OR REPLACE FUNCTION notify_table_changes()
RETURNS TRIGGER AS $$
DECLARE
    payload JSON;
BEGIN
    -- 根据不同的操作类型组装通知内容
    CASE TG_OP
        WHEN 'INSERT' THEN
            payload = json_build_object(
                'operation', TG_OP,
                'schema', TG_TABLE_SCHEMA,
                'table', TG_TABLE_NAME,
                'timestamp', now(),
                'new_data', row_to_json(NEW)
            );
        WHEN 'UPDATE' THEN
            payload = json_build_object(
                'operation', TG_OP,
                'schema', TG_TABLE_SCHEMA,
                'table', TG_TABLE_NAME,
                'timestamp', now(),
                'old_data', row_to_json(OLD),
                'new_data', row_to_json(NEW)
            );
        WHEN 'DELETE' THEN
            payload = json_build_object(
                'operation', TG_OP,
                'schema', TG_TABLE_SCHEMA,
                'table', TG_TABLE_NAME,
                'timestamp', now(),
                'old_data', row_to_json(OLD)
            );
    END CASE;

    -- 发送通知到名为`table_changes`的频道
    PERFORM pg_notify('table_changes', payload::TEXT);

    -- 返回对应数据，不影响原事务的执行
    RETURN CASE TG_OP WHEN 'DELETE' THEN OLD ELSE NEW END;
END;
$$ LANGUAGE plpgsql;

2. 给单个表绑定触发器 #

如果只是给特定表添加监听，比如public.users表，直接创建触发器即可：

CREATE TRIGGER users_change_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.users
FOR EACH ROW EXECUTE FUNCTION notify_table_changes();

3. 批量给所有表创建触发器 #

如果你需要监听所有自定义表（排除系统表），可以用动态SQL批量生成触发器：

CREATE OR REPLACE FUNCTION create_all_table_triggers()
RETURNS VOID AS $$
DECLARE
    table_rec RECORD;
BEGIN
    -- 遍历所有非系统的基础表
    FOR table_rec IN 
        SELECT table_schema, table_name
        FROM information_schema.tables
        WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
          AND table_type = 'BASE TABLE'
    LOOP
        -- 动态生成触发器创建语句
        EXECUTE format(
            'CREATE TRIGGER %I_change_trigger
             AFTER INSERT OR UPDATE OR DELETE ON %I.%I
             FOR EACH ROW EXECUTE FUNCTION notify_table_changes();',
            table_rec.table_name, table_rec.table_schema, table_rec.table_name
        );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- 执行函数，给所有符合条件的表添加触发器
SELECT create_all_table_triggers();

4. 监听通知频道 #

在客户端（比如psql终端、你的应用程序代码里）执行以下命令订阅频道，就能实时接收通知了：

LISTEN table_changes;

比如在psql里，当有表发生变更时，你会收到类似这样的异步通知：

Asynchronous notification "table_changes" with payload "{\"operation\": \"INSERT\", \"schema\": \"public\", \"table\": \"users\", \"timestamp\": \"2024-05-20T15:30:00.123456+08:00\", \"new_data\": {\"id\": 1, \"username\": \"test_user\"}}" received from server process with PID 12345.

几个关键注意点 #

• NOTIFY是异步非阻塞的，不会影响原事务的执行性能，只会在事务提交后发送通知（如果事务回滚，通知也会被取消，符合事务一致性）
• 你可以根据需求自定义JSON payload的内容，比如只保留关键字段、添加事务ID等
• 频道名可以自由修改，只要保证LISTEN的频道名和pg_notify里的一致就行

总结一下：你的方案完全可行，而且这也是PostgreSQL实现这类需求的标准做法，放心用就好！

备注：内容来源于stack exchange，提问作者mostafa hosseini