os: centos 7.8 db: postgresql 13

track_commit_timestamp (boolean) 记录事务的提交时间。这个参数只能在postgresql.conf 文件中或在服务器命令行上设置。 默认值是off。

track_commit_timestamp = off

pgbenchdb=# \x
Expanded display is on.
pgbenchdb=# select * from pg_settings where name ='track_commit_timestamp' order by name;
-[ RECORD 1 ]---+----------------------------------
name            | track_commit_timestamp
setting         | off
unit            | 
category        | Replication
short_desc      | Collects transaction commit time.
extra_desc      | 
context         | postmaster
vartype         | bool
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | off
reset_val       | off
sourcefile      | 
sourceline      | 
pending_restart | f

看下效果

pgbenchdb=# \x
pgbenchdb=# create table tmp_t0 (
a int4
)
;

pgbenchdb=# insert into tmp_t0(a) values(1);

pgbenchdb=# select pg_xact_commit_timestamp(xmin),tableoid,cmax,xmax,cmin,xmin,ctid,a from tmp_t0;

ERROR:  could not get commit timestamp data
HINT:  Make sure the configuration parameter "track_commit_timestamp" is set.

使用 pg_xact_commit_timestamp 函数是，提示很明显。

track_commit_timestamp = on

需要重启 server

pgbenchdb=# insert into tmp_t0(a) values(2);

pgbenchdb=# select pg_xact_commit_timestamp(xmin),tableoid,cmax,xmax,cmin,xmin,ctid,a from tmp_t0;
   pg_xact_commit_timestamp    | tableoid | cmax | xmax | cmin |  xmin   | ctid  | a 
-------------------------------+----------+------+------+------+---------+-------+---
                               |    16433 |    0 |    0 |    0 | 1604950 | (0,1) | 1
 2021-07-02 11:25:54.035998+08 |    16433 |    0 |    0 |    0 | 1604951 | (0,2) | 2
(2 rows)

nice

参考: postgres.cn/docs/13/run… postgres.cn/docs/13/fun…