Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

PostgreSQL 跟踪谁动了你的记录 - Use timetravel function trace tuple’s DML

4 minute read

背景

很久以前, PostgreSQL曾有内建函数来跟踪每条tuple的insert, delete时间。

现在使用timetravel也可以来模拟这个功能.

首先需要创建这个extension :

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

创建测试表 :

digoal=> create table timetravel_test (id int primary key,info text,insert_user text,update_user text, delete_user text,start_time abstime,stop_time abstime);  
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "timetravel_test_pkey" for table "timetravel_test"  
CREATE TABLE

创建触发器 :

digoal=> create trigger timetravel  
        before insert or delete or update on timetravel_test  
        for each row  
        execute procedure  
        timetravel (start_time, stop_time, insert_user, update_user, delete_user);  
CREATE TRIGGER

注意创建触发器的时候, insert_user, update_user, delete_user 这几个列都是可选的. 不选择的话则不跟踪是哪个用户做的insert, update, delete操作.

插入测试数据 :

digoal=> insert into timetravel_test (id,info) select generate_series(1,10),'digoal';  
INSERT 0 10  
digoal=> select * from timetravel_test ;  
 id |  info  | insert_user | update_user | delete_user |       start_time       | stop_time   
----+--------+-------------+-------------+-------------+------------------------+-----------  
  1 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  2 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  3 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  4 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  5 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  6 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  7 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  8 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  9 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
 10 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity

从测试数据来看， timetravel函数帮我们记录了这条tuple是哪个用户插入的, 并且记录了tuple的插入时间, infinity在这里表示这条tuple是有效的。

下面来更新一条记录看看 :

digoal=> update timetravel_test set info='new' where id=1;  
ERROR:  duplicate key value violates unique constraint "timetravel_test_pkey"  
DETAIL:  Key (id)=(1) already exists.  
CONTEXT:  SQL statement "INSERT INTO timetravel_test VALUES ( $1,$2,$3,$4,$5,$6,$7)"

由于timetravel触发器的原因，更新操作实际上是把被更新的记录的stop_time修改为当前时间，再新增一条start_time为当前时间并且stop_time为infinity的记录. 所以就导致违反了ID的PK约束。

下面我们把约束去掉重新测试 :

digoal=> alter table timetravel_test drop CONSTRAINT timetravel_test_pkey;  
ALTER TABLE  
digoal=> update timetravel_test set info='new' where id=1;  
UPDATE 1  
digoal=> select * from timetravel_test ;  
 id |  info  | insert_user | update_user | delete_user |       start_time       |       stop_time          
----+--------+-------------+-------------+-------------+------------------------+------------------------  
  2 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  3 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  4 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  5 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  6 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  7 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  8 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  9 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
 10 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  1 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | 2012-01-13 15:16:42+08  
  1 | new    |             | digoal      |             | 2012-01-13 15:16:42+08 | infinity

没错，更新操作实际上是把被更新的记录的stop_time修改为当前时间，再新增一条start_time为当前时间并且stop_time为infinity的记录.

再更新一次 :

digoal=> update timetravel_test set info='new2' where id=1;  
UPDATE 1  
digoal=> select * from timetravel_test ;  
 id |  info  | insert_user | update_user | delete_user |       start_time       |       stop_time          
----+--------+-------------+-------------+-------------+------------------------+------------------------  
  2 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  3 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  4 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  5 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  6 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  7 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  8 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  9 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
 10 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  1 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | 2012-01-13 15:16:42+08  
  1 | new    |             | digoal      |             | 2012-01-13 15:16:42+08 | 2012-01-13 15:17:22+08  
  1 | new2   |             | digoal      |             | 2012-01-13 15:17:22+08 | infinity

接下来测试一下删除操作 :

digoal=> delete from timetravel_test where id=2;  
DELETE 1  
digoal=> select * from timetravel_test ;  
 id |  info  | insert_user | update_user | delete_user |       start_time       |       stop_time          
----+--------+-------------+-------------+-------------+------------------------+------------------------  
  3 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  4 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  5 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  6 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  7 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  8 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  9 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
 10 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | infinity  
  1 | digoal | digoal      |             |             | 2012-01-13 15:08:52+08 | 2012-01-13 15:16:42+08  
  1 | new    |             | digoal      |             | 2012-01-13 15:16:42+08 | 2012-01-13 15:17:22+08  
  1 | new2   |             | digoal      |             | 2012-01-13 15:17:22+08 | infinity  
  2 | digoal | digoal      |             | digoal      | 2012-01-13 15:08:52+08 | 2012-01-13 15:18:03+08

说明delete操作是把stop_time修改为当前时间了.

因此创建了timetravel触发器后的表，只有stop_time为infinity 的记录对我们来说是有效记录。

接下来看看能不能修改start_time和stop_time :

digoal=> update timetravel_test set stop_time=now() where id=1;  
ERROR:  timetravel (timetravel_test): you cannot change start_time and/or stop_time columns (use set_timetravel)  
digoal=> update timetravel_test set start_time=now() where id=1;  
ERROR:  timetravel (timetravel_test): you cannot change start_time and/or stop_time columns (use set_timetravel)

没错, timetravel 对这两个字段进行了保护，不允许用户对这两个字段进行修改。

另外2个函数 :

关闭timetravel 对当前session的作用. 其他SESSION不影响. 参数0表示关闭, 1 表示打开.

digoal=> select set_timetravel('timetravel_test',0);  
 set_timetravel   
----------------  
              1  
(1 row)

查看当前SESSION的timetravel 状态, 是否启用. 结果0表示关闭, 1 表示打开.

digoal=> select get_timetravel('timetravel_test');  
 get_timetravel   
----------------  
              0  
(1 row)

此时在这个SESSION里面就可以修改start_time和stop_time了.

digoal=> update timetravel_test set start_time=now() where id=1;  
UPDATE 4

进入另一个SESSION, 是不可以修改的.

digoal=> update timetravel_test set start_time=now() where id=1;  
ERROR:  timetravel (timetravel_test): you cannot change start_time and/or stop_time columns (use set_timetravel)

小结

1. timetravel可以对平时变更非常少，但是每次变更都需要有变更轨迹记录的情况。当然这部分功能很容易由应用程序来完成。使用timetravel并不是一个很好的方法，但是timetravel的想法是值得借鉴的。

参考

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

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...