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

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.
If an object already exists, it cannot be created again. The worst case
scenario is that an object must be dropped due to some terrible corruption
or other disaster, in which case the creation date is not a major
consideration.

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

All I have heard so far is that the ONLY reason there is no object create
date in pg_class is because there is no general agreement as to what create
date means. Well I am giving it right now. When you execute the SQL
statement of the form

CREATE TABLE ...
CREATE INDEX ...
CREATE SEQUENCE ...
CREATE MATERIALIZED VIEW ...
CREATE TYPE ...
CREATE FOREIGN TABLE ...

then that is when clock_timestamp() should be recorded as relcreatedate or
relcreatetime.
Providing, of course, that the column is added to pg_class. :)

Is there some other overwhelming _technical_ reason that I am overlooking
that prevents this from being done?

On Tue, May 12, 2015 at 8:08 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 05/12/2015 03:44 PM, Melvin Davidson wrote:
>
>> Adrian,
>>
>> You are over thinking this. An object is only "created" once! That is
>> what I meant by relcreatedate. If it is dropped, then it is deleted from
>> the catalogs. If it is modified, then it does NOT affect the creation
>> date. Everything else is superfluous.
>>
>
> See my original post and Tom Lanes response.
>
>
>> It is also not unusual for tables to have an end of cycle in certain
>> application, hence the need to be dropped after a certain time. EG.
>> Tables that track data only for a specific year.
>>
>
> Hence my link to the partitioning part of the manual.
>
>
>> Since PostgreSQL already tracks when tables are vacuum, auto vacuumed,
>> analyzed and auto analyzed ( pg_stat_all_tables ), I don't see why it is
>> such a big deal ( or so hard ) to track when an object is created. It
>> should be a very simple patch to the catalogs.
>>
>
> It is probably not a big deal to create a timestamp field and populate it.
> The issues arise when you start asking what it really means. The Postgres
> catalogs are not part of dump file, so the data in them will not transfer
> when you restore to another database. So on restore the create date will be
> the date the table is restored, not the date the table was originally
> created. For some people that is okay, for others not okay.
>
>
>> On Tue, May 12, 2015 at 6:00 PM, Adrian Klaver
>> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>>
>> On 05/12/2015 12:51 PM, Melvin Davidson wrote:
>>
>>
>> Can anyone tell me why there is no "relcreated" column in
>> pg_class to
>> track the creation date of an object?
>>
>>
>> Meant to add to my previous post, back before I 'discovered' version
>> control I use to put the creation date in the table COMMENT:
>>
>> http://www.postgresql.org/docs/9.4/interactive/sql-comment.html
>>
>>
>> It seems to me it would make sense to have one as it would
>> facilitate
>> auditing of when objects are created. In addition, it would also
>> facilitate the dropping of objects that have exceeded a certain
>> age.
>>
>> EG: SELECT 'DELETE TABLE ' || relname || ';'
>> FROM pg_class
>> WHERE relkind = 'r'
>> AND relcreated > current_timestamp - INTERVAL ' 1 year';
>>
>> Adding that column should be relatively easy and would not break
>> backwards compatiblity with previous versions.
>> --
>> *Melvin Davidson*
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize. Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

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