Re: Proposal: Store "timestamptz" of database creation on "pg_database"

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> To mention just one problem, are we going to add nonstandard,
> non-backwards-compatible syntax to every single kind of CREATE to allow
> pg_dump to preserve the creation dates?

Perhaps 'ALTER' would be a better place to put it, but concerns around
how to make pg_dump work with it hardly strikes me as a serious argument
against this. I agree that we may be overloading ourselves with syntax
but that's a compromise we made long ago in order to have pg_dump be
able to act like a regular 'user'.

> Another interesting question is
> whether we should likewise track the last ALTER time, or perhaps whether
> a sufficiently major ALTER redefinition should update the creation time.

Yes, tracking the last 'ALTER' time would be useful as well, as it's own
field. 'ALTER' wouldn't change the 'CREATE' time, except perhaps if it
has an explicit 'make the CREATE time X' option.

> I'm inclined to think that anyone who really needs this should be
> pointed at event triggers. That feature (if it gets in) will allow
> people to track creation/DDL-change times with exactly the behavior
> they want.

I considered that and rejected it. Event triggers will be great to
allow people to customize and/or specialize exactly what is tracked and
how, but I dislike that they would be the only way to get this
information. I'm on the fence about if, assuming event triggers go in,
we provide this kind of information through a 'default' set of event
triggers. I wouldn't want users to be able to modify those event
triggers and I'd expect the results to go into a system table that we
wouldn't want users messing with either.

This information could be extremely useful for forensics, debugging, ETL
processes (many of which create tables as part of their processes), etc.

Thanks,

Stephen