Contents

track_commit_timestamp

A configuration parameter determining whether the commit time of transactions should be tracked

track_commit_timestamp is a configuration parameter determining whether the commit time of transactions should be tracked.

track_commit_timestamp was added in PostgreSQL 9.5.

Default value

The default value for track_commit_timestamp is: off.

Usage

track_commit_timestamp is primarily intended for use in logical replication solutions such as pglogical, where commit timestamps are used as part of conflict resolution.

The current value of track_commit_timestamp is one of the configuration parameters recorded in pg_control (PostgreSQL 9.5 and later).

Change history

Examples

postgres=# SHOW track_commit_timestamp ;
 track_commit_timestamp 
------------------------
 on
(1 row)

postgres=# CREATE TABLE xact_test (id INT, val TEXT);
CREATE TABLE

postgres=# BEGIN ;
BEGIN

postgres=*# SELECT txid_current();
 txid_current 
--------------
          508
(1 row)

Time: 0.374 ms
postgres=*# INSERT INTO xact_test VALUES (1, 'foo'),
                             (2, 'bar'),
                             (3, 'baz');
INSERT 0 3

postgres=*# SELECT pg_xact_commit_timestamp(xmin), xmin, * FROM xact_test ;
 pg_xact_commit_timestamp | xmin | id | val 
--------------------------+------+----+-----
                          |  508 |  1 | foo
                          |  508 |  2 | bar
                          |  508 |  3 | baz
(3 rows)

postgres=*# commit;

postgres=# SELECT pg_xact_commit_timestamp(xmin), xmin, * FROM xact_test ;
   pg_xact_commit_timestamp    | xmin | id | val 
-------------------------------+------+----+-----
 2020-10-25 12:36:47.549053+01 |  508 |  1 | foo
 2020-10-25 12:36:47.549053+01 |  508 |  2 | bar
 2020-10-25 12:36:47.549053+01 |  508 |  3 | baz
(3 rows)

postgres=# SELECT * FROM pg_last_committed_xact();
 xid |           timestamp           
-----+-------------------------------
 508 | 2020-10-25 12:36:47.549053+02
(1 row)

Corresponding WAL record (as output by pg_waldump:

rmgr: Heap        len (rec/tot):     63/    63, tx:        508, lsn: 0/03031490, prev 0/03031458, desc: INSERT off 10 flags 0x00, blkref #0: rel 1663/13580/16456 blk 0
rmgr: Heap        len (rec/tot):     63/    63, tx:        508, lsn: 0/030314D0, prev 0/03031490, desc: INSERT off 11 flags 0x00, blkref #0: rel 1663/13580/16456 blk 0
rmgr: Heap        len (rec/tot):     63/    63, tx:        508, lsn: 0/03031510, prev 0/030314D0, desc: INSERT off 12 flags 0x00, blkref #0: rel 1663/13580/16456 blk 0
rmgr: Transaction len (rec/tot):     34/    34, tx:        508, lsn: 0/03031588, prev 0/03031550, desc: COMMIT 2020-10-25 12:36:47.549053 MET

Categories

GUC configuration item, Transactions

See also

pg_last_committed_xact(), pg_xact_commit_timestamp()