PostgreSQL 9.3 Event Trigger-阿里云开发者社区

PostgreSQL 9.3 将引入事件触发器, 与普通触发器不同的是, 事件触发器是数据库全局的触发器, 可以由DDL事件来触发.

Unlike regular triggers, which are attached to a single table and capture only DML events, event triggers are global to a particular database and are capable of capturing DDL events.

事件触发器同样可以使用C, plpgsql或者其他的过程语言的函数来编写, 但是不能使用SQL语言函数来编写.

由于事件触发器涉及的权限较大, 例如能禁止DDL操作等, 所以只能使用超级用户创建事件触发器.

在创建事件触发器之前必须先创建触发器函数, 触发器函数的返回类型为event_trigger. (注意区分我们以前所熟悉的普通触发器函数的返回类型为trigger.)

事件触发器的语法 :

Command:     CREATE EVENT TRIGGER
Description: define a new event trigger
Syntax:
CREATE EVENT TRIGGER name
  ON event
  [ WHEN filter_variable IN (filter_value [, ... ]) [ AND ... ] ]
  EXECUTE PROCEDURE function_name()

语法解释 :

-- name : 触发器名称

-- event : 事件名称, 现在支持的事件为ddl_command_start 和 ddl_command_end.

支持触发事件触发器的DDL如下(包括select into) :

http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html

但是触发事件中不包括对系统共享对象的CREATE, ALTER, DROP操作, 如 :

databases, roles, and tablespaces

同样对事件触发器本身的DDL操作也不会触发事件触发器.

The ddl_command_start event occurs just before the execution of a CREATE, ALTER, or DROP command. As an exception, however, this event does not occur for DDL commands targeting shared objects - databases, roles, and tablespaces - or for command targeting event triggers themselves. The event trigger mechanism does not support these object types. ddl_command_start also occurs just before the execution of a SELECT INTO command, since this is equivalent to CREATE TABLE AS. 
The ddl_command_end event occurs just after the execution of this same set of commands.

-- filter_variable目前只支持TAG

-- filter_value是http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html这里定义的DDL

-- function_name就是我们创建好的事件触发器函数.

以plpgsql函数语言为例讲解事件触发器函数的创建方法 :

PL/pgSQL can be used to define event triggers. PostgreSQL requires that a procedure that is to be called as an event trigger must be declared as a function with no arguments and a return type of event_trigger.
When a PL/pgSQL function is called as a event trigger, several special variables are created automatically in the top-level block. They are:
TG_EVENT
Data type text; a string representing the event the trigger is fired for.
TG_TAG
Data type text; variable that contains the command tag for which the trigger is fired.

事件触发器函数的返回类型为event_trigger, 同时事件触发器的顶级块带入了两个特殊变量, TG_EVENT和TG_TAG.

TG_EVENT表示EVENT信息, 如现在支持的为ddl_command_start 和 ddl_command_end.

TG_TAG表示的是DDL信息, 信息在http://www.postgresql.org/docs/devel/static/event-trigger-matrix.html查询.

【如果同一个事件上建立了多个事件触发器, 执行顺序按触发器名字的字母先后顺序来执行, 这个和普通触发器的触发规则是一样的.】

如下 :

-- 创建两个触发器函数, 返回event_trigger类型 :

CREATE OR REPLACE FUNCTION etgr1()   
  RETURNS event_trigger                  
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE NOTICE 'this is etgr1, event:%, command:%', tg_event, tg_tag;
 END;
$$;

CREATE OR REPLACE FUNCTION etgr2()   
  RETURNS event_trigger                  
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE NOTICE 'this is etgr2, event:%, command:%', tg_event, tg_tag;
 END;
$$;

-- 创建事件触发器, 这里未使用WHEN, 也就是所有的DDL都触发这些事件触发器(除了前面提到的触发器本身的DDL和共享对象的DDL) :

CREATE EVENT TRIGGER b ON ddl_command_start EXECUTE PROCEDURE etgr1();
CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE etgr2();

-- 同一个事件类型ddl_command_start下创建了2个事件触发器, 事件触发器的名称分别为a和b, 调用的先后顺序按字母顺序来, 如下 :

digoal=# create table digoal(id int);
NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE
CREATE TABLE

-- 查询当前数据库中有哪些事件触发器 :

digoal=# select * from pg_event_trigger ;
 evtname |     evtevent      | evtowner | evtfoid | evtenabled | evttags 
---------+-------------------+----------+---------+------------+---------
 b       | ddl_command_start |       10 |   16669 | O          | 
 a       | ddl_command_start |       10 |   16671 | O          | 
(2 rows)

-- evtowner是创建事件触发器的用户, 例如上面两个事件触发器我是用postgres用户创建的。

digoal=# select rolname from pg_roles where oid=10;
 rolname  
