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

On Apr 22, 2016 19:46, "Melvin Davidson" <melvin6925@gmail.com> 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