PostgreSQL教程FG154-PG事件触发器：数据库对象变更监控与控制

本文档风哥主要介绍PostgreSQL数据库事件触发器的使用，包括DDL操作监控、数据库对象变更控制等功能，风哥教程参考PostgreSQL官方文档Event Triggers内容，适合数据库管理员和开发人员在生产环境中实现数据库对象的变更监控和控制。

Part01-基础概念与理论知识

1.1 PostgreSQL数据库事件触发器概念

事件触发器是PostgreSQL 9.3版本引入的特性，用于响应数据库对象的变更事件，如创建表、修改列、删除索引等DDL操作。更多视频教程www.fgedu.net.cn。与普通触发器不同，事件触发器是语句级的，且只响应数据库对象的变更事件。

PostgreSQL数据库事件触发器特点：

响应DDL语句（数据定义语言）
语句级触发器，每个DDL语句只触发一次
可以监控数据库对象的创建、修改、删除
可以控制DDL操作的执行
支持多个事件触发器按优先级执行

1.2 PostgreSQL数据库事件类型

PostgreSQL支持多种事件类型，主要包括：

ddl_command_start：DDL命令开始执行前触发
ddl_command_end：DDL命令执行完成后触发
table_rewrite：表重写操作触发
sql_drop：对象删除操作触发
function_create：函数创建操作触发（PostgreSQL 10+）
type_create：类型创建操作触发（PostgreSQL 10+）

1.3 PostgreSQL数据库事件触发器执行机制

事件触发器的执行遵循一定的顺序：按优先级排序，相同优先级按创建顺序执行。学习交流加群风哥微信: itpux-com。事件触发器可以通过RAISE EXCEPTION来阻止DDL操作的执行。

Part02-生产环境规划与建议

2.1 PostgreSQL数据库事件触发器设计原则

事件触发器设计原则：单一职责，每个触发器只监控一种类型的事件；性能优先，避免在触发器中执行复杂操作；错误处理，提供清晰的错误信息；可维护性，编写清晰的代码和文档。

2.2 PostgreSQL数据库事件触发器性能优化

性能优化建议：避免在事件触发器中执行大量操作；使用批量处理；合理选择事件类型；定期维护事件触发器相关表的统计信息；监控事件触发器执行时间。

2.3 PostgreSQL数据库事件触发器安全考虑

安全考虑：避免在事件触发器中执行危险操作；使用SECURITY DEFINER时要谨慎；确保事件触发器函数有适当的权限；避免信息泄露。

风哥提示：事件触发器是监控和控制数据库对象变更的强大工具，但也需要谨慎使用。建议在设计事件触发器时充分考虑其对系统性能的影响，特别是在频繁执行DDL操作的场景下。

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库DDL操作审计触发器

3.1.1 DDL操作审计表设计

— DDL操作审计触发器

