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

[Shulgin, Oleksandr]

On Sat, Apr 23, 2016 at 3:17 PM, Melvin Davidson 
wrote:

>
> On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <
> oleksandr.shul...@zalando.de> wrote:
> >I find yor lack of proper email quoting skills disturbing..
>
> I am sorry you are disturbed, but thank you for pointing that out. I have
> revised my style to make you feel more comfortable.
>

Nothing to be sorry about, but I still don't see any improvement on your
part :-/

>Yes, but that means that the timestamps must be part of the dump file,
> which means in turn they can be altered before the restore or--if
> implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
> point in time, so there is little to zero value in having the timestamps in
> the first place. This was already discussed in earlier threads.
>
> No, when restoring to a new database, then by definition, object creation
> time is when they are loaded into the new database, which is essentially
> after the new database is created.
>

Well, that is the problem.  By *your* definition that might be true, but
not necessarily by anyone's else.  Consider a DB restored from backup on a
Saturday night due to server failure.  All objects now have "outside of
working hours" creation timestamps.  Does that create more problems in your
environment?

>> I also mentioned that this is already in the catalogs of Oracle and SQL
> Server.
> >This sort of argument doesn't help the discussion: there should be a good
> reason to add the feature and merely pointing out that others already doing
> that is not a good reason, IMO.
>
> Actually, it does. One of the reasons PostgreSQL is growing in popularity
> is that many companies are looking to switch away from Oracle and SQL
> Server and switch to open source PostgreSQL to save money. In doing so,
> consideratopn of the compatibilty and features is of prime concern.
>

Well, I can see some reason in that, but what we are discussing here is not
a feature of primary concern, IMO.

>If rogue users can create objects in your schema you have bigger problems
> than tables created outside of working hours
> ...
> >Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.
>
> So you are saying users never be allowed to have access at late hours, or
> per business needs create their own sort tables?
>

At which point did you see me saying that?  What I'm saying is that trying
to monitor user actions after the fact when you suspect some of the users
are "rogue" is not the best approach: it might be too late to review once
damage is done.  A better strategy is to use existing security mechanisms
in PostgreSQL in order to prevent rogue users from logging in (pg_hba.conf)
or perform undesirable actions (GRANT/REVOKE).

"Sort tables"?..  What is that?


> The president of a company that works late would have a problem with that.
>

That was actually one of the points I was making: I see no harm in people
working with the database outside of work hours.  Sometimes it is necessary
and frankly, the most important work quite often happens outside of hours.
Moreover, it is not only the people that use the DB, what about
applications?


> Also, the same applies for developers in the development database, but
> sometimes they forget to drop experimental tables and/or document them.
>

I see absolutely no value in DBA reviewing anything in a dev database
unless asked for specifically by developer.


> The point is to be able to review without hindering.
>

My point would be to secure the system properly to avoid the constant need
for "review".  Or, if you are so concerned, setup a proper audit solution.
None of these involves addition of "relcreated" attribute in pg_class.

>If you are going to review the logs anyway, why not just set
> log_statements=ddl and use logs
>
> That is already done, but a SQL query is faster than a grep, awk and sort
> of a large log.
>

What stops you from importing the parsed log (I can recommend CSV format)
to a DB and perform SQL queries on that?  We already do that on a scale of
hundreds of DB servers at my workplace, for example.

>No one is going to make even a "simple change" just for you to try
>
> I am not asking for a change just for me, I am asking to consider the
> benefit to the PostgreSQL community.
>

That was done and so far no one has expressed any enthusiasm in having this
feature exactly as suggested.

>the idea fails in practice due to all the other things you didn't think
> about.
>
> Really, please state how this will not work. I believe I have successfully
> countered all negative arguments so far.
>

Not at all.

>you can make such a change for yourself and run a patched version for a
> while if you want--no one can stop you here.
>
> I have already stated I cannot. I am not a C coder and attempting to ALTER
> the pg_class system catalog causes an error.
>

Commercial support is available for PostgreSQL from a plenty of "vendors".
At times such commercial support can include writing a new 

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

[Alvaro Herrera]

Tom Lane wrote:
> Melvin Davidson  writes:

> > However, Customer Feedback (
> > https://postgresql.uservoice.com/forums/21853-general
> >  ) does seem to
> > indicate it and give positive results.
> 
> I had never heard of postgresql.uservoice.com before this thread, and
> I daresay most other community members had not either.  It has NO
> standing or influence on our development work.

Actually, to be fair, Peter Eisentraut set it up back in 2009 and
continues to keep it updated.  I thought I had seen it announced, but
now that I look, it seems he only mailed sysadm...@postgresql.org and
never any public list.  From the comments there, it's pretty obvious
that the list is helpful; the number of things marked "done" in recent
times is not small.

I'm not saying that *anything* listed there is useful.  (In particular I
don't think the "relcreated" column provides a lot of value.)

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <
oleksandr.shul...@zalando.de> wrote:

> On Apr 22, 2016 19:46, "Melvin Davidson"  wrote:
> >
> >
> > First, tahnk you for your feedback Alex.
> >
> > "IMO, every time it was conclusively demonstrated that when you consider
> dump/restore semantics, this feature can have exactly zero value if
> implemented *inside pg_catalog*.  And it would have to be a pretty invasive
> change (it's not enough to just add the attribute, you also need to touch
> probably a dozen of places where it will be populated or read), so without
> any positive effect it results in negative effect overall."
>
> I find yor lack of proper email quoting skills disturbing...
>
> > Actually, there is no harm in this. If a database is dumped and restored
> to a new database, then there is no need to reset the value of relcreate,
> as it is a copy of the original db.
>
> Yes, but that means that the timestamps must be part of the dump file,
> which means in turn they can be altered before the restore or--if
> implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
> point in time, so there is little to zero value in having the timestamps in
> the first place. This was already discussed in earlier threads.
>
> > If restoring to the same database. then, by definition, it is a data
> only restore, as objects are already in existence.
>
> OK
>
> > There is no need to touch anyplace other than pg_class to capture when
> an object is created, and
> > leaving relcreated NULL for existing objects has no negative effect.
>
> That is one way to think about it, I do not necessarily agree with it.
>
> > "don't buy the example of "DELETE/DROP TABLE" based on relcreated field.
> Do you, by chance, have any other use case?"
> >
> > Yes, it would greatly assist DBA's in tracking down objects created
> outside normal hours/days.
> > eg: SELECT * FROM pg_class WHERE EXTRACT(DOW FROM relcreated) IN (0,6);
>
> I hope you do not intend to drop the objects found in this way without a
> review? What if such an object was created by a user which is in a
> geographically distant location compared to the server and it was still
> Friday there, but it was already Saturday on the server's clock? You likely
> need to know the user name in addition to the timestamp, so you can verify
> the user's reasons.
>
> > I also mentioned that this is already in the catalogs of Oracle and SQL
> Server.
>
> This sort of argument doesn't help the discussion: there should be a good
> reason to add the feature and merely pointing out that others already doing
> that is not a good reason, IMO.
>
> > "Apart from created timestamp would you not like to also know the
> user/role who has created it?  What about updates (using ALTER
> TABLE)--would you want to know when that *last* happened and who did that?
> Would you want to know what exactly was altered?  Would you want to know
> the history *before* the last update?  Finally, if someone drops the table,
> you can say good bye to its pg_catalog records and there's no hope to know
> who did that and when (or if that table has even existed to start with)."
> >
> > At this point, I am only interested in capturing the creation of
> unauthorized objects by rogue users.
>
> If rogue users can create objects in your schema you have bigger problems
> than tables created outside of working hours (and frankly, I don't see any
> harm in that). Can your rogue users also drop tables?
>
> Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.
>
> > If the query shows objects created at suspicious times. A further review
> of the logs would reveal necessary
> > additional info. Yes, it would be good to capture ALTER's also, but that
> complicates things, so I am only
> > looking for a simple, safe change.
>
> If you are going to review the logs anyway, why not just set
> log_statements=ddl and use logs as the source of timestamp data (together
> with user, connection details, etc.)?
>
> > I really wish people would stop focusing on when features will not work
> and consider more of the benefit they will gain from the situations where
> they do work.
> > I also cannot understand why people are paranoid about adding a simple
> nullable timestamp column to a system catalog,
>
> There yet to be found a good reason to do that. No one is going to make
> even a "simple change" just for you to try and see the idea fails in
> practice due to all the other things you didn't think about. But you can
> make such a change for yourself and run a patched version for a while if
> you want--no one can stop you here.
>
> > especially since no one gave any
> > thought to the adverse effect caused by renaming a column ( procpid to
> pid) in pg_stat_activity when going from 9.1 to 9.2. I bet more than a few
> DBA's had to
> > change their scripts that monitored activity.
>
> Well, I didn't participate in that change discussion and I agree it is a
> bit annoying then these view chan

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

