PostgreSQL 事务,会话 GUC 变量 妙用 - 获取并跟踪事务结束时间(不是来自XLOG commit record哦)

less than 1 minute read

背景

PostgreSQL有一种触发器叫行级约束触发器,这种触发器是在事务结束时被触发的。

用法可见

https://github.com/digoal/pgsql_admin_script/blob/master/pgq.md

如果我要获取事务结束的时间戳(大概的),并且如果多行触发了触发器函数时,要得到一个同样的时间戳,怎么搞呢?

用now()得到的是事务开始的时间。

用clock_timestamp(), statement_timestamp() 得到的是语句级的时间。

PG现成的函数都无法满足需求。

那么怎么来实现呢?

我们可以使用GUC变量来实现,自定义一个函数即可。

create or replace function get_commit_time() returns timestamp without time zone as $$  
declare  
  res timestamp without time zone;  
begin  
  show commit_time.realval into res;  
  return res;  
exception when others then  -- 如果未设置, 则使用以下SQL设置.  
  res := clock_timestamp();  
  execute 'set local commit_time.realval = '''||res||'''';  -- 设置事务级变量  
  return res;  
end;  
$$ language plpgsql;  

测试:

src=# begin;  
BEGIN  
src=# select now();  
              now                
-------------------------------  
 2016-01-06 18:03:40.101664+08  
(1 row)  
  
src=# select now();  
              now                
-------------------------------  
 2016-01-06 18:03:40.101664+08  
(1 row)  
  
src=# select clock_timestamp();  
        clock_timestamp          
-------------------------------  
 2016-01-06 18:03:49.115772+08  
(1 row)  

第一次调用当时的clock_timestamp取得的时间,事务中一直延续使用。

src=# select mq.get_commit_time();  
      get_commit_time         
----------------------------  
 2016-01-06 18:04:02.244642  
(1 row)  
  
src=# select mq.get_commit_time();  
      get_commit_time         
----------------------------  
 2016-01-06 18:04:02.244642  
(1 row)  
  
src=# select mq.get_commit_time();  
      get_commit_time         
----------------------------  
 2016-01-06 18:04:02.244642  
(1 row)  
  
src=# show commit_time.realval;  
    commit_time.realval       
----------------------------  
 2016-01-06 18:04:02.244642  
(1 row)  
  
src=# end;  
COMMIT  

事务结束后,释放。

src=# show commit_time.realval;  
 commit_time.realval   
---------------------  
   
(1 row)  

Flag Counter

digoal’s 大量PostgreSQL文章入口