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)
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+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