PostgreSQL 事件触发器函数

概述

PostgreSQL 事件触发器函数是一组专门设计用于监控和响应数据库 DDL（数据定义语言）操作的特殊函数。这些函数为数据库管理员和开发人员提供了强大的工具，用于审计、监控和自动化数据库架构变更。

INFO

事件触发器的核心价值

事件触发器函数让我们能够在数据库架构发生变化时自动执行特定操作，这在企业级应用中对于：

变更审计和合规性监控
自动化部署流程
架构同步和复制
安全策略 enforcement

具有重要意义。

事件触发器工作流程

1. 在命令结束时捕获更改

1.1 函数概述

pg_event_trigger_ddl_commands() 函数专门用于捕获在 ddl_command_end 事件中执行的所有 DDL 命令信息。

函数签名：

sql

pg_event_trigger_ddl_commands() → setof record

WARNING

使用限制

此函数只能在附加到 ddl_command_end 事件触发器的函数中调用。在其他上下文中调用会引发错误。

1.2 返回列详解

列名	类型	描述	业务意义
`classid`	oid	对象所属目录的 OID	标识对象类型（表、索引、函数等）
`objid`	oid	对象本身的 OID	对象的唯一标识符
`objsubid`	integer	子对象 ID	用于列、约束等子对象的标识
`command_tag`	text	命令标签	DDL 命令类型（如 CREATE TABLE, ALTER INDEX）
`object_type`	text	对象的类型	人类可读的对象类型描述
`schema_name`	text	对象所属模式名称	用于命名空间管理
`object_identity`	text	对象完整标识	包含模式限定的完整对象名
`in_extension`	boolean	是否为扩展脚本命令	区分用户命令和扩展安装命令
`command`	pg_ddl_command	命令的内部表示	可传递给其他函数获取详细信息

1.3 实际应用场景：数据库变更审计系统

问题陈述： 企业需要监控生产数据库的所有架构变更，记录变更时间、用户、操作类型和影响的对象，以满足 SOX 合规要求。

解决方案：

创建审计表创建事件触发器函数注册事件触发器

sql

-- 创建审计日志表
CREATE TABLE schema_change_audit (
    id SERIAL PRIMARY KEY,
    change_time TIMESTAMP DEFAULT NOW(),
    user_name TEXT DEFAULT SESSION_USER,
    command_tag TEXT,
    object_type TEXT,
    schema_name TEXT,
    object_identity TEXT,
    in_extension BOOLEAN,
    raw_command TEXT
);

sql

-- 创建审计函数
CREATE OR REPLACE FUNCTION audit_ddl_commands()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
    cmd_record RECORD;
    cmd_count INTEGER := 0;
BEGIN
    -- 遍历所有执行的 DDL 命令
    FOR cmd_record IN
        SELECT * FROM pg_event_trigger_ddl_commands()
    LOOP
        -- 插入审计记录
        INSERT INTO schema_change_audit (
            command_tag,
            object_type,
            schema_name,
            object_identity,
            in_extension
        ) VALUES (
            cmd_record.command_tag,
            cmd_record.object_type,
            cmd_record.schema_name,
            cmd_record.object_identity,
            cmd_record.in_extension
        );

        cmd_count := cmd_count + 1;

        -- 记录详细信息到日志
        RAISE NOTICE 'DDL Audit: % executed on % (Schema: %)',
                     cmd_record.command_tag,
                     cmd_record.object_identity,
                     COALESCE(cmd_record.schema_name, 'N/A');
    END LOOP;

    -- 如果有多个命令，记录总数
    IF cmd_count > 1 THEN
        RAISE NOTICE 'Total DDL commands in this statement: %', cmd_count;
    END IF;
END;
$$;

sql

-- 创建事件触发器
CREATE EVENT TRIGGER ddl_audit_trigger
    ON ddl_command_end
    EXECUTE FUNCTION audit_ddl_commands();

测试示例和输出：

sql

-- 输入：创建一个包含多个对象的复杂表
CREATE TABLE public.orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    order_date DATE DEFAULT CURRENT_DATE,
    total_amount DECIMAL(10,2)
);

