# PostgreSQL Event Trigger 初探
## 什麼是Event Trigger?
這裡的event 其實是ddl event, 就是當我們下 create , drop 這類ddl時,會產生ddl event, 而event trigger 就是捕捉相對應的event,然後觸發.
## 用途
既然可以捕捉相對應的event,就可以做紀錄,或是通知,甚至更進一步的控制.
## 官方文件
[概述](https://www.postgresql.org/docs/current/event-trigger-definition.html)
[語法](https://www.postgresql.org/docs/current/sql-createeventtrigger.html)
[函數](https://www.postgresql.org/docs/current/functions-event-triggers.html)
[Event Trigger Firing Matrix](https://www.postgresql.org/docs/current/event-trigger-matrix.html)
[System Catalogs](https://www.postgresql.org/docs/current/catalog-pg-event-trigger.html)
## 範例
### 先建立一個 event trigger function
```sql=
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$;
```
### 建立event trigger
```sql=
create event trigger tr_demo
on ddl_command_end
execute 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 測試
```sql=
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.
### 修改 event trigger
```sql=
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)
```
### 再次測試 ddl
```sql=
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.
### 再來修改一下函數 增加列印的資訊,變化loop裡的資訊
```sql=
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$;
```
### 第三次測試
```sql=
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() 並無法傳回相關資訊.
### sql_drop event
因為drop 有其特殊性 所以除了是一般的ddl event,還是sql_drop event. 在 Event Trigger Firing Matrix 中,以及上面測試的觀察,我們可以得知,需要對drop event,建立另外的trigger.
### 建立 sql_drop event trigger
```sql=
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 與 drop table
```sql=
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
```
#### sql_drop event trigger 測試探討
可以觀察到 drop table 還會drop 相關的 type, 這是PostgreSQL內部較為深入的機制了.
因為我們先把index drop 了,所以就沒有再產生依賴 table 物件的刪除事件.
在實際應用上的 create , drop 會比簡單測試的產生更多相關依賴物件的建立或刪除.
## 結語
此次先將 event trigger 及 三個 event 做一個初步的探討.
後續還有第四個event,以及相關的應用,再做更多的探討.
Sign in with Wallet
Connect another wallet