PostgreSQL的Event triggers

01/19 11:51
阅读数 32

PostgreSQL中,常规触发器依附于单个表并捕获dml事件。而Event triggers是数据库全局性的,可以捕获ddl事件。与常规触发器一样,Event triggers可以用任何包含事件触发器支持的过程语言编写,也可以用C编写,但不能用纯SQL编写。

当与事件关联的事件在定义它的数据库中发生时,就会触发Event triggers。在11版本中,仅支持的事件是ddl_command_start,ddl_command_end,table_rewrite和sql_drop。在将来的版本中可能会添加对其他事件的支持。

ddl_command_start事件在执行CREATE,ALTER,DROP,SECURITY LABEL,COMMENT,GRANT或REVOKE命令之前发生。在触发事件触发器之前,不检查受影响的对象是否存在。但是,作为例外,针对共享对象(数据库,角色和表空间)的DDL命令或针对Event triggers本身的命令不会发生此事件。事件触发机制不支持这些对象类型。ddl_command_start也将在执行SELECT INTO命令之前发生,因为它等效于CREATE TABLE AS。

ddl_command_end事件在执行以上命令后立即发生。要获得有关已发生的DDL操作的更多详细信息,请使用ddl_command_end事件触发代码中的设置返回函数pg_event_trigger_ddl_commands()。请注意,触发器在执行操作之后(但在事务提交之前)触发,因此可以将系统目录读取为已更改。

对于任何删除数据库对象的操作,sql_drop事件就在ddl_command_end事件触发器之前发生。要列出已删除的对象,请使用sql_drop事件触发代码中的返回设置函数pg_event_trigger_dropped_objects()。请注意,触发器是在从系统目录中删除对象后执行的,因此无法再查找它们。

table_rewrite事件仅在通过命令ALTER TABLE和ALTER TYPE的某些操作重写表之前发生。尽管其他控制语句(例如CLUSTER和VACUUM)可用于重写表,但table_rewrite事件不会由它们触发。

事件触发器(与其他函数一样)无法在异常终止的事务中执行。因此,如果DDL命令由于错误而失败,则将不执行任何关联的ddl_command_end触发器。相反,如果ddl_command_start触发器因错误而失败,则不会触发其他事件触发器,也不会尝试执行命令本身。同样,如果ddl_command_end触发器因错误而失败,则DDL语句的效果将回滚,就像在其他任何情况下包含事务中止的情况一样。

使用命令CREATE EVENT TRIGGER创建事件触发器。为了创建事件触发器,必须首先创建具有特殊返回类型event_trigger的函数。此函数不需要(也可以不)返回值;返回类型仅用作将函数作为事件触发器调用的信号。

如果为一个特定事件定义了多个事件触发器,则它们将按触发器名称的字母顺序触发。

触发器定义还可以指定WHEN条件,例如,仅可以为用户希望拦截的特定命令触发ddl_command_start触发器。这种触发器的常见用法是限制用户可以执行的DDL操作的范围。

事件触发触发矩阵

 