-- 预期输出（NOTICE 消息）：
-- NOTICE:  DDL Audit: CREATE SEQUENCE executed on public.orders_id_seq (Schema: public)
-- NOTICE:  DDL Audit: CREATE TABLE executed on public.orders (Schema: public)
-- NOTICE:  DDL Audit: ALTER SEQUENCE executed on public.orders_id_seq (Schema: public)
-- NOTICE:  Total DDL commands in this statement: 3

-- 审计表中的记录：
SELECT * FROM schema_change_audit ORDER BY id DESC LIMIT 3;

查询结果示例：

id	change_time	user_name	command_tag	object_type	schema_name	object_identity	in_extension
1	2024-01-15 10:30:15	postgres	CREATE SEQUENCE	sequence	public	public.orders_id_seq	false
2	2024-01-15 10:30:15	postgres	CREATE TABLE	table	public	public.orders	false
3	2024-01-15 10:30:15	postgres	ALTER SEQUENCE	sequence	public	public.orders_id_seq	false

分析过程：

单个 CREATE TABLE 语句实际触发了 3 个底层 DDL 命令
首先创建序列（用于 SERIAL 列）
然后创建表本身
最后修改序列的所有者关系
每个命令都被独立捕获和记录，提供了完整的变更轨迹

2. 处理由 DDL 命令删除的对象

2.1 函数概述

pg_event_trigger_dropped_objects() 函数用于获取在 sql_drop 事件中被删除的所有对象信息，包括由于依赖关系而被级联删除的对象。

函数签名：

sql

pg_event_trigger_dropped_objects() → setof record

2.2 返回列详解

列名	类型	描述	业务场景
`classid`	oid	对象所属目录的 OID	对象分类标识
`objid`	oid	对象本身的 OID	对象唯一标识
`objsubid`	integer	子对象 ID	列、约束等子对象标识
`original`	boolean	是否为原始删除对象	区分主动删除和级联删除
`normal`	boolean	是否为正常依赖删除	依赖关系分析
`is_temporary`	boolean	是否为临时对象	临时对象清理跟踪
`object_type`	text	对象类型	人类可读的类型信息
`schema_name`	text	模式名称	命名空间信息
`object_name`	text	对象名称	简单名称（非模式限定）
`object_identity`	text	完整对象标识	模式限定的完整名称
`address_names`	text[]	地址名称数组	用于远程重建对象地址
`address_args`	text[]	地址参数数组	配合 address_names 使用

2.3 实际应用场景：智能删除保护和恢复系统

问题陈述： 在开发环境中，开发人员经常误删重要对象，需要一个系统能够：

记录所有删除操作的详细信息
区分有意删除和意外的级联删除
为误删操作提供快速恢复建议

解决方案：

创建删除日志表智能删除监控函数注册删除监控事件触发器

sql

-- 创建删除操作日志表
CREATE TABLE deletion_audit (
    id SERIAL PRIMARY KEY,
    deletion_time TIMESTAMP DEFAULT NOW(),
    user_name TEXT DEFAULT SESSION_USER,
    session_id TEXT DEFAULT to_hex(pg_backend_pid()),
    command_tag TEXT DEFAULT TG_TAG,
    object_type TEXT,
    schema_name TEXT,
    object_name TEXT,
    object_identity TEXT,
    is_original_target BOOLEAN,
    is_cascade_deletion BOOLEAN,
    is_temporary BOOLEAN,
    recovery_info JSONB
);

sql

CREATE OR REPLACE FUNCTION monitor_object_deletions()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
    deleted_obj RECORD;
    original_count INTEGER := 0;
    cascade_count INTEGER := 0;
    important_objects TEXT[] := ARRAY[]::TEXT[];
