use event trigger function record user who alter table’s SQL

背景

如果你要记录用户表被DDL修改定义, 设置默认值, 等等alter table可以完成的工作时的记录, 可以使用事件触发器来达到这个目的.

例子如下 :

postgres=# create extension hstore;  
  
postgres=# create or replace function ef_alter() returns event_trigger as $$  
declare  
  rec hstore;  
begin  
  select hstore(pg_stat_activity.*) into rec from pg_stat_activity where pid=pg_backend_pid();  
  insert into aud_alter (ctx) values (rec);  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
postgres=# create event trigger e_alter on ddl_command_end when tag in ('ALTER TABLE') execute procedure ef_alter();  
CREATE EVENT TRIGGER  
  
postgres=# create table aud_alter(id serial primary key, crt_time timestamp default now(), ctx hstore);  
CREATE TABLE  
  
postgres=# create table test(id int);  
CREATE TABLE  
  
postgres=# alter table test alter column id type int8;  
ALTER TABLE  
  
postgres=# select * from aud_alter;  
 id |          crt_time          |                                                                                                    
                                                                                                                                      
       ctx                                                                                                                            
                                                                                                                      
----+----------------------------+--------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
------------------------------------------------------------------------------------------------------------------------------------  
--------------------------------------------------------------------------------------------------------------------  
  1 | 2014-12-12 05:43:42.840327 | "pid"=>"48406", "datid"=>"12949", "query"=>"alter table test alter column id type int8;", "state"  
=>"active", "datname"=>"postgres", "usename"=>"postgres", "waiting"=>"f", "usesysid"=>"10", "xact_start"=>"2014-12-12 05:43:42.84032  
7+08", "client_addr"=>NULL, "client_port"=>"-1", "query_start"=>"2014-12-12 05:43:42.840327+08", "state_change"=>"2014-12-12 05:43:4  
2.840331+08", "backend_start"=>"2014-12-12 05:38:37.084733+08", "client_hostname"=>NULL, "application_name"=>"psql"  
(1 row)  
  
  
postgres=# select each(ctx) from aud_alter where id=1;  
                         each                            
-------------------------------------------------------  
 (pid,48406)  
 (datid,12949)  
 (query,"alter table test alter column id type int8;")  
 (state,active)  
 (datname,postgres)  
 (usename,postgres)  
 (waiting,f)  
 (usesysid,10)  
 (xact_start,"2014-12-12 05:43:42.840327+08")  
 (client_addr,)  
 (client_port,-1)  
 (query_start,"2014-12-12 05:43:42.840327+08")  
 (state_change,"2014-12-12 05:43:42.840331+08")  
 (backend_start,"2014-12-12 05:38:37.084733+08")  
 (client_hostname,)  
 (application_name,psql)  
(16 rows)  

query即当时的ALTER TABLE SQL.

其他辅助的还有用户当时的IP, PORT, 用户, 链接的数据库等信息.

如果只想跟踪表的字段被修改的前后类型, 更严格的做法应该是从parsetree中取出被修改的字段, 类型.

注意

如果用户将DDL封装在函数中执行，那么pg_stat_activity得到的就是函数调用。

参考

1. http://www.postgresql.org/docs/9.3/static/event-triggers.html

2. http://www.postgresql.org/docs/9.3/static/sql-createeventtrigger.html

3. src/backend/commands/event_trigger.c

4. src/include/commands/event_trigger.h

5. 《USE hstore store table’s trace record》

6. http://www.postgresql.org/docs/9.3/static/event-trigger-interface.html

typedef struct EventTriggerData  
{  
    NodeTag     type;  
    const char *event;      /* event name */  
    Node       *parsetree;  /* parse tree */  
    const char *tag;        /* command tag */  
} EventTriggerData;  

digoal’s 大量PostgreSQL文章入口