Command Tag ddl_command_start ddl_command_end sql_drop table_rewrite Notes
ALTER AGGREGATE X X - -  
ALTER COLLATION X X - -  
ALTER CONVERSION X X - -  
ALTER DOMAIN X X - -  
ALTER DEFAULT PRIVILEGES X X - -  
ALTER EXTENSION X X - -  
ALTER FOREIGN DATA WRAPPER X X - -  
ALTER FOREIGN TABLE X X X -  
ALTER FUNCTION X X - -  
ALTER LANGUAGE X X - -  
ALTER LARGE OBJECT X X - -  
ALTER MATERIALIZED VIEW X X - -  
ALTER OPERATOR X X - -  
ALTER OPERATOR CLASS X X - -  
ALTER OPERATOR FAMILY X X - -  
ALTER POLICY X X - -  
ALTER PROCEDURE X X - -  
ALTER PUBLICATION X X - -  
ALTER SCHEMA X X - -  
ALTER SEQUENCE X X - -  
ALTER SERVER X X - -  
ALTER STATISTICS X X - -  
ALTER SUBSCRIPTION X X - -  
ALTER TABLE X X X X  
ALTER TEXT SEARCH CONFIGURATION X X - -  
ALTER TEXT SEARCH DICTIONARY X X - -  
ALTER TEXT SEARCH PARSER X X - -  
ALTER TEXT SEARCH TEMPLATE X X - -  
ALTER TRIGGER X X - -  
ALTER TYPE X X - X  
ALTER USER MAPPING X X - -  
ALTER VIEW X X - -  
COMMENT X X - - Only for local objects
CREATE ACCESS METHOD X X - -  
CREATE AGGREGATE X X - -  
CREATE CAST X X - -  
CREATE COLLATION X X - -  
CREATE CONVERSION X X - -  
CREATE DOMAIN X X - -  
CREATE EXTENSION X X - -  
CREATE FOREIGN DATA WRAPPER X X - -  
CREATE FOREIGN TABLE X X - -  
CREATE FUNCTION X X - -  
CREATE INDEX X X - -  
CREATE LANGUAGE X X - -  
CREATE MATERIALIZED VIEW X X - -  
CREATE OPERATOR X X - -  
CREATE OPERATOR CLASS X X - -  
CREATE OPERATOR FAMILY X X - -  
CREATE POLICY X X - -  
CREATE PROCEDURE X X - -  
CREATE PUBLICATION X X - -  
CREATE RULE X X - -  
CREATE SCHEMA X X - -  
CREATE SEQUENCE X X - -  
CREATE SERVER X X - -  
CREATE STATISTICS X X - -  
CREATE SUBSCRIPTION X X - -  
CREATE TABLE X X - -  
CREATE TABLE AS X X - -  
CREATE TEXT SEARCH CONFIGURATION X X - -  
CREATE TEXT SEARCH DICTIONARY X X - -  
CREATE TEXT SEARCH PARSER X X - -  
CREATE TEXT SEARCH TEMPLATE X X - -  
CREATE TRIGGER X X - -  
CREATE TYPE X X - -  
CREATE USER MAPPING X X - -  
CREATE VIEW X X - -  
DROP ACCESS METHOD X X X -  
DROP AGGREGATE X X X -  
DROP CAST X X X -  
DROP COLLATION X X X -  
DROP CONVERSION X X X -  
DROP DOMAIN X X X -  
DROP EXTENSION X X X -  
DROP FOREIGN DATA WRAPPER X X X -  
DROP FOREIGN TABLE X X X -  
DROP FUNCTION X X X -  
DROP INDEX X X X -  
DROP LANGUAGE X X X -  
DROP MATERIALIZED VIEW X X X -  
DROP OPERATOR X X X -  
DROP OPERATOR CLASS X X X -  
DROP OPERATOR FAMILY X X X -  
DROP OWNED X X X -  
DROP POLICY X X X -  
DROP PROCEDURE X X X -  
DROP PUBLICATION X X X -  
DROP RULE X X X -  
DROP SCHEMA X X X -  
DROP SEQUENCE X X X -  
DROP SERVER X X X -  
DROP STATISTICS X X X -  
DROP SUBSCRIPTION X X X -  
DROP TABLE X X X -  
DROP TEXT SEARCH CONFIGURATION X X X -  
DROP TEXT SEARCH DICTIONARY X X X -  
DROP TEXT SEARCH PARSER X X X -  
DROP TEXT SEARCH TEMPLATE X X X -  
DROP TRIGGER X X X -  
DROP TYPE X X X -  
DROP USER MAPPING X X X -  
DROP VIEW X X X -  
GRANT X X - - Only for local objects
IMPORT FOREIGN SCHEMA X X - -  
REFRESH MATERIALIZED VIEW X X - -  
REVOKE X X - - Only for local objects
SECURITY LABEL X X - - Only for local objects
SELECT INTO X X - -  

 

1.创建一张用于存放ddl记录的表:

CREATE TABLE ddl_history ( ID serial PRIMARY KEY, ddl_date timestamptz, ddl_tag TEXT, object_name TEXT );

2.定义两个函数:

一个用于跟踪对象的创建和修改,一个用于跟踪对象的删除操作

CREATE 
   OR REPLACE FUNCTION log_ddl ( ) RETURNS event_trigger AS $$ 
DECLARE
   audit_query TEXT;
   r RECORD;
BEGIN
   IF
      tg_tag <> 'DROP TABLE' THEN
         r := pg_event_trigger_ddl_commands ( );
      INSERT INTO ddl_history ( ddl_date, ddl_tag, object_name )
      VALUES
         ( statement_timestamp( ), tg_tag, r.object_identity );
   END IF;
END;
$$ LANGUAGE plpgsql;



CREATE 
   OR REPLACE FUNCTION log_ddl_drop ( ) RETURNS event_trigger AS $$ 
DECLARE
   audit_query TEXT;
   r RECORD;
BEGIN
   IF
      tg_tag = 'DROP TABLE' THEN
         FOR r IN SELECT
         * 
      FROM
         pg_event_trigger_ddl_commands ( )
         LOOP
         INSERT INTO ddl_history ( ddl_date, ddl_tag, object_name )
      VALUES
         ( statement_timestamp( ), tg_tag, r.object_identity );
   END LOOP;
END IF;
END;
$$ LANGUAGE plpgsql;

3.创建两个事件触发器

CREATE EVENT TRIGGER log_ddl_info ON ddl_command_end EXECUTE PROCEDURE log_ddl();

CREATE EVENT TRIGGER log_ddl_drop_info ON sql_drop EXECUTE PROCEDURE log_ddl_drop();

4.执行ddl测试

CREATE TABLE testtable (id int, first_name text);
ALTER TABLE testtable ADD COLUMN last_name text;
ALTER TABLE testtable ADD COLUMN midlname text;
ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;
ALTER TABLE testtable DROP COLUMN middle_name;
DROP TABLE testtable;
SELECT * FROM ddl_history;

 

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部