PostgreSQL事件触发器实战教程

本文带你学习PostgreSQL事件触发器，同时也介绍其他解决的解决方案，如日志DDL，跟踪提交时间戳。通过对比学习可以更好理解事件触发器的优势。

PostgreSQL 提供了非常有用的事件触发器，用于检测数据变化并自动执行后续任务。例如，触发器可用于检测一个表的变化，然后在另一个表上执行相关任务，通常使用这种方式实现集中审计表，其中包括所有表的变化信息。在9.3版本之前DDL变化不能被可靠检测，9.3版本引入事件触发器让其变为可能。

在介绍事件触发其之前，我们先了解日志DDL及 跟踪提交时间戳。

日志DDL

在事件触发器之前，可以使用DDL检测监控表结构变化，通过设置日志参数：

“log_statement = ‘ddl’” (or “log_statement = ‘all’”)

这样可以顺序在日志中记录DDL语句。这种方式的缺点是，尤其对于有大量DML日志的数据库，DDL语句太多被埋没在日志中，尝试解析日志需要花费大量时间，另外由于日志文件滚动导致在部分文件中找不到感兴趣的DDL日志。

跟踪提交时间戳

从9.1版本开始，开始使用track_commit_timestamp 选项，实现事务与时间戳关联，从而可以感知 ddl变化，为记录变化信息至 pg_class铺平了道路。

postgres=# show track_commit_timestamp ;

 track_commit_timestamp
------------------------
 on

(1 row)

postgres=# create table students (id int, name text);
CREATE TABLE

postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students';
   pg_xact_commit_timestamp    |  oid  | relname
-------------------------------+-------+----------
 2021-11-27 10:36:44.467373+08 | 18278 | students
(1 行记录)


postgres=# alter table students add column phone_number text;
ALTER TABLE

postgres=# select pg_xact_commit_timestamp(xmin), oid, relname from pg_class where relname = 'students';
   pg_xact_commit_timestamp    |  oid  | relname
-------------------------------+-------+----------
 2021-11-27 10:37:48.859722+08 | 18278 | students
(1 行记录)

如果 track_commit_timestamp 为 off 可以通过 ALTER SYSTEM SET track_commit_timestamp = on; 设置参数，并重启数据框服务。

当 在表上执行DDL时，在 pg_class中的表实体被更新。通过检测属于表记录的xmin值，我们能看到最后执行的ddl。但通过这种方式跟踪过的问题是，基于 xmin值仅记录最后的 最后 DDL事件。如果需要了解历史，需要频繁从 pg_class 表中拉去信息，而不能自动方式生成记录变化。

事件触发器

使用事件触发器，当DDL发生时可以利用该机制更新历史表，下面我们先创建历史表：

CREATE TABLE ddl_history (
  id serial primary key,
  ddl_date timestamptz,
  ddl_tag text,
  object_name text
);

接着我们需要两个函数，一个跟踪创建、修改对象，另一个跟踪删除对象：

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;

创建好两个函数后，我们创建触发器：

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();

触发器创建好之后，我们来测试下：

postgres=# CREATE TABLE testtable (id int, first_name text);
CREATE TABLE
postgres=# ALTER TABLE testtable ADD COLUMN last_name text;
ALTER TABLE
postgres=# ALTER TABLE testtable ADD COLUMN midlname text;
ALTER TABLE
postgres=# ALTER TABLE testtable RENAME COLUMN midlname TO middle_name;
ALTER TABLE
postgres=# ALTER TABLE testtable DROP COLUMN middle_name;
ALTER TABLE
postgres=# DROP TABLE testtable;
DROP TABLE
postgres=# select * from ddl_history;
 id |           ddl_date            |   ddl_tag    |         object_name
----+-------------------------------+--------------+------------------------------
  1 | 2021-11-27 10:53:07.047529+08 | CREATE TABLE | public.testtable
  2 | 2021-11-27 10:53:19.360351+08 | ALTER TABLE  | public.testtable
  3 | 2021-11-27 10:53:28.442136+08 | ALTER TABLE  | public.testtable
  4 | 2021-11-27 10:53:35.61037+08  | ALTER TABLE  | public.testtable.middle_name
  5 | 2021-11-27 10:53:45.370094+08 | ALTER TABLE  | public.testtable
(5 行记录)

我们看到变化情况，如果读者还想记录更多内容，可以查看 pg_event_trigger_ddl_commands() 更多属性：

Name	Type	Description
classid	Oid	OID of catalog the object belongs in
objid	Oid	OID of the object in the catalog
objsubid	integer	Object sub-id (e.g. attribute number for columns)
command_tag	text	command tag
object_type	text	Type of the object
schema_name	text	Name of the schema the object belongs in, if any; otherwise NULL. No quoting is applied.
object_identity	text	Text rendering of the object identity, schema-qualified. Each and every identifier present in the identity is quoted if necessary.
in_extension	bool	whether the command is part of an extension script
command	pg_ddl_command	A complete representation of the command, in internal format. This cannot be output directly, but it can be passed to other functions to obtain different pieces of information about the command.

总结

事件触发是实现安全审计的强大工具。我们还可以利用它实现其他功能：

• 监控DDL性能
• 对特定用户限制执行DDL
• 对于逻辑复制环境的订阅节点执行DDL复制工作