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(at)gmail(dot)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