Contents
pg_xact_commit_timestamp()
A function returning the commit timestamp of a transaction
pg_xact_commit_timestamp() is a system function returning the commit timestamp of a transaction.
pg_xact_commit_timestamp() was added in PostgreSQL 9.5.
Usage
pg_xact_commit_timestamp (xid) →timestamp with time zone
track_commit_timestamp must be enabled for pg_xact_commit_timestamp() to work.
Change history
- PostgreSQL 9.5
- added (commit 73c986ad)
Examples
View the commit timestamp for rows in a table:
postgres=# SELECT pg_xact_commit_timestamp(xmin), xmin, * FROM xact_test; pg_xact_commit_timestamp | xmin | id | val -------------------------------+------+----+----- 2021-07-07 08:52:14.189175+01 | 738 | 1 | foo 2021-07-07 08:52:22.72496+01 | 739 | 2 | bar 2021-07-07 08:52:26.893677+01 | 740 | 3 | baz (3 rows)
If track_commit_timestamp is set to off, pg_xact_commit_timestamp() will fail with an ERROR:
postgres=# SELECT pg_xact_commit_timestamp(xmin), xmin, * FROM xact_test;
ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
postgres=# SELECT pg_xact_commit_timestamp('738'::xid);
ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
References
- PostgreSQL documentation: Committed Transaction Information Functions