— 创建DDL审计日志表
CREATE TABLE fgedu_ddl_audit_log (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMP DEFAULT NOW(),
event_type VARCHAR(50) NOT NULL,
object_type VARCHAR(50),
object_name VARCHAR(200),
schema_name VARCHAR(100),
command_tag VARCHAR(100) NOT NULL,
sql_command TEXT NOT NULL,
user_name VARCHAR(100) DEFAULT CURRENT_USER,
client_ip INET DEFAULT inet_client_addr(),
fgapplication_name VARCHAR(100) DEFAULT current_setting(‘fgapplication_name’)
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_ddl_audit_time ON fgedu_ddl_audit_log(event_time);
CREATE INDEX idx_ddl_audit_user ON fgedu_ddl_audit_log(user_name);
CREATE INDEX idx_ddl_audit_object ON fgedu_ddl_audit_log(object_type, object_name);

— 输出结果
CREATE INDEX
CREATE INDEX
CREATE INDEX

— 创建DDL审计触发器函数
CREATE OR REPLACE FUNCTION fgedu_ddl_audit()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO fgedu_ddl_audit_log(
event_type,
object_type,
object_name,
schema_name,
command_tag,
sql_command
)
VALUES(
TG_EVENT,
obj.object_type,
obj.object_identity,
obj.schema_name,
TG_TAG,
current_query()
);
END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

— 输出结果
CREATE FUNCTION

— 创建DDL命令结束事件触发器
CREATE EVENT TRIGGER trg_ddl_audit
ON ddl_command_end
EXECUTE FUNCTION fgedu_ddl_audit();

— 输出结果
CREATE EVENT TRIGGER

— 测试DDL审计触发器
CREATE TABLE fgedu_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
CREATE TABLE

— 查看审计日志
SELECT event_time, event_type, object_type, object_name, command_tag, user_name
FROM fgedu_ddl_audit_log
ORDER BY event_time DESC
LIMIT 5;

— 测试修改表
ALTER TABLE fgedu_test ADD COLUMN email VARCHAR(100);

— 输出结果
ALTER TABLE

— 查看审计日志
SELECT event_time, event_type, object_type, object_name, command_tag, user_name
FROM fgedu_ddl_audit_log
ORDER BY event_time DESC
LIMIT 5;

— 测试删除表
DROP TABLE fgedu_test;

— 输出结果
DROP TABLE

— 查看审计日志
SELECT event_time, event_type, object_type, object_name, command_tag, user_name
FROM fgedu_ddl_audit_log
ORDER BY event_time DESC
LIMIT 5;

3.2 PostgreSQL数据库对象创建控制触发器

3.2.1 对象创建控制触发器

— 对象创建控制触发器

— 创建对象创建控制触发器函数
CREATE OR REPLACE FUNCTION fgedu_object_creation_control()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
— 限制非fgedu前缀的表创建
IF obj.object_type = ‘table’ THEN
IF obj.object_identity !~ ‘^fgedu_’ THEN
RAISE EXCEPTION ‘Table name must start with fgedu_ prefix’;
END IF;
END IF;

— 限制在生产环境创建临时表
IF obj.object_type = ‘table’ AND obj.object_identity ~ ‘^pg_temp_’ THEN
RAISE EXCEPTION ‘Temporary tables are not allowed in production environment’;
END IF;

— 限制创建无主键的表
IF obj.object_type = ‘table’ THEN
— 这里可以添加更复杂的逻辑来检查主键
RAISE NOTICE ‘Table % created’, obj.object_identity;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建DDL命令开始事件触发器
CREATE EVENT TRIGGER trg_object_creation_control
ON ddl_command_start
WHEN TAG IN (‘CREATE TABLE’, ‘CREATE TABLE AS’)
EXECUTE FUNCTION fgedu_object_creation_control();

— 输出结果
CREATE EVENT TRIGGER

— 测试创建非fgedu前缀的表（应该失败）
CREATE TABLE test_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
ERROR: Table name must start with fgedu_ prefix

— 测试创建fgedu前缀的表（应该成功）
CREATE TABLE fgedu_users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100)
);

— 输出结果
NOTICE: Table public.fgedu_users created
CREATE TABLE

— 测试创建临时表（应该失败）
CREATE TEMP TABLE temp_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
ERROR: Temporary tables are not allowed in production environment

3.3 PostgreSQL数据库模式管理触发器

3.3.1 模式管理触发器

— 模式管理触发器

— 创建模式操作审计表
CREATE TABLE fgedu_schema_audit (
id BIGSERIAL PRIMARY KEY,
event_time TIMESTAMP DEFAULT NOW(),
event_type VARCHAR(50) NOT NULL,
schema_name VARCHAR(100) NOT NULL,
operation VARCHAR(50) NOT NULL,
user_name VARCHAR(100) DEFAULT CURRENT_USER,
details TEXT
);

— 输出结果
CREATE TABLE

— 创建模式管理触发器函数
CREATE OR REPLACE FUNCTION fgedu_schema_management()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
BEGIN
IF TG_EVENT = ‘ddl_command_start’ THEN
IF TG_TAG = ‘CREATE SCHEMA’ THEN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
— 检查模式名称是否符合规范
IF obj.schema_name !~ ‘^fgedu_’ THEN
RAISE EXCEPTION ‘Schema name must start with fgedu_ prefix’;
END IF;
END LOOP;
END IF;
ELSIF TG_EVENT = ‘ddl_command_end’ THEN
IF TG_TAG IN (‘CREATE SCHEMA’, ‘ALTER SCHEMA’, ‘DROP SCHEMA’) THEN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
INSERT INTO fgedu_schema_audit(
event_type,
schema_name,
operation,
details
)
VALUES(
TG_EVENT,
obj.schema_name,
TG_TAG,
current_query()
);
END LOOP;
END IF;
END IF;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建模式创建控制触发器
CREATE EVENT TRIGGER trg_schema_creation_control
ON ddl_command_start
WHEN TAG IN (‘CREATE SCHEMA’)
EXECUTE FUNCTION fgedu_schema_management();

