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

On Wednesday, April 20, 2016, Melvin Davidson <melvin6925@gmail.com> 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
<https://postgresql.uservoice.com/forums/21853-general&gt; ) 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.

Melvin Davidson <melvin6925@gmail.com> 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
<https://postgresql.uservoice.com/forums/21853-general&gt; ) 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

Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone-------- Original message --------From: "David G. Johnston" <david.g.johnston@gmail.com> Date: 4/20/2016 16:13 (GMT-05:00) To: Melvin Davidson <melvin6925@gmail.com> 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 <melvin6925@gmail.com> 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 <david.g.johnston@gmail.com> wrote:
leaving off-list...
On Wednesday, April 20, 2016, melvin6925 <melvin6925@gmail.com> 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.

On Wed, Apr 20, 2016 at 1:50 PM, melvin6925 <melvin6925@gmail.com> wrote:

On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson <melvin6925@gmail.com>
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.johnston@gmail.com> wrote:

leaving off-list...

On Wednesday, April 20, 2016, melvin6925 <melvin6925@gmail.com> 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.

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

On Wed, Apr 20, 2016 at 1:50 PM, melvin6925 <melvin6925@gmail.com> wrote:

On Wed, Apr 20, 2016 at 12:59 PM, Melvin Davidson <melvin6925@gmail.com>
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.johnston@gmail.com> wrote:

leaving off-list...

On Wednesday, April 20, 2016, melvin6925 <melvin6925@gmail.com> 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.

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

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
Name Type
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.klaver@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

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

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

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

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

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

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

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

On Wed, Apr 20, 2016 at 7:22 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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

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

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

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

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

On Wed, Apr 20, 2016 at 5:30 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

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

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

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

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_yyyymm, 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), 'YYYYMM') 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

On Thu, Apr 21, 2016 at 3:11 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
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_yyyymm, 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), 'YYYYMM') 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),
'YYYYMM') CONDITION;"
Yes, that might possibly work, but history_YYYYMM 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.

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

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