Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Commit timestamp tracking

A boolean configuration parameter named track_commit_timestamp is now available. "on" enables commit timestamp tracking. This makes it possible to see when a transaction was committed. (timestamp with timezone of the servertime)
To query that information, use the function pg_xact_commit_timestamp(transaction_id) to find out when rows were INSERTed/UPDATEed, call the pg_xact_commit_timestamp function passing in the xmin system column. Note that you have to set the parameter and restart before it can start tracking timestamps, so it won't show up until you do so:
 # SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
  pg_xact_commit_timestamp | id | name  
 --------------------------+----+-------
                           |  1 | red
                           |  2 | green
                           |  3 | blue
 (3 rows)
Then we edit postgresql.conf so that track_commit_timestamp = on, then restart PostgreSQL:
 # INSERT INTO colours VALUES ('mauve'),('cyan'),('indigo');
 INSERT
 
 # SELECT pg_xact_commit_timestamp(xmin), * FROM colours;
    pg_xact_commit_timestamp    | id |  name  
 -------------------------------+----+--------
                                |  1 | red
                                |  2 | green
                                |  3 | blue
  2015-10-02 11:16:34.678267+01 |  4 | mauve
  2015-10-02 11:16:34.678267+01 |  5 | cyan
  2015-10-02 11:16:34.678267+01 |  6 | indigo
 (6 rows)
You can see that the first 3 rows that were inserted before the parameter was enabled don't return the timestamp, so it only starts to keep track of timestamps for INSERTs/UPDATEs once the parameter has been enabled and the database cluster has been restarted.
There is also the set-returning function pg_last_committed_xact which tells you the timestamp of the last committed transaction:
 # SELECT * FROM pg_last_committed_xact();
  xid  |           timestamp           
 ------+-------------------------------
  2039 | 2015-10-02 11:16:34.678267+01
 (1 row)

Comments

Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

spfile and pfile errors

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

2017 Shortern URL - Other Website Like ADFOC.US

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory