核心内容摘要
xxxxxxxxxx69：探寻神秘数字背后的无限可能
文章目录环境文档用途详细信息环境系统平台Linux x
Red Hat Enterprise Linux 7版本
4.
2文档用途使用事件触发器审计表的alter table操作详细信息如果你要记录表被用户执行DDL修改定义, 设置默认值, 等等alter table可以完成的工作时的记录, 可以使用事件触发器来达到这个目的.例子如下 :# create or replace function ef_alter() returns event_trigger as $$declarerec hstore;beginselecthstore(pg_stat_activity.*)intorecfrompg_stat_activitywherepidpg_backend_pid();insertintoaud_alter(ctx)values(rec);end;$$languageplpgsql strict;CREATEFUNCTIONtestdb# create event trigger e_alter on ddl_command_end when tag in (ALTER TABLE) execute procedure ef_alter();CREATEEVENTTRIGGERtestdb# create table aud_alter(id serial primary key, crt_time timestamp default now(), ctx hstore);CREATETABLEtestdb# create table test(id int);CREATETABLEtestdb# alter table test alter column id type int8;ALTERTABLEtestdb# select * from aud_alter;-[RECORD1]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------id|1crt_time|
:44:
1
309983ctx|pid9408,datid16384,queryalter table test alter column id type int8;,stateactive,datnametestdb,usenamepostgres,waitingf,usesysid10,xact_start
13:44:
1
30998308,backend_xid8367,client_addrNULL,client_port-1,query_start
13:44:
1
30998308,backend_xmin8367,state_change
13:44:
1
30998508,backend_start
13:40:
1
056108,client_hostnameNULL,application_namepsqltestdb# select each(ctx) from aud_alter where id1;each-------------------------------------------------------(pid,
(datid,
(query,alter table test alter column id type int8;)(state,active)(datname,testdb)(usename,postgres)(waiting,f)(usesysid,
(xact_start,
13:44:
12.
(backend_xid,
(client_addr,)(client_port,-
(query_start,
13:44:
12.
(backend_xmin,
(state_change,
13:44:
12.
(backend_start,
13:40:
15.
(client_hostname,)(application_name,psql)(18rows)query即当时的ALTER TABLE SQL.其他辅助的还有用户当时的IP, PORT, 用户, 链接的数据库等信息。
如果只想跟踪表的字段被修改的前后类型, 更严格的做法应该是从parsetree中取出被修改的字段, 类型。