事件触发器(event trigger)

​专栏内容：

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

个人主页：我的主页

管理社区：开源数据库

座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

文章目录

• 事件触发器(event trigger)
• 概述
• 原理机制
• • ddl_command_start事件
  • ddl_command_end事件
  • table_rewrite事件
  • • table rewrite触发
    • sql_drop事件
    • 语法
    • • 创建触发器的函数
      • 创建事件触发器
      • 案例分析
      • • 策略制定
        • 启用约束策略
        • 总结
        • 结尾

          概述

          ---

          在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
                        $$;
                        

                        创建事件触发器

                        CREATE EVENT TRIGGER trigger_name
                                          ON [ddl_command_start 
                                            | ddl_command_end 
                                            | sql_drop 
                                            | table_rewrite ]
                           EXECUTE FUNCTION trigger_function();
                        

                        • 使用 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;
                            $$;
                            

                            说明

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

                              启用约束策略

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

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

                              总结

                              ---

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

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

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

                              结尾

                              ---

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

                              作者邮箱：study@senllang.onaliyun.com

                              如有错误或者疏漏欢迎指出，互相学习。

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