----------
 postgres
(1 row)

-- evtfoid指事件触发器函数的oid,

digoal=# select proname from pg_proc where oid=16669;
 proname 
---------
 etgr1
(1 row)
digoal=# select proname from pg_proc where oid=16671;
 proname 
---------
 etgr2
(1 row)

【事件触发器和DDL语句本身是在同一个事务中处理的, 所以任何事件触发器抛出异常的话, 整个事务都会回滚, 并且后续的操作也不会执行下去.】

例如 :

-- 创建事件触发器函数, 函数直接抛出异常.

digoal=# create or replace function abort1() returns event_trigger as $$
declare
begin
  raise exception 'event:%, command:%. abort.', TG_EVENT, TG_TAG;
end;
$$ language plpgsql;

-- 创建ddl_command_end 事件触发器

digoal=# create event trigger tg_abort1 on ddl_command_end execute procedure abort1();
CREATE EVENT TRIGGER

-- 执行DDL语句, 如下, 在调用了a和b事件触发器后, 最后调用ddl_command_end的触发器, 抛出异常

digoal=# create table digoal1(id int);
NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE
ERROR:  event:ddl_command_end, command:CREATE TABLE. abort.

-- 异常导致表创建失败

digoal=# \d digoal1
Did not find any relation named "digoal1".

-- 再创建1个事件触发器, 放在ddl_command_start 事件中

digoal=# create event trigger tg_abort2 on ddl_command_start execute procedure abort1();
CREATE EVENT TRIGGER
digoal=# create table digoal1(id int);
NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE
NOTICE:  this is etgr1, event:ddl_command_start, command:CREATE TABLE
ERROR:  event:ddl_command_start, command:CREATE TABLE. abort.

-- 同样会导致DDL执行失败. 这就达到了禁止执行DDL的目的.

digoal=# create event trigger abort2 on ddl_command_start execute procedure abort1();
CREATE EVENT TRIGGER
digoal=# create table digoal1(id int);
NOTICE:  this is etgr2, event:ddl_command_start, command:CREATE TABLE
ERROR:  event:ddl_command_start, command:CREATE TABLE. abort.
digoal=# \d digoal1
Did not find any relation named "digoal1".

-- 当前数据库中的事件触发器如下

digoal=# select * from pg_event_trigger ;
  evtname  |     evtevent      | evtowner | evtfoid | evtenabled | evttags 
-----------+-------------------+----------+---------+------------+---------
 b         | ddl_command_start |       10 |   16669 | O          | 
 a         | ddl_command_start |       10 |   16671 | O          | 
 tg_abort1 | ddl_command_end   |       10 |   16676 | O          | 
 tg_abort2 | ddl_command_start |       10 |   16676 | O          | 
 abort2    | ddl_command_start |       10 |   16676 | O          | 
(5 rows)

【事件触发器应用举例 : 】

1. 禁止postgres用户在数据库digoal中执行CREATE TABLE和DROP TABLE命令.

首先把已有的事件触发器删除, 方便观看测试效果.

digoal=# drop event trigger tg_abort1;
DROP EVENT TRIGGER
digoal=# drop event trigger tg_abort2;
DROP EVENT TRIGGER
digoal=# drop event trigger abort2;
DROP EVENT TRIGGER
digoal=# drop event trigger a;
DROP EVENT TRIGGER
digoal=# drop event trigger b;
DROP EVENT TRIGGER
digoal=# select * from pg_event_trigger ;
 evtname | evtevent | evtowner | evtfoid | evtenabled | evttags 
---------+----------+----------+---------+------------+---------
(0 rows)

-- 创建触发器函数 :

CREATE OR REPLACE FUNCTION abort()   
  RETURNS event_trigger                  
 LANGUAGE plpgsql
  AS $$
BEGIN
  if current_user = 'postgres' then
    RAISE EXCEPTION 'event:%, command:%', tg_event, tg_tag;
  end if;
 END;
$$;

-- 创建触发器 :

digoal=# create event trigger a on ddl_command_start when TAG IN ('CREATE TABLE', 'DROP TABLE') execute procedure abort();
CREATE EVENT TRIGGER
digoal=# select * from pg_event_trigger ;
 evtname |     evtevent      | evtowner | evtfoid | evtenabled |            evttags            
---------+-------------------+----------+---------+------------+-------------------------------
 a       | ddl_command_start |       10 |   16683 | O          | {"CREATE TABLE","DROP TABLE"}
(1 row)

-- 测试postgres用户是否可以使用create table和drop table .

digoal=# \c digoal postgres
You are now connected to database "digoal" as user "postgres".
-- 无法新建表了
digoal=# create table new(id int);
ERROR:  event:ddl_command_start, command:CREATE TABLE
digoal=# \d new
Did not find any relation named "new".
digoal=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner   
--------+---------+-------+----------
 public | digoal  | table | postgres
 public | digoal1 | table | postgres
 public | test    | table | postgres
