On Wed, Apr 20, 2016 at 7:22 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 04/20/2016 04:09 PM, Melvin Davidson wrote:
On Wed, Apr 20, 2016 at 6:17 PM, Kevin Grittner <kgrittn@xxxxxxxxx
<mailto:kgrittn@xxxxxxxxx>> wrote:
On Wed, Apr 20, 2016 at 4:40 PM, Melvin Davidson<melvin6925@xxxxxxxxx <mailto:melvin6925@xxxxxxxxx>> 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_yyyymm, 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.klaver@xxxxxxxxxxx
"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 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.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