— 输出结果
CREATE EVENT TRIGGER

— 创建模式操作审计触发器
CREATE EVENT TRIGGER trg_schema_audit
ON ddl_command_end
WHEN TAG IN (‘CREATE SCHEMA’, ‘ALTER SCHEMA’, ‘DROP SCHEMA’)
EXECUTE FUNCTION fgedu_schema_management();

— 输出结果
CREATE EVENT TRIGGER

— 测试创建非fgedu前缀的模式（应该失败）
CREATE SCHEMA test_schema;

— 输出结果
ERROR: Schema name must start with fgedu_ prefix

— 测试创建fgedu前缀的模式（应该成功）
CREATE SCHEMA fgedu_test;

— 输出结果
CREATE SCHEMA

— 查看模式审计日志
SELECT event_time, event_type, schema_name, operation, user_name
FROM fgedu_schema_audit
ORDER BY event_time DESC
LIMIT 5;

— 测试修改模式
ALTER SCHEMA fgedu_test RENAME TO fgedu_test_schema;

— 输出结果
ALTER SCHEMA

— 查看模式审计日志
SELECT event_time, event_type, schema_name, operation, user_name
FROM fgedu_schema_audit
ORDER BY event_time DESC
LIMIT 5;

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库DDL操作审计实战

本案例演示完整的DDL操作审计系统。学习交流加群风哥QQ113257174。

— DDL操作审计实战

— 创建详细的DDL审计表
CREATE TABLE fgedu_ddl_audit_detail (
id BIGSERIAL PRIMARY KEY,
audit_id BIGINT,
object_type VARCHAR(50),
object_schema VARCHAR(100),
object_name VARCHAR(200),
object_identity VARCHAR(300),
command_tag VARCHAR(100),
command_details JSONB,
FOREIGN KEY (audit_id) REFERENCES fgedu_ddl_audit_log(id)
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_ddl_audit_detail_audit ON fgedu_ddl_audit_detail(audit_id);
CREATE INDEX idx_ddl_audit_detail_object ON fgedu_ddl_audit_detail(object_type, object_schema, object_name);

— 输出结果
CREATE INDEX
CREATE INDEX

— 创建增强的DDL审计触发器函数
CREATE OR REPLACE FUNCTION fgedu_ddl_audit_enhanced()
RETURNS event_trigger
AS $$
DECLARE
v_audit_id BIGINT;
obj RECORD;
v_details JSONB;
BEGIN
— 插入主审计记录
INSERT INTO fgedu_ddl_audit_log(
event_type,
command_tag,
sql_command,
user_name
)
VALUES(
TG_EVENT,
TG_TAG,
current_query(),
CURRENT_USER
)
RETURNING id INTO v_audit_id;

— 处理DDL命令
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
v_details := jsonb_build_object(
‘object_type’, obj.object_type,
‘schema_name’, obj.schema_name,
‘object_name’, obj.object_name,
‘object_identity’, obj.object_identity,
‘command_tag’, obj.command_tag,
‘is_truncate’, obj.is_truncate
);

INSERT INTO fgedu_ddl_audit_detail(
audit_id,
object_type,
object_schema,
object_name,
object_identity,
command_tag,
command_details
)
VALUES(
v_audit_id,
obj.object_type,
obj.schema_name,
obj.object_name,
obj.object_identity,
obj.command_tag,
v_details
);
END LOOP;

— 处理SQL DROP事件
IF TG_EVENT = ‘sql_drop’ THEN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
v_details := jsonb_build_object(
‘object_type’, obj.object_type,
‘schema_name’, obj.schema_name,
‘object_name’, obj.object_name,
‘object_identity’, obj.object_identity,
‘original’, obj.original,
‘is_temporary’, obj.is_temporary
);

— 输出结果
CREATE FUNCTION

— 先删除之前的触发器
DROP EVENT TRIGGER IF EXISTS trg_ddl_audit;

— 输出结果
DROP EVENT TRIGGER

— 创建增强的DDL审计触发器
CREATE EVENT TRIGGER trg_ddl_audit_enhanced
ON ddl_command_end
EXECUTE FUNCTION fgedu_ddl_audit_enhanced();

— 输出结果
CREATE EVENT TRIGGER

— 创建SQL DROP事件触发器
CREATE EVENT TRIGGER trg_sql_drop_audit
ON sql_drop
EXECUTE FUNCTION fgedu_ddl_audit_enhanced();

— 输出结果
CREATE EVENT TRIGGER

— 测试完整的DDL审计
CREATE TABLE fgedu_audit_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
value INTEGER
);