BEGIN
    -- 遍历所有被删除的对象
    FOR deleted_obj IN
        SELECT * FROM pg_event_trigger_dropped_objects()
    LOOP
        -- 统计删除类型
        IF deleted_obj.original THEN
            original_count := original_count + 1;
        ELSE
            cascade_count := cascade_count + 1;
        END IF;

        -- 识别重要对象（生产表、视图、函数等）
        IF deleted_obj.object_type IN ('table', 'view', 'function', 'index')
           AND deleted_obj.schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_toast')
           AND NOT deleted_obj.is_temporary THEN
            important_objects := array_append(important_objects, deleted_obj.object_identity);
        END IF;

        -- 记录删除信息
        INSERT INTO deletion_audit (
            object_type,
            schema_name,
            object_name,
            object_identity,
            is_original_target,
            is_cascade_deletion,
            is_temporary,
            recovery_info
        ) VALUES (
            deleted_obj.object_type,
            deleted_obj.schema_name,
            deleted_obj.object_name,
            deleted_obj.object_identity,
            deleted_obj.original,
            NOT deleted_obj.normal,
            deleted_obj.is_temporary,
            jsonb_build_object(
                'address_names', deleted_obj.address_names,
                'address_args', deleted_obj.address_args,
                'classid', deleted_obj.classid,
                'objid', deleted_obj.objid
            )
        );

        -- 为重要对象发出警告
        IF deleted_obj.object_type IN ('table', 'view')
           AND deleted_obj.schema_name = 'public'
           AND NOT deleted_obj.is_temporary THEN
            RAISE WARNING 'Important object deleted: % (Type: %, Original: %)',
                         deleted_obj.object_identity,
                         deleted_obj.object_type,
                         deleted_obj.original;
        END IF;
    END LOOP;

    -- 删除摘要报告
    RAISE NOTICE 'Deletion Summary - Original: %, Cascade: %, Important Objects: %',
                 original_count,
                 cascade_count,
                 array_length(important_objects, 1);

    -- 如果删除了重要对象，发出详细警告
    IF array_length(important_objects, 1) > 0 THEN
        RAISE WARNING 'Important objects were deleted: %', array_to_string(important_objects, ', ');
        RAISE NOTICE 'Check deletion_audit table for recovery information';
    END IF;
END;
$$;

sql

CREATE EVENT TRIGGER deletion_monitor
    ON sql_drop
    EXECUTE FUNCTION monitor_object_deletions();

测试示例和输出：

sql

-- 输入：创建测试对象和依赖关系
CREATE TABLE public.customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE public.orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE DEFAULT CURRENT_DATE
);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE VIEW customer_orders AS
SELECT c.name, COUNT(o.id) as order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name;

-- 执行删除操作（会触发级联删除）
DROP TABLE customers CASCADE;

预期输出：

WARNING:  Important object deleted: public.customers (Type: table, Original: true)
WARNING:  Important object deleted: public.customer_orders (Type: view, Original: false)
NOTICE:  Deletion Summary - Original: 1, Cascade: 6, Important Objects: 4
WARNING:  Important objects were deleted: public.customers, public.customer_orders, public.orders, idx_orders_customer
NOTICE:  Check deletion_audit table for recovery information

审计查询和结果：

sql

-- 查询删除审计信息
SELECT
    object_type,
    object_identity,
    is_original_target,
    is_cascade_deletion,
    recovery_info->>'address_names' as address_info
FROM deletion_audit
WHERE deletion_time >= NOW() - INTERVAL '1 minute'
ORDER BY is_original_target DESC, object_type;

object_type	object_identity	is_original_target	is_cascade_deletion	address_info
table	public.customers	true	false	["public","customers"]
constraint	customers_pkey	false	true	["public","customers","customers_pkey"]
sequence	public.customers_id_seq	false	true	["public","customers_id_seq"]
table	public.orders	false	true	["public","orders"]
view	public.customer_orders	false	true	["public","customer_orders"]
index	public.idx_orders_customer	false	true	["public","idx_orders_customer"]

分析过程：

原始删除：只有 customers 表是用户直接删除的目标
级联影响：由于外键约束，orders 表、视图 customer_orders 和相关索引都被级联删除
依赖链分析：删除一个表触发了 6 个对象的删除，展现了数据库对象间的复杂依赖关系
恢复信息：recovery_info 字段保存了重建对象所需的地址信息

TIP

生产环境建议

在生产环境中，可以扩展此系统：

集成邮件/Slack 通知
实现删除操作的延迟执行（先标记，后执行）
自动生成恢复脚本
与备份系统集成，提供数据恢复选项

3. 处理表重写事件

3.1 函数概述

表重写事件发生在 PostgreSQL 需要完全重建表结构时，比如更改列的数据类型、修改表的存储参数等。相关函数提供了表重写的详细信息。

可用函数：

