PostgreSQL的Event triggers

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;