Re: Add relcreated (timestamp) column to pg_class catalog to record the time an object was created

On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner <kgrittn(at)gmail(dot)com> wrote:

> On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
> > As for what I want from the community, I would like other users
> > and dba's to weigh in on this request and it's usefulness.
>
> When I was a DBA on a team responsible for hundreds of
> geographically distributed databases, initially using products with
> this feature and then moving to PostgreSQL, I occasionally found
> this feature to be a minor convenience when it was present. We
> kept the DDL for recreating everything under source control, and
> each new release contained the DDL to move from one state to the
> next, so such a column didn't give us anything we couldn't get by
> consulting the "official" DDL. But, as an example of where it
> could save a few minutes, if someone had been allowed to run ad hoc
> reports or data cleanup on a database it was a quick way to look
> for stray tables they may have generated to keep intermediate
> results or exceptions, so we could follow up on disposition of
> those tables.
>
> It would take a lot of such incidents to add up to enough time to
> add this as a proper feature, which is probably why nobody with
> resources to devote to adding features has prioritized it to the
> point of developing a proposed patch. That and the fact that there
> is no guarantee that the community as a whole would feel that the
> feature "carried its own weight" in terms of benefit / maintenance
> cost, so it might not make it in anyway.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
Adrian,
To answer your questions:
"The first time an object was ever created?"
Yes, AFAIK, objects in pg_class can only be "created" once, so relcreatedat
would be the timestamp it
is initially created. Otherwise, if an object is dropped and subsequently
re-created, then by definition the relcreatedat must again populated.

"The time it was created in a new database during a
dump-restore/pg_upgrade/replication?"
Yes for new database, but no for upgrade/replication as by definition, the
objects would already exist..

Second thing:

"pg_class does not track all the objects in a database, so what other
system catalogs should be included. With the same questions as above."
I am only concerned with the objects in pg_class as id'd by relkind. IE:
tables, indexes, sequences, etc.

Kevin,
Thank you for your additional feedback. adhoc user temp tables is just one
case.
There is also the situation of tables with limitited use. EG:
history_yyyymm, in which case it would facilitate dropping of tables that
are no longer needed after x amount of time.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.