pg_event_trigger_table_rewrite_oid() → oid - 返回被重写表的 OID
pg_event_trigger_table_rewrite_reason() → integer - 返回重写原因代码

3.2 重写原因代码解析

代码值	二进制	含义	典型场景
1	0001	表的持久性已更改	TEMP ↔ PERMANENT 转换
2	0010	列的默认值已更改	添加带默认值的 NOT NULL 列
4	0100	列具有新的数据类型	VARCHAR(50) → VARCHAR(100)
8	1000	表访问方法已更改	heap → columnar 存储

INFO

位图组合

重写原因使用位图表示，多个原因可以同时存在。例如：

代码 6 (4+2) = 数据类型变更 + 默认值变更
代码 12 (8+4) = 访问方法变更 + 数据类型变更

3.3 实际应用场景：表重写性能监控和优化建议

问题陈述： 大型表的重写操作可能耗时很长并影响系统性能，需要：

监控表重写操作的发生
分析重写原因，提供优化建议
记录重写时间，用于容量规划
在重写大表时发出性能警告

解决方案：

创建表重写监控表智能表重写监控函数注册表重写监控事件触发器

sql

-- 创建表重写监控表
CREATE TABLE table_rewrite_monitor (
    id SERIAL PRIMARY KEY,
    rewrite_time TIMESTAMP DEFAULT NOW(),
    user_name TEXT DEFAULT SESSION_USER,
    table_oid OID,
    table_name TEXT,
    schema_name TEXT,
    reason_code INTEGER,
    reason_description TEXT,
    table_size_mb NUMERIC,
    estimated_duration_mins NUMERIC,
    performance_impact TEXT
);

-- 创建辅助函数：解析重写原因
CREATE OR REPLACE FUNCTION decode_rewrite_reason(reason_code INTEGER)
RETURNS TEXT
LANGUAGE plpgsql AS $$
DECLARE
    reasons TEXT[] := ARRAY[]::TEXT[];
BEGIN
    IF (reason_code & 1) = 1 THEN
        reasons := array_append(reasons, '持久性变更');
    END IF;

    IF (reason_code & 2) = 2 THEN
        reasons := array_append(reasons, '默认值变更');
    END IF;

    IF (reason_code & 4) = 4 THEN
        reasons := array_append(reasons, '数据类型变更');
    END IF;

    IF (reason_code & 8) = 8 THEN
        reasons := array_append(reasons, '访问方法变更');
    END IF;

    IF array_length(reasons, 1) IS NULL THEN
        RETURN '未知原因(' || reason_code || ')';
    END IF;

    RETURN array_to_string(reasons, ' + ');
END;
$$;

sql

CREATE OR REPLACE FUNCTION monitor_table_rewrite()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
DECLARE
    table_oid OID;
    reason_code INTEGER;
    table_info RECORD;
    size_mb NUMERIC;
    estimated_duration NUMERIC;
    impact_level TEXT;
    reason_text TEXT;
