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
- PostgreSQL 9.5
- added (commit 73c986ad)
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) 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+01 (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
References
- PostgreSQL documentation: track_commit_timestamp