Re: Why is there no object create date is the catalogs?

On Tue, May 12, 2015 at 6:33 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> I thank everyone for their feedback regarding the omission of object
> creation date from the catalog.
>
> I do respect the various reasons for not including it, but I feel it is my
> duty to draw out this issue a bit longer.
>

> I would like to counter the argument that a restore from a dump will
> override the create date.
> That cannot happen.
>

​You can restore a database into an empty (and newer) cluster. This is
possible because instead of archiving and restoring the catalog the
restoration script recreates everything by issuing CREATE and INSERT/COPY
statements. Perform an SQL dump and look at it if you need affirmation.

Now, saying that this field is marginalized for people who use
pg_dump/pg_restore instead of pg_upgrade is a viable decision but the use
cases put forth so far don't scream for something like this to exist.

Specifically, if an application has a requirement for something like this
then the application should take pains to manage it. It can be placed into
a system catalog but if the system is not going to use the information then
it shouldn't be responsible for it. I guess "comments" would be an
exception to this rule - but there you are dealing with constants that are
dumped and restored.

> Further to the point, why is it that both Oracle and SQL Server _do_ have
> the object create date in the catalogs?
>
>
> http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle
>
> https://msdn.microsoft.com/en-us/library/ms190324.aspx
>

​If a good why can be provided maybe we'd be convinced to add the field...​

Pondering the above I rather think to implement this as a "comment" table
but where there are two "comment" fields - one text and one timestamptz.
Like with comments pg_dump would treat this as user data to be included in
the dump and restored as-is. During the restoration new "created" entries
would be added and so then there would be two. Though at this point you
are basically saying the project should provide official storage and set of
event triggers and for the "CREATE" events.

Going that far it must be argued why -core should be responsible for such a
feature instead of interested parties maintaining it on PGXN.

In the end there is no technical reason to exclude the field but such a
field is arguably application data and should not be present on a system
catalog table. Personally, I could see an argument for such information
being valuable during schema exploration - just like comments are.

The dump/restore problem should be solvable - just export "UPDATE pg_class
SET creationdate = '2015-05-12T00:00:00UTC'::timestamptz WHERE oid = xxx"
as part of the dump - just after the COMMENT ON statements. New objects
will be created during restoration but then the old timstamp will replace
the newly assigned one. Not that I've thought this through in great detail
- the event-based setup, with history maintained across dumps - definitely
is more appealing if quite a bit more work.

I don't see this field being an end of itself but something that would be
added if some other feature required it - thus basically making it a system
field instead of an application one...

David J.