Re: Why is there no object create date is the catalogs? - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | Re: Why is there no object create date is the catalogs? |
| Date | |
| Msg-id | CAKFQuwY6r7MG1OtAukXdQn12yfZtwpkLvtp8++eCu_AC9-LtoA@mail.gmail.com Whole thread Raw |
| In response to | Re: Why is there no object create date is the catalogs? (Melvin Davidson <melvin6925@gmail.com>) |
| List | pgsql-general |
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.
pgsql-general by date: