这里的event 其实是ddl event, 就是当我们下 create , drop 这类ddl时,会产生ddl event, 而event trigger 就是捕捉相对应的event,然後触发.
既然可以捕捉相对应的event,就可以做纪录,或是通知,甚至更进一步的控制.
概述
语法
函数
Event Trigger Firing Matrix
System Catalogs
create or replace function f_event_trigger_demo()
returns event_trigger
language 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$;
create event trigger tr_demo
on ddl_command_end
execute function f_event_trigger_demo();
commit;
分别是
ddl_command_start
ddl_command_end
sql_drop
table_rewrite
可以参考官网文件中的 Event Trigger Firing Matrix(上面有连结)
create table foo();
INFO: 00000: Event trigger function called
INFO: 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.
alter event trigger tr_demo disable;
-- 这时候 tr_demo 也没有发出 info
commit;
create event trigger tr_demo2
on ddl_command_start
execute 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)
create table foo();
INFO: 00000: Event trigger function called
drop 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.
create or replace function f_event_trigger_demo()
returns event_trigger
language 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 TABLE
drop table foo;
INFO: 00000: Event trigger function called , event [ddl_command_start] tag [DROP TABLE]
DROP TABLE
-- 可以看到 TG_EVENT, TG_TAG
-- 变更为 on ddl_command_end
alter 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 TABLE
drop table foo;
INFO: 00000: Event trigger function called , event [ddl_command_end] tag [DROP TABLE]
DROP TABLE
-- 接着做其他型态 ddl
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]
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() 并无法传回相关资讯.
因为drop 有其特殊性 所以除了是一般的ddl event,还是sql_drop event. 在 Event Trigger Firing Matrix 中,以及上面测试的观察,我们可以得知,需要对drop event,建立另外的trigger.
create or replace function f_event_trigger_for_drops()
returns event_trigger
language 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_drops
on sql_drop
execute function f_event_trigger_for_drops();
commit;
在建立上面的event trigger 与 function 时,
因为是 ddl, 所以上面的 trigger 会列印出讯息,为简明起见,
在此不列出.
上面的function 中,使用了 current_query() ,是为了说明方便使用.
在实际应用上,可以弹性增加使用.
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_idx
INFO: 00000: Event trigger function called , event [ddl_command_end] tag [DROP INDEX]
DROP INDEX
drop table bar;
INFO: 00000: current_query() -> drop table bar;
INFO: 00000: DROP TABLE dropped table s12.bar s12.bar
INFO: 00000: DROP TABLE dropped type s12.bar s12.bar
INFO: 00000: DROP TABLE dropped type s12._bar s12.bar[]
INFO: 00000: Event trigger function called , event [ddl_command_end] tag [DROP TABLE]
DROP TABLE
可以观察到 drop table 还会drop 相关的 type, 这是PostgreSQL内部较为深入的机制了.
因为我们先把index drop 了,所以就没有再产生依赖 table 物件的删除事件.
在实际应用上的 create , drop 会比简单测试的产生更多相关依赖物件的建立或删除.
此次先将 event trigger 及 三个 event 做一个初步的探讨.
後续还有第四个event,以及相关的应用,再做更多的探讨.
<<: [使用者提问的问题]请问一下, 用VPN连入,\\电脑名称 找不到 \\ip 可以用 请问是什麽原因?
>>: 风险承受能力和风险偏好(Risk Capacity and Risk Appetite)
<if> 条件控制 <if> 元素根据 test 属性中的评估值决定其下的元...
前言: 今天来看 类别 / 初始化 / 继承 大纲: class 结构 宣告 名称 主要建构式 ...
好 那今天就是专案的收尾了 我先预告一下明天会把一些我从开始学习 Rust 之後陆续得到的学习资源 ...
日子一天一天过, 周而复始,无限循环 C#里面也有重复不断的语句,while while回圈 执行前...
大家好! 今天要实作能输入网址的 RSS 阅读器。 我们进入今天的主题吧! 程序码 Felix('f...