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

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.

Categories

System function, Transactions

See also

track_commit_timestamp, pg_last_committed_xact()