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

On Wed, Apr 20, 2016 at 5:30 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
> "I just cannot see that in the following:
> pg_upgrade 9.4 --> 9.5"
> The 9.5 database is the same as the 9.4 one."
>
> You are speaking of the case where relcreatedat did not/does not exist in
> the previous database?
>

​No, your are reading it too literally. If this was added to 9.7 the
correct analogy is that the upgrade from 9.7 to 9.8 causes a new database
to come into existence - and new objects to be created, which are then
populated with existing data.​

I think we need an original creation date, that can be dump/restored using
something like:

CREATE TABLE [...] WITH CREATIONDATE '2016-05-01'

And then a field for the actual time the creating CREATE TABLE ran
independent of the aforementioned CREATIONDATE.

On a serious note I have no problem with this type of implementation. This
is not being put forth as an auditing system so I don't care if malicious
or careless users can plug meaningless dates into their CREATE TABLE
statements. Let those who rely upon this data setup processes to ensure
its accuracy however they wish.

We are also need a field for "last updated" to so that people can recognize
when a objects structure has changed subsequent to its creation - via ALTER
xxx; two of them actually for the same reason as above.

We probably should start tracking which user was logged in when said object
was created and/or altered.

Pretty soon we are building a full blown auditing system one field at a
time...

Speaking blindly here but given that we now have event triggers I'm even
more inclined to simply tell people to setup user-space tables and event
triggers to do whatever they want. Is there any reason that combination
cannot solve the problems being brought up? I get the desirability of
having something in-core but this seems like a perfect problem for which
PGXN should be the solution.

David J.