PostgreSQL 跟踪谁动了你的记录 - Use insert_username Tracking Who Changed a Table

背景

上一篇BLOG讲的是使用timetravel跟踪TUPLE的DML操作，但是对于有PK的表没有办法实现跟踪，因为每次的UPDATE和DELETE都需要新建一条记录。

http://blog.163.com/digoal@126/blog/static/16387704020120133019990/

insert_username这个函数是用来跟踪TUPLE被哪个用户修改或者插入的，不涉及新建记录的情况，因此有PK的表也可以跟踪，如下是测试过程 :

首先创建insert_username extension;

digoal=> \c digoal pg92  
You are now connected to database "digoal" as user "pg92".  
digoal=# create extension insert_username;  
CREATE EXTENSION  

创建测试表

digoal=# \c digoal digoal  
You are now connected to database "digoal" as user "digoal".  
digoal=> create table trace_username (id int primary key,info text,username text);  
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trace_username_pkey" for table "trace_username"  
CREATE TABLE  

创建触发器

digoal=> CREATE TRIGGER insert_usernames  
digoal->         BEFORE INSERT OR UPDATE ON trace_username  
digoal->         FOR EACH ROW  
digoal->         EXECUTE PROCEDURE insert_username (username);  
CREATE TRIGGER  

插入测试记录

digoal=> insert into trace_username (id,info) select generate_series(1,10),'digoal';  
INSERT 0 10  

可以看到username字段被自动填充了插入这些记录时使用的用户名.

digoal=> select * from trace_username ;  
 id |  info  | username   
----+--------+----------  
  1 | digoal | digoal  
  2 | digoal | digoal  
  3 | digoal | digoal  
  4 | digoal | digoal  
  5 | digoal | digoal  
  6 | digoal | digoal  
  7 | digoal | digoal  
  8 | digoal | digoal  
  9 | digoal | digoal  
 10 | digoal | digoal  
(10 rows)  

连接到pg92用户下面，测试插入和更新记录.

digoal=> \c digoal pg92  
You are now connected to database "digoal" as user "pg92".  

使用pg92用户插入的记录username=’pg92’

digoal=# insert into digoal.trace_username (id,info) values (11,'digoal');  
INSERT 0 1  
digoal=# select * from digoal.trace_username ;  
 id |  info  | username   
----+--------+----------  
  1 | digoal | digoal  
  2 | digoal | digoal  
  3 | digoal | digoal  
  4 | digoal | digoal  
  5 | digoal | digoal  
  6 | digoal | digoal  
  7 | digoal | digoal  
  8 | digoal | digoal  
  9 | digoal | digoal  
 10 | digoal | digoal  
 11 | digoal | pg92  
(11 rows)  

测试使用pg92用户更新一条tuple, 更新后看到username=’pg92’

digoal=# update digoal.trace_username set info='new' where id=9;  
UPDATE 1  
digoal=# select * from digoal.trace_username ;  
 id |  info  | username   
----+--------+----------  
  1 | digoal | digoal  
  2 | digoal | digoal  
  3 | digoal | digoal  
  4 | digoal | digoal  
  5 | digoal | digoal  
  6 | digoal | digoal  
  7 | digoal | digoal  
  8 | digoal | digoal  
 10 | digoal | digoal  
 11 | digoal | pg92  
  9 | new    | pg92  
(11 rows)  

其他

实际上这个需求, 自己编辑触发器也可以实现, 不需要使用这个插件, 例如使用who字段存储用户名.

postgres=# create table test (id int, info text, who name);  
CREATE TABLE  
postgres=# create or replace function tg1() returns trigger as $$  
declare  
begin  
  NEW.who := current_user;  
  return NEW;    
end;  
$$ language plpgsql;  
CREATE FUNCTION  
  
postgres=# create trigger tg1 before insert or update on test for each row execute procedure tg1()  
postgres-# ;  
CREATE TRIGGER  
  
postgres=# insert into test values (1,'test','');  
INSERT 0 1  
  
postgres=# select * from test;  
 id | info |   who      
----+------+----------  
  1 | test | postgres  
(1 row)  
  
postgres=# \du  
                             List of roles  
 Role name |                   Attributes                   | Member of   
-----------+------------------------------------------------+-----------  
 postgres  | Superuser, Create role, Create DB, Replication | {}  
 test      |                                                | {}  
  
postgres=# grant all on table test to test;  
GRANT  
  
postgres=# \c postgres test  
You are now connected to database "postgres" as user "test".  
postgres=> update test set info='new' where id=1;  
UPDATE 1  
  
postgres=> select * from test;  
 id | info | who    
----+------+------  
  1 | new  | test  
(1 row)  

参考

http://www.postgresql.org/docs/9.1/static/contrib-spi.html

digoal’s 大量PostgreSQL文章入口