[Shulgin, Oleksandr]

On Apr 22, 2016 19:46, "Melvin Davidson"  wrote:
>
>
> First, tahnk you for your feedback Alex.
>
> "IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall."

I find yor lack of proper email quoting skills disturbing...

> Actually, there is no harm in this. If a database is dumped and restored
to a new database, then there is no need to reset the value of relcreate,
as it is a copy of the original db.

Yes, but that means that the timestamps must be part of the dump file,
which means in turn they can be altered before the restore or--if
implemented as separate commands like ALTER TABLE ... SET TIMESTAMP--at any
point in time, so there is little to zero value in having the timestamps in
the first place. This was already discussed in earlier threads.

> If restoring to the same database. then, by definition, it is a data only
restore, as objects are already in existence.

OK

> There is no need to touch anyplace other than pg_class to capture when an
object is created, and
> leaving relcreated NULL for existing objects has no negative effect.

That is one way to think about it, I do not necessarily agree with it.

> "don't buy the example of "DELETE/DROP TABLE" based on relcreated field.
Do you, by chance, have any other use case?"
>
> Yes, it would greatly assist DBA's in tracking down objects created
outside normal hours/days.
> eg: SELECT * FROM pg_class WHERE EXTRACT(DOW FROM relcreated) IN (0,6);

I hope you do not intend to drop the objects found in this way without a
review? What if such an object was created by a user which is in a
geographically distant location compared to the server and it was still
Friday there, but it was already Saturday on the server's clock? You likely
need to know the user name in addition to the timestamp, so you can verify
the user's reasons.

> I also mentioned that this is already in the catalogs of Oracle and SQL
Server.

This sort of argument doesn't help the discussion: there should be a good
reason to add the feature and merely pointing out that others already doing
that is not a good reason, IMO.

> "Apart from created timestamp would you not like to also know the
user/role who has created it?  What about updates (using ALTER
TABLE)--would you want to know when that *last* happened and who did that?
Would you want to know what exactly was altered?  Would you want to know
the history *before* the last update?  Finally, if someone drops the table,
you can say good bye to its pg_catalog records and there's no hope to know
who did that and when (or if that table has even existed to start with)."
>
> At this point, I am only interested in capturing the creation of
unauthorized objects by rogue users.

If rogue users can create objects in your schema you have bigger problems
than tables created outside of working hours (and frankly, I don't see any
harm in that). Can your rogue users also drop tables?

Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

> If the query shows objects created at suspicious times. A further review
of the logs would reveal necessary
> additional info. Yes, it would be good to capture ALTER's also, but that
complicates things, so I am only
> looking for a simple, safe change.

If you are going to review the logs anyway, why not just set
log_statements=ddl and use logs as the source of timestamp data (together
with user, connection details, etc.)?

> I really wish people would stop focusing on when features will not work
and consider more of the benefit they will gain from the situations where
they do work.
> I also cannot understand why people are paranoid about adding a simple
nullable timestamp column to a system catalog,

There yet to be found a good reason to do that. No one is going to make
even a "simple change" just for you to try and see the idea fails in
practice due to all the other things you didn't think about. But you can
make such a change for yourself and run a patched version for a while if
you want--no one can stop you here.

> especially since no one gave any
> thought to the adverse effect caused by renaming a column ( procpid to
pid) in pg_stat_activity when going from 9.1 to 9.2. I bet more than a few
DBA's had to
> change their scripts that monitored activity.

Well, I didn't participate in that change discussion and I agree it is a
bit annoying then these view change the column names.

--
Alex

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

[Melvin Davidson]

On Fri, Apr 22, 2016 at 12:49 PM, Shulgin, Oleksandr <
oleksandr.shul...@zalando.de> wrote:

> On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson 
> wrote:
>
>> And so far, NO ONE has shown any proof that this enhancement could
>> possibly cause ANY negative result.
>>
>
> Searching through the list archives[1] I can see that you've asked this
> question a number of times already.  And I'm pretty sure it was asked quite
> a number of times by the others.
>
> IMO, every time it was conclusively demonstrated that when you consider
> dump/restore semantics, this feature can have exactly zero value if
> implemented *inside pg_catalog*.  And it would have to be a pretty invasive
> change (it's not enough to just add the attribute, you also need to touch
> probably a dozen of places where it will be populated or read), so without
> any positive effect it results in negative effect overall.
>
>
>> All that has been presented so far are corner cases where this "might"
>> not be useful.
>> If the PostgreSQL developers are really worried about unexpected
>> drawbacks, then, based on that,  ALL future development should stop
>> immediately.
>> This is total insanity! I am asking for a simple, safe enhancement that
>> would add what compatibility with what is already in other databases, yet
>> everyone seems to be terrified about it.
>> We have already modified system catalogs previously with no ill effect.
>>
>
> I believe system catalogs are modified on a regular basis with every major
> release.  But in every instance there has to be a good reason for a change.
>
> So please, someone present a logical explanation of why this should not be
>> done, or how it will negatively impact the PostgreSQL project.
>> If you cannot do so, then start thinking positively.
>>
>
> As said before a number of times: what you propose looks easy, but it's
> just the tip of an iceberg.  Even if the community comes to an agreement
> what dump/restore semantics should be and it is implemented, the feature is
> still not *that* useful on its own to justify its existence (no, I don't
> buy the example of "DELETE/DROP TABLE" based on relcreated field. Do you,
> by chance, have any other use case?)
>
> Apart from created timestamp would you not like to also know the user/role
> who has created it?  What about updates (using ALTER TABLE)--would you want
> to know when that *last* happened and who did that?  Would you want to know
> what exactly was altered?  Would you want to know the history *before* the
> last update?  Finally, if someone drops the table, you can say good bye to
> its pg_catalog records and there's no hope to know who did that and when
> (or if that table has even existed to start with).
>
> When you just start thinking in this direction, it becomes apparent that a
> proper audit solution is a much better fit to tackle these problems.  There
> are features continuously added in the recent releases that will facilitate
> building such solutions in form of extensions: DDL event triggers and
> Logical decoding, to name a few.
>
> Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
>> to present enhancement requests.
>>
>
> There is plenty of information on PostgreSQL sites about this[2,3,4].  Are
> you suggesting something was add yesterday on top of that?
>
> Now that it has been verified this is the correct list,
>>
>
> Probably it is the most appropriate one, unless you have the patch ready
> (then it would be for -hackers).  I'm still puzzled as to how have you
> found that completely unrelated feature request voting site given the
> abundance of information on the official sites and lack of links to that
> site from there.
>
> It is true that some visibility of what majority of users consider to be
> the most useful enhancement could benefit the project, but it has to be
> maintained by the community in order to provide some value.  Otherwise it
> is going to have only the negative impact: an impression that PostgreSQL
> developers doesn't listen to the users.
>
> There still exists no formal requirements for presenting an enhancement
>> request.
>>
>
> Just follow the requirements for a good problem report, especially[5].
> After all you have a problem of a missing feature, right?
>
>
>> WHY am I being vilified for making a simple request? How is it that
>> developers proceed with other enhancements, yet so much negative attention
>> is being given to my request because of unjustified fear that something
>> bad will happen?
>>
>
> Less colorful^W^W plain text mails without top-posting might help here.
> Seriously, not everyone has the time to present the same arguments over and
> over again: searching the archives should have given you some perspective
> on the destiny of this feature request.
>
> Should we really put this on Todo with a mark that we actually don't want
> it?
>
> Regards,
> --
> Alex
>
> [1] http://www.postgresql.org/search/?m=1&q=relcreated
> [2] http://www.postgresql.org/support/
> 

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

[Shulgin, Oleksandr]

On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson 
wrote:

> And so far, NO ONE has shown any proof that this enhancement could
> possibly cause ANY negative result.
>

Searching through the list archives[1] I can see that you've asked this
question a number of times already.  And I'm pretty sure it was asked quite
a number of times by the others.

IMO, every time it was conclusively demonstrated that when you consider
dump/restore semantics, this feature can have exactly zero value if
implemented *inside pg_catalog*.  And it would have to be a pretty invasive
change (it's not enough to just add the attribute, you also need to touch
probably a dozen of places where it will be populated or read), so without
any positive effect it results in negative effect overall.


> All that has been presented so far are corner cases where this "might" not
> be useful.
> If the PostgreSQL developers are really worried about unexpected
> drawbacks, then, based on that,  ALL future development should stop
> immediately.
> This is total insanity! I am asking for a simple, safe enhancement that
> would add what compatibility with what is already in other databases, yet
> everyone seems to be terrified about it.
> We have already modified system catalogs previously with no ill effect.
>

I believe system catalogs are modified on a regular basis with every major
release.  But in every instance there has to be a good reason for a change.

So please, someone present a logical explanation of why this should not be
> done, or how it will negatively impact the PostgreSQL project.
> If you cannot do so, then start thinking positively.
>

As said before a number of times: what you propose looks easy, but it's
just the tip of an iceberg.  Even if the community comes to an agreement
what dump/restore semantics should be and it is implemented, the feature is
still not *that* useful on its own to justify its existence (no, I don't
buy the example of "DELETE/DROP TABLE" based on relcreated field. Do you,
by chance, have any other use case?)

Apart from created timestamp would you not like to also know the user/role
who has created it?  What about updates (using ALTER TABLE)--would you want
to know when that *last* happened and who did that?  Would you want to know
what exactly was altered?  Would you want to know the history *before* the
last update?  Finally, if someone drops the table, you can say good bye to
its pg_catalog records and there's no hope to know who did that and when
(or if that table has even existed to start with).

When you just start thinking in this direction, it becomes apparent that a
proper audit solution is a much better fit to tackle these problems.  There
are features continuously added in the recent releases that will facilitate
building such solutions in form of extensions: DDL event triggers and
Logical decoding, to name a few.

Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
> to present enhancement requests.
>

There is plenty of information on PostgreSQL sites about this[2,3,4].  Are
you suggesting something was add yesterday on top of that?

Now that it has been verified this is the correct list,
>

Probably it is the most appropriate one, unless you have the patch ready
(then it would be for -hackers).  I'm still puzzled as to how have you
found that completely unrelated feature request voting site given the
abundance of information on the official sites and lack of links to that
site from there.

It is true that some visibility of what majority of users consider to be
the most useful enhancement could benefit the project, but it has to be
maintained by the community in order to provide some value.  Otherwise it
is going to have only the negative impact: an impression that PostgreSQL
developers doesn't listen to the users.

There still exists no formal requirements for presenting an enhancement
> request.
>

Just follow the requirements for a good problem report, especially[5].
After all you have a problem of a missing feature, right?


> WHY am I being vilified for making a simple request? How is it that
> developers proceed with other enhancements, yet so much negative attention
> is being given to my request because of unjustified fear that something
> bad will happen?
>

Less colorful^W^W plain text mails without top-posting might help here.
Seriously, not everyone has the time to present the same arguments over and
over again: searching the archives should have given you some perspective
on the destiny of this feature request.

Should we really put this on Todo with a mark that we actually don't want
it?

Regards,
--
Alex

[1] http://www.postgresql.org/search/?m=1&q=relcreated
[2] http://www.postgresql.org/support/
[3] https://wiki.postgresql.org/wiki/FAQ#Where_can_I_get_support.3F
[4] https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
[5]
https://wiki.postgresql.org/wiki/Guide_to_reporting_problems#Things_Not_To_Do

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

[John W Higgins]

On Thu, Apr 21, 2016 at 9:55 AM, Melvin Davidson 
wrote:

> Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop
> with the nay saying.



I think you should look seriously at the suggestion offered of using an
event trigger to get what you desire here. I think the most logical
argument is that there is no need to do anything if a solution is available
on the table right now. Whether or not you agree with someone's fear of the
side effects of a larger solution - if you can use a tool that is already
available you should.

You are also more than happy to produce any patch that you would like to
offer up for inclusion. I completely appreciate that for any reason you may
not be able to offer up such a patch - but on the other hand - you cannot
demand that someone create the patch for you if they do not desire.

John

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

[Jan de Visser]