— 输出结果
CREATE TABLE

— 创建索引
CREATE INDEX idx_audit_test_name ON fgedu_audit_test(name);

— 输出结果
CREATE INDEX

— 修改表
ALTER TABLE fgedu_audit_test ADD COLUMN description TEXT;

— 输出结果
ALTER TABLE

— 删除表
DROP TABLE fgedu_audit_test;

— 输出结果
DROP TABLE

— 查看审计日志
SELECT a.id, a.event_time, a.event_type, a.command_tag, a.user_name,
d.object_type, d.object_schema, d.object_name
FROM fgedu_ddl_audit_log a
LEFT JOIN fgedu_ddl_audit_detail d ON a.id = d.audit_id
ORDER BY a.event_time DESC
LIMIT 10;

4.2 PostgreSQL数据库对象创建控制实战

本案例演示对象创建控制的完整实现。更多学习教程公众号风哥教程itpux_com。

— 对象创建控制实战

— 创建对象创建规则表
CREATE TABLE fgedu_object_rules (
id SERIAL PRIMARY KEY,
object_type VARCHAR(50) NOT NULL,
rule_type VARCHAR(50) NOT NULL,
rule_pattern VARCHAR(200) NOT NULL,
is_allowed BOOLEAN DEFAULT TRUE,
description TEXT,
created_at TIMESTAMP DEFAULT NOW()
);

— 输出结果
CREATE TABLE

— 插入规则数据
INSERT INTO fgedu_object_rules(object_type, rule_type, rule_pattern, is_allowed, description)
VALUES
(‘table’, ‘name_pattern’, ‘^fgedu_.*’, TRUE, ‘允许创建fgedu前缀的表’),
(‘table’, ‘name_pattern’, ‘^test_.*’, FALSE, ‘禁止创建test前缀的表’),
(‘schema’, ‘name_pattern’, ‘^fgedu_.*’, TRUE, ‘允许创建fgedu前缀的模式’),
(‘function’, ‘name_pattern’, ‘^fgedu_.*’, TRUE, ‘允许创建fgedu前缀的函数’),
(‘index’, ‘name_pattern’, ‘^idx_.*’, TRUE, ‘允许创建idx前缀的索引’);

— 输出结果
INSERT 0 5

— 创建对象创建控制触发器函数
CREATE OR REPLACE FUNCTION fgedu_object_creation_control_enhanced()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
v_rule RECORD;
v_allowed BOOLEAN;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
v_allowed := FALSE;

— 检查规则
FOR v_rule IN
SELECT * FROM fgedu_object_rules
WHERE object_type = obj.object_type
ORDER BY id
LOOP
IF obj.object_identity ~ v_rule.rule_pattern THEN
v_allowed := v_rule.is_allowed;
EXIT;
END IF;
END LOOP;

— 检查默认规则
IF NOT v_allowed THEN
— 检查是否有默认允许规则
SELECT is_allowed INTO v_allowed
FROM fgedu_object_rules
WHERE object_type = obj.object_type
AND rule_pattern = ‘*’;

IF NOT FOUND THEN
v_allowed := FALSE;
END IF;
END IF;

IF NOT v_allowed THEN
RAISE EXCEPTION ‘Creation of % % is not allowed’, obj.object_type, obj.object_identity;
END IF;

— 额外的检查
IF obj.object_type = ‘table’ THEN
— 检查表名长度
IF length(obj.object_name) > 50 THEN
RAISE EXCEPTION ‘Table name is too long (max 50 characters)’;
END IF;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 先删除之前的触发器
DROP EVENT TRIGGER IF EXISTS trg_object_creation_control;

— 输出结果
DROP EVENT TRIGGER

— 创建增强的对象创建控制触发器
CREATE EVENT TRIGGER trg_object_creation_control_enhanced
ON ddl_command_start
WHEN TAG IN (‘CREATE TABLE’, ‘CREATE SCHEMA’, ‘CREATE FUNCTION’, ‘CREATE INDEX’)
EXECUTE FUNCTION fgedu_object_creation_control_enhanced();

— 输出结果
CREATE EVENT TRIGGER

— 测试允许的对象创建
CREATE TABLE fgedu_allowed_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
CREATE TABLE

— 测试禁止的对象创建
CREATE TABLE test_prohibited_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
ERROR: Creation of table public.test_prohibited_table is not allowed

