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

On Fri, Apr 22, 2016 at 12:49 PM, Shulgin, Oleksandr <
oleksandr(dot)shulgin(at)zalando(dot)de> wrote:

> On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> 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
>
>
*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."

*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.If restoring to the same database. then,
by definition, it is a data only restore, as objects are already in
existence. There is no need to touch anyplace other than pg_class to
capture when an object is created, andleaving relcreated NULL for existing
objects has no negative effect. *

"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 also mentioned that this is already in the catalogs
of Oracle and SQL Server.*

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

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

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