【postgresql初级使用】事件触发器event trigger，被忽略的table rewrite，组合策略保障重大操作

事件触发器(event trigger)

​专栏内容：

• postgresql使用入门基础
• 手写数据库toadb
• 并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

概述

---

在postgresql 除了普通触发器外，还支持事件触发器(event trigger)。

普通触发器在单个表上捕获 DML事件，与普通触发器不同，事件触发器是数据库的全局触发器，能够捕获 DDL事件，它不限定于那张表。

与常规触发器一样，事件触发器可以使用任何支持事件触发器功能的过程语言或 C 语言来编写。

原理机制

---

事件触发器支持四种事件类型：

• ddl_command_start，在DDL执行前触发；
• ddl_command_end，在DDL执行后触发；
• table_rewrite，表的重写时触发；
• sql_drop，删除数据库时会触发；

下面看看这四种事件类型详细的机制。

ddl_command_start事件

• ddl_command_start事件，可以在CREATE, ALTER, DROP, SECURITY LABEL, COMMENT, GRANT 和 REVOKE这些命令时触发。

• 它触发的时机是在命令执行之前，也就是不会检查受影响的对象是否存在。

• 对于针对数据库级的共享对象（如数据库、角色和表空间）或针对事件触发器本身的 DDL 命令，此事件不会触发。

• 对于 SELECT INTO 命令，它也会触发，因为这与 CREATE TABLE AS 命令等效。

ddl_command_end事件

• ddl_command_end与ddl_command_start触发的DDL命令相同；
• 在触发器中，要获得执行的DDL的信息，可以通过调用 pg_event_trigger_ddl_commands() 函数；
• 触发器在DDL动作发生后（但在事务提交之前）触发，因此可以读取已更改的数据字典。

当然触发器中发生了错误时，事务就会中止，产生回滚。

table_rewrite事件

• 当执行某些 ALTER TABLE 或 ALTER TYPE 命令操作导致表的被重写时，会触发 table_rewrite 事件;
• 其他控制语句（如 CLUSTER 和 VACUUM）也可以引发表的重写，但它们不会触发 table_rewrite 事件；

注意，不是所有的alter table会引起表的重写。

table rewrite触发

这里有一个表的重写的概念，简单理解就是把一张表的所有数据又写入了一遍，保持数据的内容与表的字段定义一致。

比如通过alter table 新增一列：

• 如果此列不指定默认值时，填充就是空值，此时不会引发表的重写，只是修改了表的定义；
• 如果新增列指定了值，但值是一个相同的默认值；在postgresql 11以前的版本，会引发表的重写，将该值更新到每一行数据上；pg11的版本中做了优化，也只记录到表定义中；
• 而对于新增列，与时间相关的，采用了不同的值，那么就会触发表的重写，会将新列的值更新到每一行数据上；

当表中的数据非常多时，表的重写是非常可怕的，有两种方式避免产生：

• 一是通过软件开发规范约束，对于表重写的DDL，先设置空值，再用update进行更新；
• 二是采用table_rewrite事件，制定执行的策略，限制执行用户的权限，同时限制当表大于多少时禁止执行；

sql_drop事件

• 当执行任何删除数据库对象的操作（如 DROP TABLE、DROP INDEX 等）时，在 ddl_command_end 事件之前会触发 sql_drop 事件。
• 要列出已删除的对象，可以使用 pg_event_trigger_dropped_objects() 函数。这个函数也是集合返回函数，可以在 sql_drop 事件触发器的代码中使用。
• 要特别注意的是，触发器在对象已经从系统目录中删除后执行，因此无法再查找这些对象。

语法

---

事件触发器的创建步骤与普通触发器类似，也需要先创建触发器执行函数，然后再创建触发器。

创建触发器的函数

事件触发器的执行函数，也是没有参数，但是返回值必须是event_trigger类型；

CREATE OR REPLACE FUNCTION trigger_function()
 RETURNS event_trigger
 LANGUAGE plpgsql AS
$$
BEGIN
...
END
$$;
1
2
3
4
5
6
7
8

创建事件触发器

CREATE EVENT TRIGGER trigger_name
                  ON [ddl_command_start 
                    | ddl_command_end 
                    | sql_drop 
                    | table_rewrite ]
   EXECUTE FUNCTION trigger_function();
1
2
3
4
5
6

• 使用 create event命令创建事件触发器；
• 在on子句后面，指定触发的事件；这些事件作用的对角为当前数据库范围；
• 最后excute function 指定执行函数；

案例分析

---

表的重写，在平时维护数据库是都不会太关注，最新的postgresql 也优化了很多，非必要场景已经不再出现，但是它还是非常的危险，尤其在生产环境上进行维护时。

下面我们通过 table_rewrite事件触发器，来制定一个限制策略，来避免对业务的影响。

策略制定

通过事件触发器函数来制定rewrite事件触发的规则：

• 核心业务表 employee 不能有表的重写，它是基础表，数据量和业务都比较多，所以限制它；
• 其它表，当表的数据块大于100时，说明表中数据量比较大，也不允许；那样会引起IO峰值；
• 另外，此类操作只允许在凌晨1-6点进行操作，此时间段业务非常少；

对于上述三条规则，实现如下函数；

CREATE OR REPLACE FUNCTION rewrite_rule_fun()
 RETURNS event_trigger
 LANGUAGE plpgsql AS
$$
DECLARE
  table_oid oid := pg_event_trigger_table_rewrite_oid();
  current_hour integer := extract('hour' from current_time);
  pages integer;
  max_pages integer := 100;
BEGIN
  -- 规则一
  IF pg_event_trigger_table_rewrite_oid() = 'public.employee'::regclass
  THEN
        RAISE EXCEPTION 'you''re not allowed to rewrite the table %',
                        table_oid::regclass;
  END IF;

  -- 规则二
  SELECT INTO pages relpages FROM pg_class WHERE oid = table_oid;
  IF pages > max_pages
  THEN
        RAISE EXCEPTION 'rewrites only allowed for table with less than % pages',
                        max_pages;
  END IF;

  -- 规则三
  IF current_hour NOT BETWEEN 1 AND 6
  THEN
        RAISE EXCEPTION 'rewrites only allowed between 1am and 6am';
  END IF;
END;
$$;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

说明

• 使用pg_event_trigger_table_rewrite_oid 获得引起表重写的数据库对象OID；
• 表的数据块数量记录在pg_class表中，但是它不是非常准确的值，依赖于analyze；

启用约束策略

制定好策略之后，就启用吧，下面创建当前数据库的事件触发器。

CREATE EVENT TRIGGER tri_rewrite_rule
                  ON table_rewrite
   EXECUTE FUNCTION rewrite_rule_fun();
1
2
3

总结

---

postgresql 中的事件触发器，可以指定的事件有 ddl_command_start ddl_command_end table_rewrite sql_drop,

它可以让我们制定对这些事件的约束策略，当然也可以实现之前的审计案例。

其中特别要注意表的重写事件，它是一个经常被忽视，对业务影响非常大的事件，可以通过一系列规则进行限制。

结尾

---

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！