PostgreSQL 跟踪DDL时间 - cann’t use pg_class’s trigger trace user_table’s create,modify,delete Time
背景
本文适用于没有事件触发器的版本。
最近经常有朋友会问在PostgreSQL中如何查询表的创建时间, MODIFY时间, 删除时间等.
首先来理一理思路:
1. PostgreSQL中pg_class系统表存储了表, 索引, 视图, 序列, TOAST 等数据对象的基本元信息。
每次创建一个新的对象都会往这个系统表插入一条记录。
当涉及到这个系统表的元信息更新时，记录会随之更新。
那么是否可以在pg_class上创建触发器来跟踪一下pg_class里面每条记录的轨迹呢? 从而就可以知道表是什么时候创建的，什么时候被修改的，什么时候被删除的了。就用我前面讲到的HSTORE来存储。
http://blog.163.com/digoal@126/blog/static/163877040201252575529358/
这样可行吗？
答案是不可行，不是因为不能在pg_class上创建触发器，而是触发器没有办法被触发。
如果你需要测试一下的话, 在pg_class上创建触发器前请修改一个隐含参数 : ( allow_system_table_mods = on ) 然后重启数据库.
注意这个参数比较危险，请慎用。
想要得到更详细的情形, 使用gdb进行跟踪.
2. 既然trigger没有办法被触发, 那么rule行不行呢, 我这里测试了是不行的。
3. 那么在pg_class里面还有什么信息可以挖掘的?
还记得tupleHead么? 里面有几个隐含字段, ctic, cmin, cmax, xmin, xmax.
xmin和xmax分别记录的是记录被插入的事务ID和被更新时的事务ID, 而事务ID和时间是挂钩的, 只是事务ID的信息要和时间匹配上的话, 需要检索pg_xlog里面的信息.
而且事务ID是可能被vacuum掉成为frozenXID的, 那就没有参考价值了. 索引使用xmin, xmax来搜寻表的创建时间也就不可取了.
4. 那么外挂一个程序实时的去查询pg_class的新增数据并记录到一个新表行不行呢?
这个办法理论上来说是可行的, pg_class 的 oid字段上是有索引的, 而且oid是往上增长的, (当然系统中不建议创建with oid的用户表)
查询max(oid)的值与自建的用来存储表信息的表的max(oid)进行比较, 不相等则表示有新增数据进入, 把新增的数据插入到自建表即可.
digoal=# create table table_pg_class (reloid oid, relname text, relnsp oid, relkind text, crt_time timestamp);
CREATE TABLE
create or replace function rec_pg_class () returns int as $$
declare
src_max_oid oid;
des_max_oid oid;
begin
select max(oid) into src_max_oid from pg_class;
select max(reloid) into des_max_oid from table_pg_class;
if ( src_max_oid <> des_max_oid or des_max_oid is null ) then
insert into table_pg_class (reloid,relname,relnsp,relkind,crt_time) select t1.oid,t1.relname,t1.relnamespace,t1.relkind,now() from pg_class t1 where oid not in (select reloid from table_pg_class t2);
end if;
return 0;
end;
$$ language plpgsql;
digoal=# create index idx_pg_class_1 on table_pg_class (reloid);
CREATE INDEX
digoal=# create table test (id int);
CREATE TABLE
digoal=# select * from rec_pg_class();
rec_pg_class
--------------
0
(1 row)
digoal=# select count(*) from table_pg_class ;
count
-------
297
(1 row)
digoal=# select count(*) from pg_class;
count
-------
297
(1 row)
digoal=# select * from table_pg_class where relname='test';
reloid | relname | relnsp | relkind | crt_time
--------+---------+--------+---------+----------------------------
24103 | test | 2200 | r | 2012-06-26 23:36:29.653227
(1 row)
digoal=# select * from rec_pg_class();
rec_pg_class
--------------
0
(1 row)
Time: 0.307 ms
digoal=# explain select max(oid) into src_max_oid from pg_class;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Result (cost=0.05..0.06 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.05 rows=1 width=4)
-> Index Scan Backward using pg_class_oid_index on pg_class (cost=0.00..15.15 rows=280 width=4)
Index Cond: (oid IS NOT NULL)
(5 rows)
Time: 0.529 ms
digoal=# explain select max(reloid) into des_max_oid from table_pg_class;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Result (cost=0.04..0.05 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.04 rows=1 width=4)
-> Index Scan Backward using idx_pg_class_1 on table_pg_class (cost=0.00..10.40 rows=294 width=4)
Index Cond: (reloid IS NOT NULL)
(5 rows)
Time: 0.389 ms
rec_pg_class()这个函数的调用可以放在操作系统的crontab里面.
5. 当然传统的方法还有使用csvlog来查询表的创建时间, 这个需要配置postgresql.conf的log_statement=’ddl’, 那么会记录下所有的DDL语句, 当然包含表的创建语句. 以及时间等信息.
6. 查看表对应的文件的时间戳也是一种办法, 只是表在被truncate, cluster, rewrite后都会改变文件的创建时间, 或新建文件. 所以不是太准确.
那么最后，还有什么方法呢?
PostgreSQL支持钩子, 所以用钩子来实现是最靠谱的.
例如
http://pgxn.org/dist/pgaudit/1.0.0/