PostgreSQL Event Trigger 初探

什么是Event Trigger?

这里的event 其实是ddl event, 就是当我们下 create , drop 这类ddl时,会产生ddl event, 而event trigger 就是捕捉相对应的event,然后触发.

用途

既然可以捕捉相对应的event,就可以做纪录,或是通知,甚至更进一步的控制.

官方文件

概述
语法
函数
Event Trigger Firing Matrix
System Catalogs

範例

先建立一个 event trigger function

create or replace function f_event_trigger_demo()returns event_triggerlanguage plpgsql as $code$declare  event_tuple record;begin  raise info 'Event trigger function called ';  for event_tuple in      select *        from pg_event_trigger_ddl_commands() loop              raise info 'command_tag [%] object_type [%]', event_tuple.command_tag, event_tuple.object_type;    end loop;end$code$;

建立event trigger

create event trigger tr_demoon ddl_command_endexecute function f_event_trigger_demo();commit;

注意到event trigger 是 on event , 有四种event,

分别是

ddl_​command_​start ddl_​command_​end sql_​drop table_​rewrite

可以参考官网文件中的 Event Trigger Firing Matrix(上面有连结)

ddl 测试

create table foo();INFO:  00000: Event trigger function calledINFO:  00000: command_tag [CREATE TABLE] object_type [table]drop table foo;INFO:  00000: Event trigger function called

测试结果探讨

create 时 除了第一道 raise info 以外,还有loop 里面的
会将 command_tag, object_type 用 raise info 列印出来.
而 drop 时, 只有执行了第一道.
会不会是event trigger 设为 on ddl_command_end的关係?
我们先将现在的event trigger tr_demo disable,然后使用同样的function,建立 on ddl_command_start 的 event trigger.

修改 event trigger

alter event trigger tr_demo disable;-- 这时候 tr_demo 也没有发出 infocommit;create event trigger tr_demo2on ddl_command_startexecute function f_event_trigger_demo();commit;-- 使用meta command 观察 event trigger# \dy tr_demo*                             List of event triggers   Name   |       Event       | Owner  | Enabled  |       Function       | Tags----------+-------------------+--------+----------+----------------------+------ tr_demo  | ddl_command_end   | pagila | disabled | f_event_trigger_demo | tr_demo2 | ddl_command_start | pagila | enabled  | f_event_trigger_demo |(2 rows)

再次测试 ddl

create table foo();INFO:  00000: Event trigger function calleddrop table foo;INFO:  00000: Event trigger function called

第二次测试的探讨

可以观察到 on ddl_command_start , 只有前面的 rasie info,
而 pg_event_trigger_ddl_commands() 产生的是空的,所以for loop 是没有列印.由此可以得知,一般要做纪录时,适合使用捕捉 ddl_command_end event.

再来修改一下函数 增加列印的资讯,变化loop里的资讯

create or replace function f_event_trigger_demo()returns event_triggerlanguage plpgsql as $code$declare  event_tuple record;begin  raise info 'Event trigger function called , event [%] tag [%]', TG_EVENT, TG_TAG;  for event_tuple in      select *        from pg_event_trigger_ddl_commands() loop              raise info 'object_type [%] object_identity [%]', event_tuple.object_type, event_tuple.object_identity;    end loop;end$code$;

第三次测试

create table foo();INFO:  00000: Event trigger function called , event [ddl_command_start] tag [CREATE TABLE]CREATE TABLEdrop table foo;INFO:  00000: Event trigger function called , event [ddl_command_start] tag [DROP TABLE]DROP TABLE-- 可以看到 TG_EVENT, TG_TAG-- 变更为 on ddl_command_endalter event trigger tr_demo2 disable;alter event trigger tr_demo enable;commit;create table foo();INFO:  00000: Event trigger function called , event [ddl_command_end] tag [CREATE TABLE]INFO:  00000: object_type [table] object_identity [s12.foo]CREATE TABLEdrop table foo;INFO:  00000: Event trigger function called , event [ddl_command_end] tag [DROP TABLE]DROP TABLE-- 接着做其他型态 ddlcreate table foo ();INFO:  00000: Event trigger function called , event [ddl_command_end] tag [CREATE TABLE]INFO:  00000: object_type [table] object_identity [s12.foo]alter table foo add column i int;INFO:  00000: Event trigger function called , event [ddl_command_end] tag [ALTER TABLE]INFO:  00000: object_type [table] object_identity [s12.foo]create index on foo(i);INFO:  00000: Event trigger function called , event [ddl_command_end] tag [CREATE INDEX]INFO:  00000: object_type [index] object_identity [s12.foo_i_idx]alter table foo rename to bar;INFO:  00000: Event trigger function called , event [ddl_command_end] tag [ALTER TABLE]INFO:  00000: object_type [table] object_identity [s12.bar]

第三次测试的探讨

根据上面的实做,能够理解 ddl_​command_​start , ddl_​command_​end 两个 event 以及 pg_event_trigger_ddl_commands() 的搭配使用方法,及其不足之处.
在 drop 的时候,只能是 trigger 基本的变数,而pg_event_trigger_ddl_commands() 并无法传回相关资讯.

sql_drop event

因为drop 有其特殊性 所以除了是一般的ddl event,还是sql_​drop event. 在 Event Trigger Firing Matrix 中,以及上面测试的观察,我们可以得知,需要对drop event,建立另外的trigger.

建立 sql_drop event trigger

create or replace function f_event_trigger_for_drops()returns event_triggerlanguage plpgsql as$code$declare  event_tuple record;begin  raise info 'current_query() -> %', current_query();    for event_tuple in select * from pg_event_trigger_dropped_objects()  loop    raise info '% dropped % %.% %',      TG_TAG,      event_tuple.object_type,      event_tuple.schema_name,      event_tuple.object_name,      event_tuple.object_identity;        end loop;end;$code$;create event trigger test_event_trigger_for_dropson sql_dropexecute function f_event_trigger_for_drops();commit;

在建立上面的event trigger 与 function 时,
因为是 ddl, 所以上面的 trigger 会列印出讯息,为简明起见,
在此不列出.
上面的function 中,使用了 current_query() ,是为了说明方便使用.
在实际应用上,可以弹性增加使用.

测试 drop index 与 drop table

drop index s12.foo_i_idx;INFO:  00000: current_query() -> drop index s12.foo_i_idx;INFO:  00000: DROP INDEX dropped index s12.foo_i_idx s12.foo_i_idxINFO:  00000: Event trigger function called , event [ddl_command_end] tag [DROP INDEX]DROP INDEXdrop table bar;INFO:  00000: current_query() -> drop table bar;INFO:  00000: DROP TABLE dropped table s12.bar s12.barINFO:  00000: DROP TABLE dropped type s12.bar s12.barINFO:  00000: DROP TABLE dropped type s12._bar s12.bar[]INFO:  00000: Event trigger function called , event [ddl_command_end] tag [DROP TABLE]DROP TABLE

sql_drop event trigger 测试探讨

可以观察到 drop table 还会drop 相关的 type, 这是PostgreSQL内部较为深入的机制了.
因为我们先把index drop 了,所以就没有再产生依赖 table 物件的删除事件.
在实际应用上的 create , drop 会比简单测试的产生更多相关依赖物件的建立或删除.

结语

此次先将 event trigger 及 三个 event 做一个初步的探讨.
后续还有第四个event,以及相关的应用,再做更多的探讨.