PostgreSQL 9.5 new feature - record transaction commit timestamp

背景

PostgreSQL 9.5新增的一个功能, 允许用户开启过关闭记录事务提交时间.

代码见

src/backend/access/transam/commit_ts.c

 * commit_ts.c  
 *              PostgreSQL commit timestamp manager  
 *  
 * This module is a pg_clog-like system that stores the commit timestamp  
 * for each transaction.  
 *  
 * XLOG interactions: this module generates an XLOG record whenever a new  
 * CommitTs page is initialized to zeroes.  Also, one XLOG record is  
 * generated for setting of values when the caller requests it; this allows  
 * us to support values coming from places other than transaction commit.  
 * Other writes of CommitTS come from recording of transaction commit in  
 * xact.c, which generates its own XLOG records for these events and will  
 * re-perform the status update on redo; so we need make no additional XLOG  
 * entry here.  
 *  
 * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group  
 * Portions Copyright (c) 1994, Regents of the University of California  
 *  
 * src/backend/access/transam/commit_ts.c  

使用和block_size同样大小的数据块.

/*  
 * Defines for CommitTs page sizes.  A page is the same BLCKSZ as is used  
 * everywhere else in Postgres.  
 *  
 * Note: because TransactionIds are 32 bits and wrap around at 0xFFFFFFFF,  
 * CommitTs page numbering also wraps around at  
 * 0xFFFFFFFF/COMMIT_TS_XACTS_PER_PAGE, and CommitTs segment numbering at  
 * 0xFFFFFFFF/COMMIT_TS_XACTS_PER_PAGE/SLRU_PAGES_PER_SEGMENT.  We need take no  
 * explicit notice of that fact in this module, except when comparing segment  
 * and page numbers in TruncateCommitTs (see CommitTsPagePrecedes).  
 */  

使用12个字节记录一个事务和对应的时间.

/*  
 * We need 8+4 bytes per xact.  Note that enlarging this struct might mean  
 * the largest possible file name is more than 5 chars long; see  
 * SlruScanDirectory.  
 */  
typedef struct CommitTimestampEntry  
{  
        TimestampTz             time;  
        CommitTsNodeId  nodeid;  
} CommitTimestampEntry;  
  
#define SizeOfCommitTimestampEntry (offsetof(CommitTimestampEntry, nodeid) + \  
                                                                        sizeof(CommitTsNodeId))  
  
#define COMMIT_TS_XACTS_PER_PAGE \  
        (BLCKSZ / SizeOfCommitTimestampEntry)  

这个模块和pg_clog类似, 也是记录在XLOG以外的位置, 如下, 我们可看到新增的目录pg_commit_ts :

pg95@db-172-16-3-150-> cd $PGDATA  
pg95@db-172-16-3-150-> ll  
...  
drwx------ 2 pg95 pg95 4.0K Apr  9 09:04 pg_commit_ts  

查看控制文件可以得知当前是否开启了跟踪事务提交时间.

pg95@db-172-16-3-150-> pg_controldata   
Current track_commit_timestamp setting: off  

如果要开启, 显然需要重启数据库.

vi $PGDATA/postgresql.conf  
track_commit_timestamp = on   
  
pg_ctl restart -m fast  
pg95@db-172-16-3-150-> pg_controldata |grep track  
Current track_commit_timestamp setting: on  

如果你开启了跟踪事务时间, 那么在需要重置xlog时, 也多了需要指定的值.

[-c xid,xid]  
       ·   A safe value for the oldest transaction ID for which the commit time can be retrieved (first part of -c)  
           can be determined by looking for the numerically smallest file name in the directory pg_committs under the  
           data directory. Conversely, a safe value for the newest transaction ID for which the commit time can be  
           retrieved (second part of -c) can be determined by looking for the numerically greatest file name in the  
           same directory. As above, the file names are in hexadecimal.  

记录事务提交时的时间底有啥用呢?

目前暂时未看到有使用这块的,

不过可以想象一下 垃圾回收(例如snapshot too old), 逻辑复制有一定关系.

未来也许会针对这个开发一系列附加功能.

参考

1. src/backend/access/transam/commit_ts.c

2. src/backend/access/rmgrdesc/committsdesc.c

3. man pg_resetxlog

digoal’s 大量PostgreSQL文章入口