BEGIN
    -- 获取表重写信息
    table_oid := pg_event_trigger_table_rewrite_oid();
    reason_code := pg_event_trigger_table_rewrite_reason();

    -- 获取表的详细信息
    SELECT
        schemaname,
        tablename,
        pg_size_pretty(pg_total_relation_size(table_oid::regclass))::TEXT as size_pretty,
        ROUND(pg_total_relation_size(table_oid::regclass) / 1024.0 / 1024.0, 2) as size_mb_val
    INTO table_info
    FROM pg_tables
    WHERE schemaname || '.' || tablename = table_oid::regclass::TEXT
    LIMIT 1;

    -- 如果在pg_tables中找不到，尝试从pg_class获取基本信息
    IF table_info IS NULL THEN
        SELECT
            n.nspname as schemaname,
            c.relname as tablename,
            ROUND(pg_total_relation_size(table_oid) / 1024.0 / 1024.0, 2) as size_mb_val
        INTO table_info
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        WHERE c.oid = table_oid;
    END IF;

    size_mb := COALESCE(table_info.size_mb_val, 0);
    reason_text := decode_rewrite_reason(reason_code);

    -- 估算重写时间和性能影响
    CASE
        WHEN size_mb < 100 THEN
            estimated_duration := size_mb * 0.1; -- 小表：约0.1分钟/100MB
            impact_level := '低';
        WHEN size_mb < 1000 THEN
            estimated_duration := size_mb * 0.2; -- 中表：约0.2分钟/100MB
            impact_level := '中';
        ELSE
            estimated_duration := size_mb * 0.5; -- 大表：约0.5分钟/100MB
            impact_level := '高';
    END CASE;

    -- 记录监控信息
    INSERT INTO table_rewrite_monitor (
        table_oid,
        table_name,
        schema_name,
        reason_code,
        reason_description,
        table_size_mb,
        estimated_duration_mins,
        performance_impact
    ) VALUES (
        table_oid,
        COALESCE(table_info.tablename, table_oid::regclass::TEXT),
        COALESCE(table_info.schemaname, 'unknown'),
        reason_code,
        reason_text,
        size_mb,
        estimated_duration,
        impact_level
    );

    -- 发出通知和警告
    RAISE NOTICE 'Table Rewrite: % (%.% MB) - Reason: %',
                 table_oid::regclass,
                 size_mb,
                 reason_text;

    -- 大表重写警告
    IF size_mb > 500 THEN
        RAISE WARNING 'Large table rewrite detected! Table: %, Size: % MB, Estimated Duration: % minutes, Impact: %',
                     table_oid::regclass,
                     size_mb,
                     ROUND(estimated_duration, 1),
                     impact_level;

        RAISE NOTICE 'Performance Tips: Consider maintenance window, monitor disk space, check for blocking queries';
    END IF;

    -- 特定原因的建议
    IF (reason_code & 4) = 4 THEN
        RAISE NOTICE 'Data type change detected - consider using ALTER TABLE with USING clause for complex conversions';
    END IF;

    IF (reason_code & 2) = 2 THEN
        RAISE NOTICE 'Default value change - consider adding column without default, then updating in batches for large tables';
    END IF;
END;
$$;

sql

CREATE EVENT TRIGGER table_rewrite_monitor
    ON table_rewrite
    EXECUTE FUNCTION monitor_table_rewrite();

测试示例和输出：

sql