(3 rows)
-- 无法删表了
digoal=# drop table digoal;
ERROR:  event:ddl_command_start, command:DROP TABLE
digoal=# \d digoal
    Table "public.digoal"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer |

-- 测试其他用户是否会有影响

digoal=# \c digoal digoal
You are now connected to database "digoal" as user "digoal".
digoal=> create table tbl(id int);
CREATE TABLE
digoal=> drop table tbl;
DROP TABLE
-- 未受到影响.

【其他】

1. 事件触发器还可以结合会话参数session_replication_role来使用, 例如仅针对replica角色生效, 其他不生效.

Command:     ALTER EVENT TRIGGER
Description: change the definition of an event trigger
Syntax:
ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO new_owner
ALTER EVENT TRIGGER name RENAME TO new_name

具体用法可参见trigger的用法介绍 :

http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

2. 我们知道PostgreSQL没有像Oracle里面的DBA_OBJECTS表, 无法得知创建时间, ALTER时间.

使用事件触发器这个将会变成可能, 但是目前的事件触发器函数仅仅支持TG_EVENT和TG_TAG变量, 如果能加入TG_RELID, 那么就可以在DDL的时候记录这个事件到一个对象表中. 从而达到跟踪对象被执行DDL的时间的目的.

3. 事件触发器实际上是通过钩子实现的，例如 InvokeObjectPostCreateHook 在创建对象结束时调用。

src/backend/catalog/objectaccess.c

/*
 * RunObjectPostCreateHook
 *
 * It is entrypoint of OAT_POST_CREATE event
 */
void
RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,
                                                bool is_internal)
{
        ObjectAccessPostCreate pc_arg;

        /* caller should check, but just in case... */
        Assert(object_access_hook != NULL);

        memset(&pc_arg, 0, sizeof(ObjectAccessPostCreate));
        pc_arg.is_internal = is_internal;

        (*object_access_hook) (OAT_POST_CREATE,
                                                   classId, objectId, subId,
                                                   (void *) &pc_arg);
}

src/include/catalog/objectaccess.h

/* Core code uses these functions to call the hook (see macros below). */
extern void RunObjectPostCreateHook(Oid classId, Oid objectId, int subId,
                                                bool is_internal);
extern void RunObjectDropHook(Oid classId, Oid objectId, int subId,
                                  int dropflags);
extern void RunObjectPostAlterHook(Oid classId, Oid objectId, int subId,
                                           Oid auxiliaryId, bool is_internal);
extern bool RunNamespaceSearchHook(Oid objectId, bool ereport_on_volation);
extern void RunFunctionExecuteHook(Oid objectId);
。。。。。。
/*
 * The following macros are wrappers around the functions above; these should
 * normally be used to invoke the hook in lieu of calling the above functions
 * directly.
 */

#define InvokeObjectPostCreateHook(classId,objectId,subId)                      \
        InvokeObjectPostCreateHookArg((classId),(objectId),(subId),false)
#define InvokeObjectPostCreateHookArg(classId,objectId,subId,is_internal) \
        do {                                                                                                                    \
                if (object_access_hook)                                                                         \
                        RunObjectPostCreateHook((classId),(objectId),(subId),   \
                                                                        (is_internal));                                 \
        } while(0)

。。。。。。

在函数中执行DDL，同样被审查，因为HOOK不是语义层面的，而是执行层面的。

例如：

postgres=# create or replace function fe() returns event_trigger as $$
declare
begin
  if current_user = 'digoal' then
    raise exception 'can not execute ddl';
  end if;
end;
$$ language plpgsql strict;
CREATE FUNCTION

postgres=# CREATE EVENT TRIGGER a ON ddl_command_start EXECUTE PROCEDURE fe();
CREATE EVENT TRIGGER

postgres=# \c postgres digoal
You are now connected to database "postgres" as user "digoal".

postgres=> create table tbl(id int);
ERROR:  can not execute ddl

postgres=> do language plpgsql $$
postgres$> declare
postgres$> begin
postgres$>   execute 'create table tbl (id int)';
postgres$> end;
postgres$> $$;
ERROR:  can not execute ddl
CONTEXT:  SQL statement "create table tbl (id int)"
PL/pgSQL function inline_code_block line 4 at EXECUTE statement

【参考】

1. http://www.postgresql.org/docs/devel/static/event-triggers.html

2. http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html

3. http://www.postgresql.org/docs/devel/static/sql-createeventtrigger.html
4. http://www.postgresql.org/docs/devel/static/catalog-pg-event-trigger.html

5. http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

6. http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

7. http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=3a0e4d36ebd7f477822d5bae41ba121a40d22ccc