cancel
Showing results for 
Search instead for 
Did you mean: 

Getting Object Creation Time

PostgreSQL Core Team - EDB

 

The Postgres community is often asked to provide automatic tracking of the creation and last modification times for objects, e.g. tables, functions. File systems track file last modification times, and some record creation time, so the logic is why can't Postgres do this? (Unix's inode modification time does not represent the creation time.)

 

For object creation-time tracking, we have to consider when creation time should be set. It is obvious that the creation time should be set when the object is first created, but what about:

For object modification, things become more complicated. There are several layers of modification:

Tracking all of these times is reasonable. However, there is significant code and performance overhead in doing it, and a given user is probably only interested in a few of these. A detailed email by Tom Lane also explains that even if we tracked all of this, it only records the change time, not what was changed.

A more holistic solution is auditing, which would contain change information as well as tracking of multiple change times. Effort is probably better spent adding auditing to Postgres than adding fine-grained tracking of creation and modification times. Users who want to track such times are best served writing custom event triggers.

 

(Article originally published on Bruce's blog - Tuesday, November 21, 2017 @ https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017 )