— 测试创建索引
CREATE INDEX idx_allowed_table_name ON fgedu_allowed_table(name);

— 输出结果
CREATE INDEX

— 测试创建函数
CREATE FUNCTION fgedu_test_function(p_id INTEGER)
RETURNS INTEGER
AS $$
BEGIN
RETURN p_id * 2;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

4.3 PostgreSQL数据库模式变更审计实战

本案例演示模式变更审计的完整实现。from PostgreSQL视频:www.itpux.com。

— 模式变更审计实战

— 创建模式变更审计表
CREATE TABLE fgedu_schema_change_audit (
id BIGSERIAL PRIMARY KEY,
change_time TIMESTAMP DEFAULT NOW(),
schema_name VARCHAR(100) NOT NULL,
change_type VARCHAR(50) NOT NULL,
old_state JSONB,
new_state JSONB,
changed_by VARCHAR(100) DEFAULT CURRENT_USER,
details TEXT
);

— 输出结果
CREATE TABLE

— 创建模式状态函数
CREATE OR REPLACE FUNCTION fgedu_get_schema_state(p_schema_name VARCHAR)
RETURNS JSONB
AS $$
DECLARE
v_state JSONB;
BEGIN
SELECT jsonb_build_object(
‘schema_name’, nspname,
‘owner’, rolname,
‘tables’, (SELECT jsonb_agg(jsonb_build_object(
‘table_name’, relname,
‘columns’, (SELECT jsonb_agg(jsonb_build_object(
‘column_name’, attname,
‘data_type’, format_type(atttypid, atttypmod)
)) FROM pg_attribute WHERE attrelid = c.oid AND attnum > 0)
)) FROM pg_class c WHERE c.relnamespace = n.oid AND c.relkind = ‘r’),
‘functions’, (SELECT jsonb_agg(jsonb_build_object(
‘function_name’, proname,
‘arguments’, pg_get_function_arguments(p.oid),
‘return_type’, pg_get_function_result(p.oid)
)) FROM pg_proc p WHERE p.pronamespace = n.oid)
) INTO v_state
FROM pg_namespace n
JOIN pg_roles r ON n.nspowner = r.oid
WHERE n.nspname = p_schema_name;

RETURN v_state;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建模式变更审计触发器函数
CREATE OR REPLACE FUNCTION fgedu_schema_change_audit()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
v_old_state JSONB;
v_new_state JSONB;
BEGIN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
IF obj.object_type = ‘schema’ THEN
— 获取变更前状态
v_old_state := fgedu_get_schema_state(obj.schema_name);

— 这里可以添加逻辑来捕获变更后的状态
— 注意：在ddl_command_start中只能获取变更前状态
— 在ddl_command_end中可以获取变更后状态

INSERT INTO fgedu_schema_change_audit(
schema_name,
change_type,
old_state,
details
)
VALUES(
obj.schema_name,
TG_TAG,
v_old_state,
current_query()
);
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建模式变更审计触发器
CREATE EVENT TRIGGER trg_schema_change_audit
ON ddl_command_end
WHEN TAG IN (‘CREATE SCHEMA’, ‘ALTER SCHEMA’, ‘DROP SCHEMA’)
EXECUTE FUNCTION fgedu_schema_change_audit();

— 输出结果
CREATE EVENT TRIGGER

— 测试模式变更审计
CREATE SCHEMA fgedu_audit_schema;

— 输出结果
CREATE SCHEMA

— 在模式中创建表
CREATE TABLE fgedu_audit_schema.fgedu_audit_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
CREATE TABLE

— 在模式中创建函数
CREATE FUNCTION fgedu_audit_schema.fgedu_audit_function(p_id INTEGER)
RETURNS INTEGER
AS $$
BEGIN
RETURN p_id + 1;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 查看模式变更审计
SELECT change_time, schema_name, change_type, changed_by
FROM fgedu_schema_change_audit
ORDER BY change_time DESC
LIMIT 5;

— 查看模式状态
SELECT fgedu_get_schema_state(‘fgedu_audit_schema’);

— 输出结果
fgedu_get_schema_state
——————————————————————————————————————————————————–
{“functions”: [{“arguments”: “p_id integer”, “function_name”: “fgedu_audit_function”, “return_type”: “integer”}], “owner”: “fgedu”, “schema_name”: “fgedu_audit_schema”, “tables”: [{“columns”: [{“column_name”: “id”, “data_type”: “integer”}, {“column_name”: “name”, “data_type”: “character varying(100)”}], “table_name”: “fgedu_audit_table”}]}
(1 row)

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库事件触发器最佳实践

