Re: Get the date of creation of objects in the database

Stephen Frost <sfrost(at)snowman(dot)net> writes:
> At least on a first blush look through the threads linked from such a
> search, I'm unimpressed by the arguments against and note that there are
> quite a few arguments for.

I think you missed the worries around what dump/reload semantics would be.

> We could provide a function for 'last data modification time' which
> simply uses the filesystem modification time.

As far as tables go, the filesystem mod time would not be anything of
great use to users. Consider that (1) there might be committed but
unwritten data sitting in shared buffers, so the filesystem mod time could
be too old by as much as the max checkpoint interval; while (2) writes for
hint bit setting or xid freezing could happen long after the last data
write, so the filesystem mod time could be almost arbitrarily later than
what the user thinks is the last mod time. Not to mention whether
physical rewrites such as CLUSTER ought to count as data mods.

But I thought this request was about DDL timestamps, not data timestamps.
The filesystem will help you not at all there, because at best it would
know about the last mod time on the relevant system catalog, not any
individual object.

Anyway, my main objection to this idea is that it would be a sinkhole for
arguments over what the detailed semantics would be. Should dump/reload
result in a new DDL timestamp? (If not, the only way to prevent it would
be to invent a new "ALTER object SET TIMESTAMP" family of DDL, which would
not merely be a lot of work but would mean that the timestamps would have
exactly 0 value for any sort of forensic purposes.) Should, eg, COMMENT
ON cause a DDL timestamp update on the referenced object? How about
REINDEX or VACUUM or ANALYZE? How about something like creating a foreign
key reference to a table? I think that you could make credible arguments
either way on each of these issues, depending on what you assume the true
use-case is for having the timestamps; which means that trying to support
them is a mug's game. We won't satisfy anybody, least of all the users
who don't care and don't need the additional overhead.

Lastly, even if we had a DDL timestamp, it wouldn't tell you anything
about what that last change was. So I think logging/auditing DDL
operations is a far better path to pursue.

regards, tom lane