Digoal.zhou

Digoal.zhou

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

PostgreSQL 触发器应用 - (触发器WHEN)前置条件过滤跟踪目标记录

5 minute read

背景

回答一位朋友问的一个问题 :

写一个触发器，将当前的表中某一行的一个字段被修改了，然后将该行记录插入到另一张表里面？如何写呢.

要对触发器用法有详细的了解, 请参考 :

1. http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

2. http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

3. http://blog.163.com/digoal@126/blog/static/1638770402014728105442434/

本文首先看看触发器的语法 :

CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }  
    ON table_name  
    [ FROM referenced_table_name ]  
    [ NOT DEFERRABLE | [ DEFERRABLE ] { INITIALLY IMMEDIATE | INITIALLY DEFERRED } ]  
    [ FOR [ EACH ] { ROW | STATEMENT } ]  
    [ WHEN ( condition ) ]  
    EXECUTE PROCEDURE function_name ( arguments )  
  
where event can be one of:  
  
    INSERT  
    UPDATE [ OF column_name [, ... ] ]  
    DELETE  
    TRUNCATE

为了实现这位朋友的需求, 需要用到行触发器, 以及WHEN ( condition )

这里实际用了前段时间写的关于模拟flashback query的一个触发器.

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

例子 :

postgres=# create table tgtest(id int, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# insert into tgtest values (1,'abc',now());  
INSERT 0 1  
postgres=# insert into tgtest values (2,'abcdef',now());  
INSERT 0 1

创建一个表, 用来记录跟踪tgtest表的某条记录的某个字段被更新时的记录.

CREATE TABLE public.undo_tgtest (  
  id serial8 primary key,  
  xid int8,  
  relid oid,  
  table_schema text,  
  table_name text,  
  when_tg text,  
  level text,  
  op text,  
  encoding name,  
  old_rec public.tgtest,  
  new_rec public.tgtest,  
  crt_time timestamp without time zone DEFAULT now(),  
  username text,  
  client_addr inet,  
  client_port int  
);

创建一个触发器函数, 记录当tgtest表的某条记录的某个字段被更新时, 插入另一个表.

CREATE OR REPLACE FUNCTION public.undo_tgtest_trace()  
RETURNS trigger  
LANGUAGE plpgsql  
AS $BODY$  
DECLARE  
  v_username text := session_user;  
  v_client_addr inet := inet_client_addr();  
  v_client_port int := inet_client_port();  
  v_xid bigint := txid_current();  -- 记录这条记录被更新的事务号.  
  v_encoding name := pg_client_encoding();  
BEGIN  
  case TG_OP  
  when 'UPDATE' then   
    insert into public.undo_tgtest (xid, relid, table_schema, table_name, when_tg, level, op, encoding, old_rec, new_rec, username, client_addr, client_port)  
      values (v_xid, tg_relid, tg_table_schema, tg_table_name, tg_when, tg_level, tg_op, v_encoding, OLD, NEW, v_username, v_client_addr, v_client_port);  
  else  
    return null;  
  end case;  
  RETURN null;  
END;  
$BODY$ strict volatile;

创建触发器, 当tgtest表的某条记录(info=’abc’)的某个字段(info)被更新时, 调用触发器函数undo_tgtest_trace() :

postgres=# create trigger tg1 after update on tgtest for each row when (old.info<>new.info and old.info='abc') execute procedure undo_tgtest_trace();  
CREATE TRIGGER

测试

跟踪

postgres=# update tgtest set info='abcd' where id=1;  
UPDATE 1  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
(1 row)  
  
postgres=# select * from tgtest ;  
 id |  info  |          crt_time            
----+--------+----------------------------  
  2 | abcdef | 2014-09-17 08:38:59.28963  
  1 | abcd   | 2014-09-17 08:38:54.622664  
(2 rows)

不跟踪

postgres=# update tgtest set info='abcdefgi' where id=2;  
UPDATE 1  
postgres=# select * from tgtest ;  
 id |   info   |          crt_time            
----+----------+----------------------------  
  1 | abcd     | 2014-09-17 08:38:54.622664  
  2 | abcdefgi | 2014-09-17 08:38:59.28963  
(2 rows)  
  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
(1 row)

注意事项 :

空值的判断, 因为空和任何值的比较都返回空, 所以条件不成立. 因此在写when时, 看你有没有这方面的需求, 如果有的话, 需要多写几个条件.

postgres=# update tgtest set info='abc' where id=1;  
UPDATE 1  
postgres=# update tgtest set info=null where id=1;  
UPDATE 1  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
(1 row)

要解决这个问题, 需要重建触发器 :

例如 :

((old.info<>new.info or (old.info is null and new.info is not null) or (old.info is not null and new.info is null)) and old.id=1)

这里使用id=1来定位一条记录, 并且判断info字段是否被变更.

postgres=# drop trigger tg1 on tgtest;  
DROP TRIGGER  
postgres=# create trigger tg1 after update on tgtest for each row when ((old.info<>new.info or (old.info is null and new.info is not null) or (old.info is not null and new.info is null)) and old.id=1) execute procedure undo_tgtest_trace();  
CREATE TRIGGER

从null更新到not null, 被记录 :

postgres=# update tgtest set info='ab' where id=1;  
UPDATE 1  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
  2 | 17573021 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,,"2014-09-17 08:38:54.622664")    | (1  
,ab,"2014-09-17 08:38:54.622664")   | 2014-09-17 08:46:10.78577  | postgres |             |              
(2 rows)

从not null更新到null, 被记录 :

postgres=# update tgtest set info=null where id=1;  
UPDATE 1  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
  2 | 17573021 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,,"2014-09-17 08:38:54.622664")    | (1  
,ab,"2014-09-17 08:38:54.622664")   | 2014-09-17 08:46:10.78577  | postgres |             |              
  3 | 17573033 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,ab,"2014-09-17 08:38:54.622664")  | (1  
,,"2014-09-17 08:38:54.622664")     | 2014-09-17 08:46:22.820805 | postgres |             |              
(3 rows)

从null更新到null, 不被记录 :

postgres=# update tgtest set info=null where id=1;  
UPDATE 1  
postgres=# select * from undo_tgtest;  
 id |   xid    | relid | table_schema | table_name | when_tg | level |   op   | encoding |               old_rec                |     
             new_rec                |          crt_time          | username | client_addr | client_port   
----+----------+-------+--------------+------------+---------+-------+--------+----------+--------------------------------------+---  
------------------------------------+----------------------------+----------+-------------+-------------  
  1 | 17572725 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,abc,"2014-09-17 08:38:54.622664") | (1  
,abcd,"2014-09-17 08:38:54.622664") | 2014-09-17 08:40:25.268737 | postgres |             |              
  2 | 17573021 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,,"2014-09-17 08:38:54.622664")    | (1  
,ab,"2014-09-17 08:38:54.622664")   | 2014-09-17 08:46:10.78577  | postgres |             |              
  3 | 17573033 | 31051 | public       | tgtest     | AFTER   | ROW   | UPDATE | UTF8     | (1,ab,"2014-09-17 08:38:54.622664")  | (1  
,,"2014-09-17 08:38:54.622664")     | 2014-09-17 08:46:22.820805 | postgres |             |              
(3 rows)

参考

1. http://blog.163.com/digoal@126/blog/static/1638770402013283547959/

2. http://blog.163.com/digoal@126/blog/static/1638770402013211102130526/

3. http://blog.163.com/digoal@126/blog/static/1638770402014728105442434/

4. http://www.postgresql.org/docs/9.3/static/sql-createtrigger.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...