事件触发器最佳实践：合理选择事件类型和触发时机；保持触发器逻辑简单；使用事务保证数据一致性；编写清晰的错误处理；定期维护事件触发器相关表；监控事件触发器执行性能。

事件触发器使用场景推荐：

DDL审计：使用ddl_command_end事件
对象创建控制：使用ddl_command_start事件
对象删除审计：使用sql_drop事件
表重写监控：使用table_rewrite事件

5.2 PostgreSQL数据库事件触发器常见问题

常见问题：性能问题、递归触发、权限问题、错误处理不当、事件类型选择错误。

5.3 PostgreSQL数据库事件触发器调试技巧

调试技巧：使用RAISE NOTICE输出调试信息；使用日志记录事件触发器执行情况；使用事务回滚测试；分步测试事件触发器逻辑；检查事件触发器执行顺序。

— 事件触发器调试示例

— 创建调试事件触发器函数
CREATE OR REPLACE FUNCTION fgedu_debug_event_trigger()
RETURNS event_trigger
AS $$
DECLARE
obj RECORD;
BEGIN
RAISE NOTICE ‘Event trigger fired: %’, TG_EVENT;
RAISE NOTICE ‘Command tag: %’, TG_TAG;
RAISE NOTICE ‘Current query: %’, current_query();

IF TG_EVENT = ‘ddl_command_start’ OR TG_EVENT = ‘ddl_command_end’ THEN
FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands()
LOOP
RAISE NOTICE ‘Object: type=%s, identity=%s’, obj.object_type, obj.object_identity;
END LOOP;
ELSIF TG_EVENT = ‘sql_drop’ THEN
FOR obj IN SELECT * FROM pg_event_trigger_dropped_objects()
LOOP
RAISE NOTICE ‘Dropped object: type=%s, identity=%s’, obj.object_type, obj.object_identity;
END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;

— 输出结果
CREATE FUNCTION

— 创建调试事件触发器
CREATE EVENT TRIGGER trg_debug_event
ON ddl_command_end
EXECUTE FUNCTION fgedu_debug_event_trigger();

— 输出结果
CREATE EVENT TRIGGER

— 测试调试事件触发器
CREATE TABLE fgedu_debug_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);

— 输出结果
NOTICE: Event trigger fired: ddl_command_end
NOTICE: Command tag: CREATE TABLE
NOTICE: Current query: CREATE TABLE fgedu_debug_test (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
)
NOTICE: Object: type=table, identity=public.fgedu_debug_test
CREATE TABLE

— 查看日志
SELECT * FROM pg_log WHERE message LIKE ‘%Event trigger fired%’ ORDER BY log_time DESC LIMIT 5;

风哥提示：事件触发器是PostgreSQL的强大功能，用于监控和控制数据库对象的变更。在生产环境中，建议：1) 只在必要时使用事件触发器；2) 保持触发器逻辑简单；3) 充分测试事件触发器的性能影响；4) 建立完善的监控机制。同时，要注意事件触发器的执行顺序和错误处理，确保系统的稳定性和可靠性。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

PostgreSQL教程FG154-PG事件触发器：数据库对象变更监控与控制

Part01-基础概念与理论知识

1.1 PostgreSQL数据库事件触发器概念

1.2 PostgreSQL数据库事件类型

1.3 PostgreSQL数据库事件触发器执行机制

Part02-生产环境规划与建议

2.1 PostgreSQL数据库事件触发器设计原则

2.2 PostgreSQL数据库事件触发器性能优化

2.3 PostgreSQL数据库事件触发器安全考虑

Part03-生产环境项目实施方案

3.1 PostgreSQL数据库DDL操作审计触发器

3.1.1 DDL操作审计表设计

3.2 PostgreSQL数据库对象创建控制触发器

3.2.1 对象创建控制触发器

3.3 PostgreSQL数据库模式管理触发器

3.3.1 模式管理触发器

Part04-生产案例与实战讲解

4.1 PostgreSQL数据库DDL操作审计实战

4.2 PostgreSQL数据库对象创建控制实战

4.3 PostgreSQL数据库模式变更审计实战

Part05-风哥经验总结与分享

5.1 PostgreSQL数据库事件触发器最佳实践

5.2 PostgreSQL数据库事件触发器常见问题

5.3 PostgreSQL数据库事件触发器调试技巧

相关推荐

联系我们