On Thursday, April 21, 2016 12:55:00 PM EDT Melvin Davidson wrote:
> WHY am I being vilified for making a simple request? How is it that
> developers proceed with other enhancements, yet so much negative attention
> is being given to my request because of unjustified fear that something bad
> will happen?

The open source development model is "scratch your itch". Stuff gets developed 
because people have problems and solve them. Apparently nobody has a 
sufficiently large itch to both scratch it and submit the result to the 
project.

The main difference between a project like this and a commercial product is 
that here there are no product managers defining roadmaps and writing 
requirements, but people bring solutions to problems *they* face or find 
interesting. So, if this is a thing you really care about: do a git checkout 
and start hacking. "Patches welcome".

And you're not being vilified. Your communication style is a tad, um, 
abrasive, and sometimes hard to deal with.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Thu, Apr 21, 2016 at 11:59 AM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson 
> wrote:
>
>> On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver <
>> adrian.kla...@aklaver.com> wrote:
>>
>>> On 04/21/2016 07:53 AM, Melvin Davidson wrote:
>>>
>>>
 "Whether that is worthy or not is the point of your request and really
 depends on more input."
 Correct. And that is what I am looking for. Stating obscure corner cases
 does not rule out the need for an enhancement. If it did, there would be
 no point in any enhancement.
 As of yet, other than this will not work for certain cases, I have not
 heard any argument where this would cause harm to the PostgreSQL
 database (performance or security concern)
 or that this will take any great effort to implement, as I have already
 disproved that in a previous update.

>>>
>>> Making OIDs a default column on user tables was probably not a great
>>> effort either. Easy and all user tables got a built in PK, until folks
>>> started pushing more data into their database and the OID counter wrapped
>>> which had consequences for both user and system tables. Just saying I would
>>> want to hear more from the folks that deal with the internals.
>>>
>>>
>> And your point is? Adding an nullable column with a default of now() to a
>> system catalog has no impact whatsoever on OID's.
>> Please state a relevant  case how this negatively impacts anything.
>> ​
>>
>
> ​Y​
> our grasp of analogy
> ​ could use some work...
>
> That was a long winded way of saying that there is this thing called
> "unintended consequences".​
>
>
> ​https://en.wikipedia.org/wiki/Unintended_consequences
>
> Fear of both "unexpected drawbacks" and "perverse ​results" exist here.
>
> David J.
>
>
"Fear of both "unexpected drawbacks" and "perverse ​results" exist here."

And so far, NO ONE has shown any proof that this enhancement could possibly
cause ANY negative result.
All that has been presented so far are corner cases where this "might" not
be useful.
If the PostgreSQL developers are really worried about unexpected drawbacks,
then, based on that,  ALL future development should stop immediately.
This is total insanity! I am asking for a simple, safe enhancement that
would add what compatibility with what is already in other databases, yet
everyone seems to be terrified about it.
We have already modified system catalogs previously with no ill effect.

So please, someone present a logical explanation of why this should not be
done, or how it will negatively impact the PostgreSQL project.
If you cannot do so, then start thinking positively.

Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE
to present enhancement requests.
Now that it has been verified this is the correct list, There still exists
no formal requirements for presenting an enhancement request.
WHY am I being vilified for making a simple request? How is it that
developers proceed with other enhancements, yet so much negative attention
is being given to my request because of unjustified fear that something bad
will happen?

Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop
with the nay saying.

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

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

[David G. Johnston]

On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson 
wrote:

> On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver  > wrote:
>
>> On 04/21/2016 07:53 AM, Melvin Davidson wrote:
>>
>>
>>> "Whether that is worthy or not is the point of your request and really
>>> depends on more input."
>>> Correct. And that is what I am looking for. Stating obscure corner cases
>>> does not rule out the need for an enhancement. If it did, there would be
>>> no point in any enhancement.
>>> As of yet, other than this will not work for certain cases, I have not
>>> heard any argument where this would cause harm to the PostgreSQL
>>> database (performance or security concern)
>>> or that this will take any great effort to implement, as I have already
>>> disproved that in a previous update.
>>>
>>
>> Making OIDs a default column on user tables was probably not a great
>> effort either. Easy and all user tables got a built in PK, until folks
>> started pushing more data into their database and the OID counter wrapped
>> which had consequences for both user and system tables. Just saying I would
>> want to hear more from the folks that deal with the internals.
>>
>>
> And your point is? Adding an nullable column with a default of now() to a
> system catalog has no impact whatsoever on OID's.
> Please state a relevant  case how this negatively impacts anything.
> ​
>

​Y​
our grasp of analogy
​ could use some work...

That was a long winded way of saying that there is this thing called
"unintended consequences".​


​https://en.wikipedia.org/wiki/Unintended_consequences

Fear of both "unexpected drawbacks" and "perverse ​results" exist here.

David J.

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

[Melvin Davidson]

On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver 
wrote:

> On 04/21/2016 07:53 AM, Melvin Davidson wrote:
>
>
>> "Whether that is worthy or not is the point of your request and really
>> depends on more input."
>> Correct. And that is what I am looking for. Stating obscure corner cases
>> does not rule out the need for an enhancement. If it did, there would be
>> no point in any enhancement.
>> As of yet, other than this will not work for certain cases, I have not
>> heard any argument where this would cause harm to the PostgreSQL
>> database (performance or security concern)
>> or that this will take any great effort to implement, as I have already
>> disproved that in a previous update.
>>
>
> Making OIDs a default column on user tables was probably not a great
> effort either. Easy and all user tables got a built in PK, until folks
> started pushing more data into their database and the OID counter wrapped
> which had consequences for both user and system tables. Just saying I would
> want to hear more from the folks that deal with the internals.
>
>
>
>> --
>> *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.kla...@aklaver.com
>

And your point is? Adding an nullable column with a default of now() to a
system catalog has no impact whatsoever on OID's.
Please state a relevant  case how this negatively impacts anything.

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

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

[Adrian Klaver]

On 04/21/2016 07:53 AM, Melvin Davidson wrote:



"Whether that is worthy or not is the point of your request and really
depends on more input."
Correct. And that is what I am looking for. Stating obscure corner cases
does not rule out the need for an enhancement. If it did, there would be
no point in any enhancement.
As of yet, other than this will not work for certain cases, I have not
heard any argument where this would cause harm to the PostgreSQL
database (performance or security concern)
or that this will take any great effort to implement, as I have already
disproved that in a previous update.


Making OIDs a default column on user tables was probably not a great 
effort either. Easy and all user tables got a built in PK, until folks 
started pushing more data into their database and the OID counter 
wrapped which had consequences for both user and system tables. Just 
saying I would want to hear more from the folks that deal with the 
internals.




--
*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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Thu, Apr 21, 2016 at 10:44 AM, Adrian Klaver 
wrote:

> On 04/21/2016 07:30 AM, Melvin Davidson wrote:
>
>>
>>
>>
>
>>
>> "Just one example of why that assertion does not hold:"
>>
>> I fail to see your point
>>
>> "That is easy. Compare dumps of the current schema against the
>> official schema."
>>
>> So your solution is to do a dump and then grep for anomalies? How is
>> that faster than just querying for recently created objects, or objects
>> created at odd days/hours (weekends/early moring)?
>>
>> You seem to be spending all of your time finding exemptions rather than
>> understanding the benefit. So what is your point? That it is not
>> worthwhile because there are a few cases where it might not work?
>>
>
> The point is that simple is simple, it is dealing with the
> exceptions/corner cases/etc that things get complex. While an individual
> user may want only simple, the developers have to think about the user
> community as a whole and that is where the complexity comes in. Whether
> that is worthy or not is the point of your request and really depends on
> more input.
>
>
>> --
>> *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.kla...@aklaver.com
>

"Whether that is worthy or not is the point of your request and really
depends on more input."
Correct. And that is what I am looking for. Stating obscure corner cases
does not rule out the need for an enhancement. If it did, there would be no
point in any enhancement.
As of yet, other than this will not work for certain cases, I have not
heard any argument where this would cause harm to the PostgreSQL database
(performance or security concern)
or that this will take any great effort to implement, as I have already
disproved that in a previous update.

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

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

[Adrian Klaver]

On 04/21/2016 07:30 AM, Melvin Davidson wrote:








"Just one example of why that assertion does not hold:"

I fail to see your point

"That is easy. Compare dumps of the current schema against the
official schema."

So your solution is to do a dump and then grep for anomalies? How is
that faster than just querying for recently created objects, or objects
created at odd days/hours (weekends/early moring)?

You seem to be spending all of your time finding exemptions rather than
understanding the benefit. So what is your point? That it is not
worthwhile because there are a few cases where it might not work?


The point is that simple is simple, it is dealing with the 
exceptions/corner cases/etc that things get complex. While an individual 
user may want only simple, the developers have to think about the user 
community as a whole and that is where the complexity comes in. Whether 
that is worthy or not is the point of your request and really depends on 
more input.




--
*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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Thu, Apr 21, 2016 at 10:08 AM, Karsten Hilbert 
wrote:

> On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote:
>
> > "and what about user objects added to a database which is
> > then used as a template for creating another DB ?"
> >
> > This existence of objects that are part of the default schema is NOT a
> > problem. Developers and users should never have access to a template.
>
> Just one example of why that assertion does not hold:
>
> GNUmed stores medical records. There's no allowance for
> loosing data. One measure it takes to protect data is to
> execute (roughly) the following sequence when a database
> schema upgrade is needed (currently at major release 21
> thereof). Say, going from v20 to v21:
>
> - create database 'gnumed_v21' template 'gnumed_v20'
> - from this point on gnumed_v20 is NOT TOUCHED anymore
> - at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is
> concerned
> - apply - to gnumed_v21 - those SQL fixups scripts intended to
>   bring v20 up to the very latest minor release of v20
> - apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts
> - apply - to gnumed_v21 - the SQL fixup scripts intended to
>   bring v21 up to the very latest minor release of v21
>
> Whatever goes wrong after having cloned gnumed_v20 into
> gnumed_v21 doesn't matter to the user because they can
> _always_ go back to using the gnumed_v20 database until a
> future upgrade run succeeds at which point they can switch
> over.
>
> Of course, this can also be done via dump v20 / restore into
> v21 but that's slightly more fragile (more things can go
> wrong).
>
> > The point is to be able to track down rogue objects created
> > by developers and users
>
> That is easy. Compare dumps of the current schema against the
> official schema.
>
> In fact, GNUmed does so. The upgrade does not even start if
> the template schema does not pass an md5 comparison and it
> does not consider success unless the upgraded schema passes
> another (target) md5 comparison.
>
> Furthermore, the client refuses to connect to a given
> database if it cannot verify that database's schema via
> expected md5 thereof.
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

"Just one example of why that assertion does not hold:"

I fail to see your point

"That is easy. Compare dumps of the current schema against the
official schema."

So your solution is to do a dump and then grep for anomalies? How is that
faster than just querying for recently created objects, or objects created
at odd days/hours (weekends/early moring)?

You seem to be spending all of your time finding exemptions rather than
understanding the benefit. So what is your point? That it is not worthwhile
because there are a few cases where it might not work?

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

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

[Karsten Hilbert]

On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote:

> "and what about user objects added to a database which is
> then used as a template for creating another DB ?"
> 
> This existence of objects that are part of the default schema is NOT a
> problem. Developers and users should never have access to a template.

Just one example of why that assertion does not hold:

GNUmed stores medical records. There's no allowance for
loosing data. One measure it takes to protect data is to
execute (roughly) the following sequence when a database
schema upgrade is needed (currently at major release 21
thereof). Say, going from v20 to v21:

- create database 'gnumed_v21' template 'gnumed_v20'
- from this point on gnumed_v20 is NOT TOUCHED anymore
- at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is 
concerned
- apply - to gnumed_v21 - those SQL fixups scripts intended to
  bring v20 up to the very latest minor release of v20
- apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts
- apply - to gnumed_v21 - the SQL fixup scripts intended to
  bring v21 up to the very latest minor release of v21

Whatever goes wrong after having cloned gnumed_v20 into
gnumed_v21 doesn't matter to the user because they can
_always_ go back to using the gnumed_v20 database until a
future upgrade run succeeds at which point they can switch
over.

Of course, this can also be done via dump v20 / restore into
v21 but that's slightly more fragile (more things can go
wrong).

> The point is to be able to track down rogue objects created 
> by developers and users

That is easy. Compare dumps of the current schema against the
official schema.

In fact, GNUmed does so. The upgrade does not even start if
the template schema does not pass an md5 comparison and it
does not consider success unless the upgraded schema passes
another (target) md5 comparison.

Furthermore, the client refuses to connect to a given
database if it cannot verify that database's schema via
expected md5 thereof.

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

"and what about user objects added to a database which is
then used as a template for creating another DB ?"

This existence of objects that are part of the default schema is NOT a
problem. Developers and users should never have access to a template. The
point is to be able to track down rogue objects created  by developers and
users and at the same time add the same functionality as already is in
Oracle and SQL Server. What would be your solution?


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

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

[Melvin Davidson]

"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."

Wekk, yes and no. To use event triggers requires that a user first create
their own audit table, then create a function to process the event, and
finally create the event trigger. That works fine for the single database
situation, but when you have hundreds of servers and databases to monitor
(as I did), it makes the implementation a bit more daunting. Whereas having
relcreatedat would mean "no muss, no fuss". :)


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

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

[Melvin Davidson]

On Thu, Apr 21, 2016 at 3:11 AM, Karsten Hilbert 
wrote:

> On Wed, Apr 20, 2016 at 07:09:24PM -0400, Melvin Davidson wrote:
>
> > There is also the situation of tables with limitited use. EG:
> > history_mm, in which case it would facilitate dropping of tables that
> > are no longer needed after x amount of time.
>
> select * from pg_class where to_timestamp(substring(relname from
> 9), 'MM') CONDITION;
>
> Karsten
> --
> GPG key ID E4071346 @ eu.pool.sks-keyservers.net
> E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