-- 输入：创建测试表并填入数据
CREATE TABLE public.user_profiles (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入一些测试数据
INSERT INTO user_profiles (username, email)
SELECT
    'user_' || i,
    'user' || i || '@example.com'
FROM generate_series(1, 10000) i;

-- 执行会触发表重写的操作
-- 1. 数据类型变更（原因代码：4）
ALTER TABLE user_profiles ALTER COLUMN username TYPE VARCHAR(100);

预期输出：

NOTICE:  Table Rewrite: public.user_profiles (1.2 MB) - Reason: 数据类型变更
NOTICE:  Data type change detected - consider using ALTER TABLE with USING clause for complex conversions

sql

-- 2. 添加带默认值的NOT NULL列（原因代码：2）
ALTER TABLE user_profiles ADD COLUMN status VARCHAR(20) DEFAULT 'active' NOT NULL;

预期输出：

NOTICE:  Table Rewrite: public.user_profiles (1.3 MB) - Reason: 默认值变更
NOTICE:  Default value change - consider adding column without default, then updating in batches for large tables

sql

-- 3. 模拟大表重写（创建大表进行测试）
CREATE TABLE large_table AS
SELECT
    i as id,
    'data_' || i as data,
    NOW() as created_at
FROM generate_series(1, 500000) i;

-- 触发大表重写
ALTER TABLE large_table ALTER COLUMN data TYPE TEXT;

预期输出：

WARNING:  Large table rewrite detected! Table: public.large_table, Size: 45.2 MB, Estimated Duration: 22.6 minutes, Impact: 中
NOTICE:  Performance Tips: Consider maintenance window, monitor disk space, check for blocking queries
NOTICE:  Table Rewrite: public.large_table (45.2 MB) - Reason: 数据类型变更
NOTICE:  Data type change detected - consider using ALTER TABLE with USING clause for complex conversions

监控数据查询：

sql

-- 查询表重写监控历史
SELECT
    rewrite_time,
    table_name,
    schema_name,
    table_size_mb,
    reason_description,
    performance_impact,
    estimated_duration_mins
FROM table_rewrite_monitor
ORDER BY rewrite_time DESC;

查询结果示例：

rewrite_time	table_name	schema_name	table_size_mb	reason_description	performance_impact	estimated_duration_mins
2024-01-15 14:30:45	large_table	public	45.20	数据类型变更	中	22.6
2024-01-15 14:25:12	user_profiles	public	1.30	默认值变更	低	0.3
2024-01-15 14:20:33	user_profiles	public	1.20	数据类型变更	低	0.2

分析过程：

性能影响评估：根据表大小自动分类性能影响级别
时间估算：基于表大小提供重写时间预估，帮助规划维护窗口
原因分析：位图解码提供清晰的重写原因说明
优化建议：针对不同重写原因提供相应的优化建议
预警机制：大表重写时自动发出警告，提醒管理员关注

4. 综合应用场景：企业级数据库变更管理系统

4.1 完整的数据库治理解决方案

将所有事件触发器函数整合，构建一个完整的数据库变更管理和治理系统：

统一监控视图智能分析函数

sql

-- 创建统一的数据库变更监控视图
CREATE VIEW database_change_summary AS
SELECT
    'DDL_AUDIT' as event_type,
    change_time as event_time,
    user_name,
    command_tag as operation,
    object_identity as affected_object,
    schema_name,
    CASE
        WHEN in_extension THEN '扩展操作'
        ELSE '用户操作'
    END as operation_source,
    NULL as additional_info
FROM schema_change_audit

UNION ALL

SELECT
    'DELETION' as event_type,
    deletion_time as event_time,
    user_name,
    'DROP' as operation,
    object_identity as affected_object,
    schema_name,
    CASE
        WHEN is_original_target THEN '直接删除'
        ELSE '级联删除'
    END as operation_source,
    'Cascade: ' || is_cascade_deletion::TEXT as additional_info
FROM deletion_audit

UNION ALL

SELECT
    'TABLE_REWRITE' as event_type,
    rewrite_time as event_time,
    user_name,
    'REWRITE' as operation,
    schema_name || '.' || table_name as affected_object,
    schema_name,
    'Impact: ' || performance_impact as operation_source,
    'Reason: ' || reason_description || ', Size: ' || table_size_mb || 'MB' as additional_info
FROM table_rewrite_monitor

ORDER BY event_time DESC;

sql

-- 创建智能分析函数
CREATE OR REPLACE FUNCTION analyze_database_changes(
    start_time TIMESTAMP DEFAULT NOW() - INTERVAL '24 hours',
    end_time TIMESTAMP DEFAULT NOW()
)
RETURNS TABLE (
    summary_type TEXT,
    metric_name TEXT,
    metric_value TEXT,
    recommendation TEXT
)
LANGUAGE plpgsql AS $$
DECLARE
    ddl_count INTEGER;
    deletion_count INTEGER;
    rewrite_count INTEGER;
    high_impact_rewrites INTEGER;
    cascade_deletions INTEGER;
    extension_operations INTEGER;
BEGIN
    -- 统计各类操作数量
    SELECT COUNT(*) INTO ddl_count
    FROM schema_change_audit
    WHERE change_time BETWEEN start_time AND end_time;

    SELECT COUNT(*) INTO deletion_count
    FROM deletion_audit
    WHERE deletion_time BETWEEN start_time AND end_time;

    SELECT COUNT(*) INTO rewrite_count
    FROM table_rewrite_monitor
    WHERE rewrite_time BETWEEN start_time AND end_time;

    SELECT COUNT(*) INTO high_impact_rewrites
    FROM table_rewrite_monitor
    WHERE rewrite_time BETWEEN start_time AND end_time
    AND performance_impact = '高';

    SELECT COUNT(*) INTO cascade_deletions
    FROM deletion_audit
    WHERE deletion_time BETWEEN start_time AND end_time
    AND is_cascade_deletion = true;

    SELECT COUNT(*) INTO extension_operations
    FROM schema_change_audit
    WHERE change_time BETWEEN start_time AND end_time
    AND in_extension = true;

    -- 返回分析结果
    RETURN QUERY VALUES
        ('活动统计', 'DDL操作数量', ddl_count::TEXT,
         CASE WHEN ddl_count > 100 THEN '变更频率较高，建议加强变更管理' ELSE '正常范围' END),
        ('活动统计', '删除操作数量', deletion_count::TEXT,
         CASE WHEN deletion_count > 20 THEN '删除操作频繁，注意数据安全' ELSE '正常范围' END),
        ('性能影响', '表重写操作', rewrite_count::TEXT,
         CASE WHEN rewrite_count > 5 THEN '重写操作较多，注意性能影响' ELSE '正常范围' END),
        ('性能影响', '高影响重写', high_impact_rewrites::TEXT,
         CASE WHEN high_impact_rewrites > 0 THEN '有大表重写，建议在维护窗口执行' ELSE '无高影响操作' END),
        ('数据安全', '级联删除', cascade_deletions::TEXT,
         CASE WHEN cascade_deletions > 10 THEN '级联删除较多，检查对象依赖关系' ELSE '依赖关系正常' END),
        ('系统维护', '扩展操作', extension_operations::TEXT,
         CASE WHEN extension_operations > 0 THEN '有扩展安装/更新操作' ELSE '无扩展变更' END);
END;
$$;

使用示例：

sql

-- 生成24小时内的数据库变更分析报告
SELECT * FROM analyze_database_changes();

-- 查看最近的变更摘要
SELECT
    event_type,
    COUNT(*) as operation_count,
    COUNT(DISTINCT user_name) as unique_users,
    COUNT(DISTINCT schema_name) as affected_schemas
FROM database_change_summary
WHERE event_time >= NOW() - INTERVAL '1 day'
GROUP BY event_type
ORDER BY operation_count DESC;

总结

PostgreSQL 事件触发器函数为数据库管理提供了强大的自动化和监控能力：

关键优势

全面监控：覆盖 DDL 命令、对象删除、表重写等所有重要的架构变更事件
实时响应：事件发生时立即触发，无延迟监控
详细信息：提供丰富的元数据，支持深度分析和审计
业务集成：可与企业治理流程、通知系统、自动化工具集成

最佳实践

TIP

生产环境部署建议

性能考虑：事件触发器会增加 DDL 操作的开销，在高频变更环境中要谨慎使用
权限管理：确保只有授权用户能够修改事件触发器
监控存储：定期清理审计表，避免占用过多存储空间
测试验证：在部署到生产环境前，充分测试各种场景
备份策略：包含事件触发器的定义在备份策略中

扩展方向

集成外部系统：通过 pg_notify 与监控系统集成
自动化响应：基于事件触发器实现自动化的数据库维护任务
合规报告：生成符合企业治理要求的审计报告
智能分析：结合机器学习分析数据库变更模式，预测潜在问题

通过合理使用这些事件触发器函数，可以构建一个完整的数据库治理和自动化运维体系，提升数据库管理的效率和安全性。

PostgreSQL 事件触发器函数 ​

概述 ​

事件触发器工作流程 ​

1. 在命令结束时捕获更改 ​

1.1 函数概述 ​

1.2 返回列详解 ​

1.3 实际应用场景：数据库变更审计系统 ​

2. 处理由 DDL 命令删除的对象 ​

2.1 函数概述 ​

2.2 返回列详解 ​

2.3 实际应用场景：智能删除保护和恢复系统 ​

3. 处理表重写事件 ​

3.1 函数概述 ​

3.2 重写原因代码解析 ​

3.3 实际应用场景：表重写性能监控和优化建议 ​

4. 综合应用场景：企业级数据库变更管理系统 ​

4.1 完整的数据库治理解决方案 ​

总结 ​

关键优势 ​

最佳实践 ​

扩展方向 ​

PostgreSQL 事件触发器函数

概述

事件触发器工作流程

1. 在命令结束时捕获更改

1.1 函数概述

1.2 返回列详解

1.3 实际应用场景：数据库变更审计系统

2. 处理由 DDL 命令删除的对象

2.1 函数概述

2.2 返回列详解

2.3 实际应用场景：智能删除保护和恢复系统

3. 处理表重写事件

3.1 函数概述

3.2 重写原因代码解析

3.3 实际应用场景：表重写性能监控和优化建议

4. 综合应用场景：企业级数据库变更管理系统

4.1 完整的数据库治理解决方案

总结

关键优势

最佳实践

扩展方向