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

事件触发器(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

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

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