"select * from pg_class where to_timestamp(substring(relname from 9),
'MM') CONDITION;"
Yes, that might possibly work, but history_MM was just an example
illustration. What if the table name did not have a date in it?
eg: persons_things_done,
Then you need the creation date of the table.

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

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

[Karsten Hilbert]

On Wed, Apr 20, 2016 at 07:09:24PM -0400, Melvin Davidson wrote:

> There is also the situation of tables with limitited use. EG:
> history_mm, in which case it would facilitate dropping of tables that
> are no longer needed after x amount of time.

select * from pg_class where to_timestamp(substring(relname from 9), 
'MM') CONDITION;

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Karsten Hilbert]

On Wed, Apr 20, 2016 at 05:17:20PM -0500, Kevin Grittner wrote:

> 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.

Would

pg_dump -schema-only
sort
diff official-DDL.sql.sorted

do, too ?

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Karsten Hilbert]

On Wed, Apr 20, 2016 at 03:02:52PM -0700, Adrian Klaver wrote:

> No one is arguing that slapping a new column on pg_class is not easy, just
> that the implications of doing so requires a good deal of thought. The first
> thing that comes to my mind(also in threads on --hackers) is what is the
> creation time?:
> 
> The first time an object was ever created?
> 
> The time it was created in a new database during a
> dump-restore/pg_upgrade/replication?

... and what about user objects added to a database which is
then used as a template for creating another DB ?

- initial add time ?
- template-reuse time ?

Karsten
-- 
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

On Wed, Apr 20, 2016 at 5:30 PM, Melvin Davidson 
wrote:

> On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver 
> 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.

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

[Melvin Davidson]

On Wed, Apr 20, 2016 at 8:01 PM, Adrian Klaver 
wrote:

> On 04/20/2016 04:33 PM, Melvin Davidson wrote:
>
>>
>>
>>
>
>>
>> "Not until pg_upgrade is done or replication is started, in either case
>> a new cluster is started probably at a different time from the original
>> cluster."
>> Not true, whether an upgrade or rep[lication, the relcreatedat time will
>> not/cannot change. It will only change if a new database is created.
>>
>
> We will have to agree to disagree.
>
> 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.
>
> Also in replication case:
>
> Master --> Standby
> Master dies
> Standby gets promoted to new Master.
> STONITH the original Master
> Work continues on the new Master.
> The old Master is resurrected as a new Standby.
>
> To me it would be important to know when the objects actually appeared in
> the various databases as a way of figuring what the above timeline was.
>
>
>>
>> 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.
>>
>>
>> "Again, that is your wish and is fairly simple. Now I usually do not
>> make guarantees, but in this case I will. If pg_class gets an object
>> creation time, the clamor will start immediately for the same thing to
>> be done to the other relevant system catalogs."
>>
>> Fine. As per precedent set today, that is exactly what this list is for.
>> Now that I have initialized the request and started the discussion, that
>> is exactly the kind of feedback I want and the developers should take
>> note of.
>>
>> --
>> *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.kla...@aklaver.com
>

"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?
True, but the whole point of this request is to "start obtaining creation
dates". Once we are at a point
where we have pg_class with relccreatedat, then all subsequent create dates
will be correct. I cannot think
of a single case where having incorrect creation dates from
previous/upgraded databases will cause any harm
or hinder operation of the PostgreSQL. I can however, cite instances where
users create their own tables but do
not notify the dba as such, then cry when something happens because they
are not replicated, as in the case of
using slony. In which case having relcreatedat would go a long ways to
preventing that.

"Master dies
Standby gets promoted to new Master.
STONITH the original Master
Work continues on the new Master.
The old Master is resurrected as a new Standby.
To me it would be important to know when the objects actually appeared in
the various databases as a way of figuring what the above timeline was"

That is the whole point of relcreatedat. A properly replicated database
brings over ALL needed data from the master to the slave(s), including
created objects. It works when promoting the slave, and restoring the
master providing you follow correct procedure. Otherwise, your replication
is useless. I know when working with slony this can cause a problem, but
that is a weakness of slony, not of relcreatedat. Besides, are you more
concerned with keeping the database on line, or tracking object creation
dates when PosgreSQL crashes? You are quoting the corner case. That's like
saying "I refuse to wear a seat belt because I may pass out and drive into
a lake", even though you are in Kansas and driving I-70.

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

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

[Adrian Klaver]

On 04/20/2016 04:33 PM, Melvin Davidson wrote:








"Not until pg_upgrade is done or replication is started, in either case
a new cluster is started probably at a different time from the original
cluster."
Not true, whether an upgrade or rep[lication, the relcreatedat time will
not/cannot change. It will only change if a new database is created.


We will have to agree to disagree.

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.

Also in replication case:

Master --> Standby
Master dies
Standby gets promoted to new Master.
STONITH the original Master
Work continues on the new Master.
The old Master is resurrected as a new Standby.

To me it would be important to know when the objects actually appeared 
in the various databases as a way of figuring what the above timeline was.





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.


"Again, that is your wish and is fairly simple. Now I usually do not
make guarantees, but in this case I will. If pg_class gets an object
creation time, the clamor will start immediately for the same thing to
be done to the other relevant system catalogs."

Fine. As per precedent set today, that is exactly what this list is for.
Now that I have initialized the request and started the discussion, that
is exactly the kind of feedback I want and the developers should take
note of.

--
*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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Wed, Apr 20, 2016 at 7:22 PM, Adrian Klaver 
wrote:

> On 04/20/2016 04:09 PM, Melvin Davidson wrote:
>
>>
>> On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner > > wrote:
>>
>> On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson
>> mailto:melvin6...@gmail.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..
>>
>
> Not until pg_upgrade is done or replication is started, in either case a
> new cluster is started probably at a different time from the original
> cluster. To some people that would indicate they are actually dealing with
> a different object. Again the problem is not the simple case, but the
> complex one. The use case may start out as you state, but once it was in
> the wild, you can rest assured folks will want more.
>
>
>> 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.
>>
>
> Again, that is your wish and is fairly simple. Now I usually do not make
> guarantees, but in this case I will. If pg_class gets an object creation
> time, the clamor will start immediately for the same thing to be done to
> the other relevant system catalogs.
>
>
>> 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_mm, 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.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>


"Not until pg_upgrade is done or replication is started, in either case a
new cluster is started probably at a different time from the original
cluster."
Not true, whether an upgrade or rep[lication, the relcreatedat time will
not/cannot change. It will only change if a new database is created.


> 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.
>

"Again, that is your wish and is fairly simple. Now I usually do not make
guarantees, but in this case I will. If pg_class gets an object creation
time, the clamor will start immediately for the same thing to be done to
the other relevant system catalogs."

Fine. As per precedent set today, that is exactly what this list is for.
Now that I have initialized the request and started the discussion, that is
exactly the kind of feedback I want and the developers

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

[Adrian Klaver]

On 04/20/2016 04:09 PM, Melvin Davidson wrote:


On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner mailto:kgri...@gmail.com>> wrote:

On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson
mailto:melvin6...@gmail.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..


Not until pg_upgrade is done or replication is started, in either case a 
new cluster is started probably at a different time from the original 
cluster. To some people that would indicate they are actually dealing 
with a different object. Again the problem is not the simple case, but 
the complex one. The use case may start out as you state, but once it 
was in the wild, you can rest assured folks will want more.




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.


Again, that is your wish and is fairly simple. Now I usually do not make 
guarantees, but in this case I will. If pg_class gets an object creation 
time, the clamor will start immediately for the same thing to be done to 
the other relevant system catalogs.




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_mm, 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.



--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner  wrote:

> On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson 
> 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_mm, 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.

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

[Kevin Grittner]

On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson 
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

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

[Adrian Klaver]

On 04/20/2016 02:40 PM, Melvin Davidson wrote:


On Wed, Apr 20, 2016 at 5:14 PM, David G. Johnston





Really, it's that hard to add another column to an existing system
catalog and document it? Hmm, let's try
ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreatedat timestamp
default now();
Documentation
NameType
References   Descriptionrelcreatedat | timestamp | |
The date and time the object was initially created

Gee, that took over 4 minutes, I guess it is really is hard to do
this... NOT.


No one is arguing that slapping a new column on pg_class is not easy, 
just that the implications of doing so requires a good deal of thought. 
The first thing that comes to my mind(also in threads on --hackers) is 
what is the creation time?:


The first time an object was ever created?

The time it was created in a new database during a 
dump-restore/pg_upgrade/replication?


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 do not have the capability of adding my own patch, but certainly is is
not that hard to add one new column of type timestamp with a default of
now() to the existing pg_class system catalog. I have already statedthe
logic/reason/need behind it in my initial request, both today and years
ago. as for Tom Lane saying that a forthcoming audit log will fulfill
this request it is not completely true. An audit log will require
additional code for reviewing, whereas a simple SQL query would be able
to determine creation date if the column is added as requested, as per
my previous support post on Oracle and SQL Server capability.

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.

--
*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.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Melvin Davidson]

On Wed, Apr 20, 2016 at 5:14 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Apr 20, 2016 at 1:50 PM, melvin6925  wrote:
>
>> On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson 
>> wrote:
>>
>>> To Tom:
>>> "it *sounds* trivial, until you start thinking about
>>> backup/restore/replication situation"
>>>
>>> That is BS. It is trivial.
>>> In backup/restore, there is no need to record the create date, as a new
>>> create date is only needed if a table is dropped, otherwise if it exists on
>>> restore it is a data restore only situation. If it is to create a new
>>> database, then it is perfectly fine to use the new creation time. As for
>>> replication, then it is a straightforward duplication of create time.
>>>
>>> To David:
>>> "The burden seems to rest with you, not others.  I'll leave it at that
>>> since everything else has already been said elsewhere.
>>> I'll add that failing to point out a previous discussion you were
>>> involved with is inconsiderate to others reading these lists. "
>>>
>>> That is also a poor argument. My initial request was years ago and there
>>> has never been a valid argument as to why  this cannot be done. Please see
>>> my response to Tom Lane.
>>> Further to my point:
>>>
>>> http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle
>>>
>>> http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query
>>> both show proof that this has been implemented in those databases.
>>>
>>> So instead of replying back with "this has already been discussed and
>>> cannot be done", I respectfully request that I get the courtesy of a
>>> _detailed explanation_ of why this is appears so hard. It would also be
>>> nice if you allowed a couple of days for other users to comment before so
>>> rapidly dismissing it. Especially since we just went through a very length
>>> discussion on code of conduct.
>>>
>>>
> ​My response what simply that you brought this up again without any
> reference to prior discussions or any apparent work toward making others
> more likely to not only agree with you but to also perform the work.​  I
> don't really have a position on the actual topic at hand - though I summed
> my thoughts in great detail less than a year ago when you brought this up
> last time:
>
> If your application needs to maintain knowledge of aging it should record
> that information into user-space tables using whatever semantics it
> requires.
>
> ​Courtesy goes both ways and given your bare-bones request it is not
> surprising that I, and likely others, are choosing to respond "go look at
> the previous discussions on this topic".  Its not likely we care to rehash
> our previous arguments in a new thread.
>
>
>>> On Wed, Apr 20, 2016 at 2:58 PM, David G. Johnston <
>>> david.g.johns...@gmail.com> wrote:
>>>
 leaving off-list...

 On Wednesday, April 20, 2016, melvin6925  wrote:

> You are correct, nothing has changed. This is a very simple request
> and should be easy to implement. I have yet to hear a valid, logical
> argument against it. In fact. This has been implemented in both Oracle and
> Sql Server.
>

 The burden seems to rest with you, not others.  I'll leave it at that
 since everything else has already been said elsewhere.

 I'll add that failing to point out a previous discussion you
 were involved with is inconsiderate to others reading these lists.

  I'll admit that maybe a better system for tracking and recording these
 kinds of requests would be nice - though expensive maintain - would be nice
 but in the meantime at least point to known history when bringing something
 like this up.  At worse it shows you did your research.


> ​I personally don't know whether it is worth whatever amount of effort it
> would take to design, document, and implement this capability.  If you want
> a concrete explanation then I'd suggest putting forth an actual patch upon
> which the -hackers can comment.  It doesn't appear that anyone else on
> -hackers is willing to put in the leg work for a feature they appear to
> consider of marginal utility and in a line of work that they fear is likely
> to result in even more requests of a similar nature that they, not the
> people requesting, are apparently going to be on the hook for not only
> maintaining but designing and coding as well.
> ​
>
> What is it that you actually want from the community, and -hackers in
> particular?​  An entry on the wiki TODO list?  An entry in the "We don't
> not want" section of the WIki TODO list?  If all you want is to feel out
> whether someone reading these lists is now willing to write such a patch
> then for the most part it doesn't matter what or how many negative or
> dubious responses you get - the possibility for commit is always open but
> doesn't mean much until one person is willing to write a submit a patch.
>
> David J.
>
>
> Rea

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

[David G. Johnston]

On Wed, Apr 20, 2016 at 1:50 PM, melvin6925  wrote:

> On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson 
> wrote:
>
>> To Tom:
>> "it *sounds* trivial, until you start thinking about
>> backup/restore/replication situation"
>>
>> That is BS. It is trivial.
>> In backup/restore, there is no need to record the create date, as a new
>> create date is only needed if a table is dropped, otherwise if it exists on
>> restore it is a data restore only situation. If it is to create a new
>> database, then it is perfectly fine to use the new creation time. As for
>> replication, then it is a straightforward duplication of create time.
>>
>> To David:
>> "The burden seems to rest with you, not others.  I'll leave it at that
>> since everything else has already been said elsewhere.
>> I'll add that failing to point out a previous discussion you were
>> involved with is inconsiderate to others reading these lists. "
>>
>> That is also a poor argument. My initial request was years ago and there
>> has never been a valid argument as to why  this cannot be done. Please see
>> my response to Tom Lane.
>> Further to my point:
>>
>> http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle
>>
>> http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query
>> both show proof that this has been implemented in those databases.
>>
>> So instead of replying back with "this has already been discussed and
>> cannot be done", I respectfully request that I get the courtesy of a
>> _detailed explanation_ of why this is appears so hard. It would also be
>> nice if you allowed a couple of days for other users to comment before so
>> rapidly dismissing it. Especially since we just went through a very length
>> discussion on code of conduct.
>>
>>
​My response what simply that you brought this up again without any
reference to prior discussions or any apparent work toward making others
more likely to not only agree with you but to also perform the work.​  I
don't really have a position on the actual topic at hand - though I summed
my thoughts in great detail less than a year ago when you brought this up
last time:

If your application needs to maintain knowledge of aging it should record
that information into user-space tables using whatever semantics it
requires.

​Courtesy goes both ways and given your bare-bones request it is not
surprising that I, and likely others, are choosing to respond "go look at
the previous discussions on this topic".  Its not likely we care to rehash
our previous arguments in a new thread.


>> On Wed, Apr 20, 2016 at 2:58 PM, David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> leaving off-list...
>>>
>>> On Wednesday, April 20, 2016, melvin6925  wrote:
>>>
 You are correct, nothing has changed. This is a very simple request and
 should be easy to implement. I have yet to hear a valid, logical argument
 against it. In fact. This has been implemented in both Oracle and Sql
 Server.

>>>
>>> The burden seems to rest with you, not others.  I'll leave it at that
>>> since everything else has already been said elsewhere.
>>>
>>> I'll add that failing to point out a previous discussion you
>>> were involved with is inconsiderate to others reading these lists.
>>>
>>>  I'll admit that maybe a better system for tracking and recording these
>>> kinds of requests would be nice - though expensive maintain - would be nice
>>> but in the meantime at least point to known history when bringing something
>>> like this up.  At worse it shows you did your research.
>>>
>>>
​I personally don't know whether it is worth whatever amount of effort it
would take to design, document, and implement this capability.  If you want
a concrete explanation then I'd suggest putting forth an actual patch upon
which the -hackers can comment.  It doesn't appear that anyone else on
-hackers is willing to put in the leg work for a feature they appear to
consider of marginal utility and in a line of work that they fear is likely
to result in even more requests of a similar nature that they, not the
people requesting, are apparently going to be on the hook for not only
maintaining but designing and coding as well.
​

What is it that you actually want from the community, and -hackers in
particular?​  An entry on the wiki TODO list?  An entry in the "We don't
not want" section of the WIki TODO list?  If all you want is to feel out
whether someone reading these lists is now willing to write such a patch
then for the most part it doesn't matter what or how many negative or
dubious responses you get - the possibility for commit is always open but
doesn't mean much until one person is willing to write a submit a patch.

David J.

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

[melvin6925]

Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone Original 
message From: "David G. Johnston"  Date: 
4/20/2016  16:13  (GMT-05:00) To: Melvin Davidson  
Subject: Re: [GENERAL] Add relcreated (timestamp) column to pg_class catalog to 
record the time an object was created 
try replying to the list
On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson  wrote:
To Tom:
"it *sounds* trivial, until you start thinking about backup/restore/replication 
situation"

That is BS. It is trivial.
In backup/restore, there is no need to record the create date, as a new create 
date is only needed if a table is dropped, otherwise if it exists on restore it 
is a data restore only situation. If it is to create a new database, then it is 
perfectly fine to use the new creation time. As for replication, then it is a 
straightforward duplication of create time. 

To David:
"The burden seems to rest with you, not others.  I'll leave it at that since 
everything else has already been said elsewhere.
I'll add that failing to point out a previous discussion you were involved with 
is inconsiderate to others reading these lists. "

That is also a poor argument. My initial request was years ago and there has 
never been a valid argument as to why  this cannot be done. Please see my 
response to Tom Lane. 
Further to my point: 
http://stackoverflow.com/questions/4442323/how-to-find-out-when-a-particular-table-was-created-in-oracle
http://stackoverflow.com/questions/1171019/sql-server-table-creation-date-query
both show proof that this has been implemented in those databases.

So instead of replying back with "this has already been discussed and cannot be 
done", I respectfully request that I get the courtesy of a _detailed 
explanation_ of why this is appears so hard. It would also be nice if you 
allowed a couple of days for other users to comment before so rapidly 
dismissing it. Especially since we just went through a very length discussion 
on code of conduct.


On Wed, Apr 20, 2016 at 2:58 PM, David G. Johnston  
wrote:
leaving off-list...
On Wednesday, April 20, 2016, melvin6925  wrote:
You are correct, nothing has changed. This is a very simple request and should 
be easy to implement. I have yet to hear a valid, logical argument against it. 
In fact. This has been implemented in both Oracle and Sql Server. 
The burden seems to rest with you, not others.  I'll leave it at that since 
everything else has already been said elsewhere.
 I'll add that failing to point out a previous discussion you were involved 
with is inconsiderate to others reading these lists. 
 I'll admit that maybe a better system for tracking and recording these kinds 
of requests would be nice - though expensive maintain - would be nice but in 
the meantime at least point to known history when bringing something like this 
up.  At worse it shows you did your research.
David J.




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

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

[Tom Lane]

Melvin Davidson  writes:
> *I am a bit confused. If this is the correct list for enhancement requests,
> then why is that not explicitly stated in the list description?

In general, any of the major PG lists are suitable places for discussing
enhancements; either here or pgsql-hackers is most common, depending on
how much technical detail is part of the discussion.

> However, Customer Feedback (
> https://postgresql.uservoice.com/forums/21853-general
>  ) does seem to
> indicate it and give positive results.

I had never heard of postgresql.uservoice.com before this thread, and
I daresay most other community members had not either.  It has NO
standing or influence on our development work.

> That being said, I would like to put
> forth a very simple enhancement request.Add relcreated (timestamp) column
> to pg_class catalog to record the time an object was created.

This has been discussed, and rejected, many times before.  Please consult
the PG list archives to find previous threads about it.  I'll just note
that it *sounds* trivial, until you start thinking about backup/restore/
replication situations, and then you realize that the required semantics
are far from clear.  In practice, audit logs (which is a class of feature
that we are working on) are a far better solution.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[David G. Johnston]

On Wednesday, April 20, 2016, Melvin Davidson  wrote:

>
>
>
>
>
>
>
> *I am a bit confused. If this is the correct list for enhancement
> requests, then why is that not explicitly stated in the list description?
> Then again, none of the list descriptions mention they are are the correct
> ones for enhancement requests. However, Customer Feedback (
> https://postgresql.uservoice.com/forums/21853-general
>  ) does seem to
> indicate it and give positive results.That being said, I would like to put
> forth a very simple enhancement request.Add relcreated (timestamp) column
> to pg_class catalog to record the time an object was created.Adding
> relcreated column to pg_class 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';There are those whom have argued that this would create
> a problem with table restore from pg_dump, but it does not. Simply make it
> an attribute of CREATE TABLE. The only requirement would be to insure that
> the date cannot be a future date.*
>

I'm reasonably certain nothing has changed since the last time you've made
this request...

David J.