Home > mailing lists

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

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

From

melvin6925

Date:

20 April 2016, 23:50:38

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.

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

From

"David G. Johnston"

Date:

21 April 2016, 00:14:12

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.

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

From

Melvin Davidson

Date:

21 April 2016, 00:41:03

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.

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

From

Adrian Klaver

Date:

21 April 2016, 01:02:59

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

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

From

Kevin Grittner

Date:

21 April 2016, 01:17:26

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

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

From

Melvin Davidson

Date:

21 April 2016, 02:10:10

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.

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

From

Adrian Klaver

Date:

21 April 2016, 02:22:58

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

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

From

Melvin Davidson

Date:

21 April 2016, 02:33:50

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.

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

From

Adrian Klaver

Date:

21 April 2016, 03:01:15

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

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

From

Melvin Davidson

Date:

21 April 2016, 03:31:27

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.

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

From

"David G. Johnston"

Date:

21 April 2016, 03:50:16

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.

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

From

Karsten Hilbert

Date:

21 April 2016, 10:02:05

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

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

From

Karsten Hilbert

Date:

21 April 2016, 10:04:13

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

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

From

Karsten Hilbert

Date:

21 April 2016, 10:11:32

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

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

From

Melvin Davidson

Date:

21 April 2016, 15:50:41

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.

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

From

Melvin Davidson

Date:

21 April 2016, 16:10:17

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

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

From

Melvin Davidson

Date:

21 April 2016, 16:41:05

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

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

From

Karsten Hilbert

Date:

21 April 2016, 17:08:44

On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote:

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

Just one example of why that assertion does not hold:

GNUmed stores medical records. There's no allowance for
loosing data. One measure it takes to protect data is to
execute (roughly) the following sequence when a database
schema upgrade is needed (currently at major release 21
thereof). Say, going from v20 to v21:

- create database 'gnumed_v21' template 'gnumed_v20'
- from this point on gnumed_v20 is NOT TOUCHED anymore
- at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is concerned
- apply - to gnumed_v21 - those SQL fixups scripts intended to
  bring v20 up to the very latest minor release of v20
- apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts
- apply - to gnumed_v21 - the SQL fixup scripts intended to
  bring v21 up to the very latest minor release of v21

Whatever goes wrong after having cloned gnumed_v20 into
gnumed_v21 doesn't matter to the user because they can
_always_ go back to using the gnumed_v20 database until a
future upgrade run succeeds at which point they can switch
over.

Of course, this can also be done via dump v20 / restore into
v21 but that's slightly more fragile (more things can go
wrong).

> The point is to be able to track down rogue objects created
> by developers and users

That is easy. Compare dumps of the current schema against the
official schema.

In fact, GNUmed does so. The upgrade does not even start if
the template schema does not pass an md5 comparison and it
does not consider success unless the upgraded schema passes
another (target) md5 comparison.

Furthermore, the client refuses to connect to a given
database if it cannot verify that database's schema via
expected md5 thereof.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

From

Melvin Davidson

Date:

21 April 2016, 17:31:29

On Thu, Apr 21, 2016 at 10:08 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote:

On Thu, Apr 21, 2016 at 09:40:18AM -0400, Melvin Davidson wrote:

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

Just one example of why that assertion does not hold:

GNUmed stores medical records. There's no allowance for
loosing data. One measure it takes to protect data is to
execute (roughly) the following sequence when a database
schema upgrade is needed (currently at major release 21
thereof). Say, going from v20 to v21:

- create database 'gnumed_v21' template 'gnumed_v20'
- from this point on gnumed_v20 is NOT TOUCHED anymore
- at this point gnumed_v21 is identical to gnumed_v20 as far as GNUmed is concerned
- apply - to gnumed_v21 - those SQL fixups scripts intended to
bring v20 up to the very latest minor release of v20
- apply - to gnumed_v21 - the v20.latest->v21 upgrade SQL scripts
- apply - to gnumed_v21 - the SQL fixup scripts intended to
bring v21 up to the very latest minor release of v21

Whatever goes wrong after having cloned gnumed_v20 into
gnumed_v21 doesn't matter to the user because they can
_always_ go back to using the gnumed_v20 database until a
future upgrade run succeeds at which point they can switch
over.

Of course, this can also be done via dump v20 / restore into
v21 but that's slightly more fragile (more things can go
wrong).

> The point is to be able to track down rogue objects created
> by developers and users

That is easy. Compare dumps of the current schema against the
official schema.

In fact, GNUmed does so. The upgrade does not even start if
the template schema does not pass an md5 comparison and it
does not consider success unless the upgraded schema passes
another (target) md5 comparison.

Furthermore, the client refuses to connect to a given
database if it cannot verify that database's schema via
expected md5 thereof.

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

"Just one example of why that assertion does not hold:"

I fail to see your point

"That is easy. Compare dumps of the current schema against the
official schema."

So your solution is to do a dump and then grep for anomalies? How is that faster than just querying for recently created objects, or objects created at odd days/hours (weekends/early moring)?

You seem to be spending all of your time finding exemptions rather than understanding the benefit. So what is your point? That it is not worthwhile because there are a few cases where it might not work?

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

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

From

Adrian Klaver

Date:

21 April 2016, 17:44:43

On 04/21/2016 07:30 AM, Melvin Davidson wrote:
>
>

>
>
> "Just one example of why that assertion does not hold:"
>
> I fail to see your point
>
> "That is easy. Compare dumps of the current schema against the
> official schema."
>
> So your solution is to do a dump and then grep for anomalies? How is
> that faster than just querying for recently created objects, or objects
> created at odd days/hours (weekends/early moring)?
>
> You seem to be spending all of your time finding exemptions rather than
> understanding the benefit. So what is your point? That it is not
> worthwhile because there are a few cases where it might not work?

The point is that simple is simple, it is dealing with the
exceptions/corner cases/etc that things get complex. While an individual
user may want only simple, the developers have to think about the user
community as a whole and that is where the complexity comes in. Whether
that is worthy or not is the point of your request and really depends on
more input.

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

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

From

Melvin Davidson

Date:

21 April 2016, 17:54:13

On Thu, Apr 21, 2016 at 10:44 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/21/2016 07:30 AM, Melvin Davidson wrote:

"Just one example of why that assertion does not hold:"

I fail to see your point

"That is easy. Compare dumps of the current schema against the
official schema."

So your solution is to do a dump and then grep for anomalies? How is
that faster than just querying for recently created objects, or objects
created at odd days/hours (weekends/early moring)?

You seem to be spending all of your time finding exemptions rather than
understanding the benefit. So what is your point? That it is not
worthwhile because there are a few cases where it might not work?

The point is that simple is simple, it is dealing with the exceptions/corner cases/etc that things get complex. While an individual user may want only simple, the developers have to think about the user community as a whole and that is where the complexity comes in. Whether that is worthy or not is the point of your request and really depends on more input.

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

"Whether that is worthy or not is the point of your request and really depends on more input."

Correct. And that is what I am looking for. Stating obscure corner cases does not rule out the need for an enhancement. If it did, there would be no point in any enhancement.

As of yet, other than this will not work for certain cases, I have not heard any argument where this would cause harm to the PostgreSQL database (performance or security concern)

or that this will take any great effort to implement, as I have already disproved that in a previous update.

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

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

From

Adrian Klaver

Date:

21 April 2016, 18:08:59

On 04/21/2016 07:53 AM, Melvin Davidson wrote:

>
> "Whether that is worthy or not is the point of your request and really
> depends on more input."
> Correct. And that is what I am looking for. Stating obscure corner cases
> does not rule out the need for an enhancement. If it did, there would be
> no point in any enhancement.
> As of yet, other than this will not work for certain cases, I have not
> heard any argument where this would cause harm to the PostgreSQL
> database (performance or security concern)
> or that this will take any great effort to implement, as I have already
> disproved that in a previous update.

Making OIDs a default column on user tables was probably not a great
effort either. Easy and all user tables got a built in PK, until folks
started pushing more data into their database and the OID counter
wrapped which had consequences for both user and system tables. Just
saying I would want to hear more from the folks that deal with the
internals.

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

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

From

Melvin Davidson

Date:

21 April 2016, 18:19:21

On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/21/2016 07:53 AM, Melvin Davidson wrote:

"Whether that is worthy or not is the point of your request and really
depends on more input."
Correct. And that is what I am looking for. Stating obscure corner cases
does not rule out the need for an enhancement. If it did, there would be
no point in any enhancement.
As of yet, other than this will not work for certain cases, I have not
heard any argument where this would cause harm to the PostgreSQL
database (performance or security concern)
or that this will take any great effort to implement, as I have already
disproved that in a previous update.

Making OIDs a default column on user tables was probably not a great effort either. Easy and all user tables got a built in PK, until folks started pushing more data into their database and the OID counter wrapped which had consequences for both user and system tables. Just saying I would want to hear more from the folks that deal with the internals.

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

And your point is? Adding an nullable column with a default of now() to a system catalog has no impact whatsoever on OID's.

Please state a relevant case how this negatively impacts anything.

--

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

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

From

"David G. Johnston"

Date:

21 April 2016, 18:59:43

On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/21/2016 07:53 AM, Melvin Davidson wrote:

"Whether that is worthy or not is the point of your request and really
depends on more input."
Correct. And that is what I am looking for. Stating obscure corner cases
does not rule out the need for an enhancement. If it did, there would be
no point in any enhancement.
As of yet, other than this will not work for certain cases, I have not
heard any argument where this would cause harm to the PostgreSQL
database (performance or security concern)
or that this will take any great effort to implement, as I have already
disproved that in a previous update.

Making OIDs a default column on user tables was probably not a great effort either. Easy and all user tables got a built in PK, until folks started pushing more data into their database and the OID counter wrapped which had consequences for both user and system tables. Just saying I would want to hear more from the folks that deal with the internals.

And your point is? Adding an nullable column with a default of now() to a system catalog has no impact whatsoever on OID's.
Please state a relevant case how this negatively impacts anything.

our grasp of analogy

could use some work...

That was a long winded way of saying that there is this thing called "unintended consequences".

https://en.wikipedia.org/wiki/Unintended_consequences

Fear of both "unexpected drawbacks" and "perverse results" exist here.

David J.

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

From

Melvin Davidson

Date:

21 April 2016, 19:55:46

On Thu, Apr 21, 2016 at 11:59 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Thu, Apr 21, 2016 at 8:18 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
On Thu, Apr 21, 2016 at 11:08 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 04/21/2016 07:53 AM, Melvin Davidson wrote:

"Whether that is worthy or not is the point of your request and really
depends on more input."
Correct. And that is what I am looking for. Stating obscure corner cases
does not rule out the need for an enhancement. If it did, there would be
no point in any enhancement.
As of yet, other than this will not work for certain cases, I have not
heard any argument where this would cause harm to the PostgreSQL
database (performance or security concern)
or that this will take any great effort to implement, as I have already
disproved that in a previous update.

Making OIDs a default column on user tables was probably not a great effort either. Easy and all user tables got a built in PK, until folks started pushing more data into their database and the OID counter wrapped which had consequences for both user and system tables. Just saying I would want to hear more from the folks that deal with the internals.

And your point is? Adding an nullable column with a default of now() to a system catalog has no impact whatsoever on OID's.
Please state a relevant case how this negatively impacts anything.

Y
our grasp of analogy
could use some work...

That was a long winded way of saying that there is this thing called "unintended consequences".

https://en.wikipedia.org/wiki/Unintended_consequences

Fear of both "unexpected drawbacks" and "perverse results" exist here.

David J.

"Fear of both "unexpected drawbacks" and "perverse results" exist here."

And so far, NO ONE has shown any proof that this enhancement could possibly cause ANY negative result.

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.

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.

Previous to yesterday, nowhere on the PostgreSQL site was it stated WHERE to present enhancement requests.

Now that it has been verified this is the correct list, There still exists no formal requirements for presenting an enhancement request.

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?

Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop with the nay saying.

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

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

From

Jan de Visser

Date:

21 April 2016, 20:10:33

On Thursday, April 21, 2016 12:55:00 PM EDT Melvin Davidson wrote:
> 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?

The open source development model is "scratch your itch". Stuff gets developed
because people have problems and solve them. Apparently nobody has a
sufficiently large itch to both scratch it and submit the result to the
project.

The main difference between a project like this and a commercial product is
that here there are no product managers defining roadmaps and writing
requirements, but people bring solutions to problems *they* face or find
interesting. So, if this is a thing you really care about: do a git checkout
and start hacking. "Patches welcome".

And you're not being vilified. Your communication style is a tad, um,
abrasive, and sometimes hard to deal with.

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

From

John W Higgins

Date:

21 April 2016, 20:53:30

On Thu, Apr 21, 2016 at 9:55 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

Please, just ONE LOGICAL VALID argument, not speculation. Otherwise, stop with the nay saying.

I think you should look seriously at the suggestion offered of using an event trigger to get what you desire here. I think the most logical argument is that there is no need to do anything if a solution is available on the table right now. Whether or not you agree with someone's fear of the side effects of a larger solution - if you can use a tool that is already available you should.

You are also more than happy to produce any patch that you would like to offer up for inclusion. I completely appreciate that for any reason you may not be able to offer up such a patch - but on the other hand - you cannot demand that someone create the patch for you if they do not desire.

John

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

From

"Shulgin, Oleksandr"

Date:

22 April 2016, 19:50:17

On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson <melvin6925@gmail.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

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

From

Melvin Davidson

Date:

22 April 2016, 20:46:48

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

On Thu, Apr 21, 2016 at 6:55 PM, Melvin Davidson <melvin6925@gmail.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, and
leaving 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.

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

From

"Shulgin, Oleksandr"

Date:

23 April 2016, 08:03:55

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.

> 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

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

From

Melvin Davidson

Date:

23 April 2016, 16:17:57

On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <oleksandr.shulgin@zalando.de> wrote:

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

>I find yor lack of proper email quoting skills disturbing..

I am sorry you are disturbed, but thank you for pointing that out. I have revised my style to make you feel more comfortable.

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

No, when restoring to a new database, then by definition, object creation time is when they are loaded into the new database, which is essentially after the new database is created.

>I hope you do not intend to drop the objects found in this way without a review?

Of course not.

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

Actually, it does. One of the reasons PostgreSQL is growing in popularity is that many companies are looking to switch away from Oracle and SQL Server and switch to open source PostgreSQL to save money. In doing so, consideratopn of the compatibilty and features is of prime concern.

>If rogue users can create objects in your schema you have bigger problems than tables created outside of working hours
...
>Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

So you are saying users never be allowed to have access at late hours, or per business needs create their own sort tables? The president of a company that works late would have a problem with that. Also, the same applies for developers in the development database, but sometimes they forget to drop experimental tables and/or document them. The point is to be able to review without hindering.

>If you are going to review the logs anyway, why not just set log_statements=ddl and use logs

That is already done, but a SQL query is faster than a grep, awk and sort of a large log.

>No one is going to make even a "simple change" just for you to try

I am not asking for a change just for me, I am asking to consider the benefit to the PostgreSQL community.

>the idea fails in practice due to all the other things you didn't think about.

Really, please state how this will not work. I believe I have successfully countered all negative arguments so far.

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

I have already stated I cannot. I am not a C coder and attempting to ALTER the pg_class system catalog causes an error.
I am hoping the PostgreSQL developers http://www.postgresql.org/community/contributors/ will review and either say yea or provide a specific reason for rejection.

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

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

From

"Shulgin, Oleksandr"

Date:

26 April 2016, 10:41:23

On Sat, Apr 23, 2016 at 3:17 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Sat, Apr 23, 2016 at 1:03 AM, Shulgin, Oleksandr <oleksandr.shulgin@zalando.de> wrote:
>I find yor lack of proper email quoting skills disturbing..

I am sorry you are disturbed, but thank you for pointing that out. I have revised my style to make you feel more comfortable.

Nothing to be sorry about, but I still don't see any improvement on your part :-/

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

No, when restoring to a new database, then by definition, object creation time is when they are loaded into the new database, which is essentially after the new database is created.

Well, that is the problem. By *your* definition that might be true, but not necessarily by anyone's else. Consider a DB restored from backup on a Saturday night due to server failure. All objects now have "outside of working hours" creation timestamps. Does that create more problems in your environment?

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

Actually, it does. One of the reasons PostgreSQL is growing in popularity is that many companies are looking to switch away from Oracle and SQL Server and switch to open source PostgreSQL to save money. In doing so, consideratopn of the compatibilty and features is of prime concern.

Well, I can see some reason in that, but what we are discussing here is not a feature of primary concern, IMO.

>If rogue users can create objects in your schema you have bigger problems than tables created outside of working hours
...
>Your better bet is to keep rogue users out: using GRANTs and pg_hba.conf.

So you are saying users never be allowed to have access at late hours, or per business needs create their own sort tables?

At which point did you see me saying that? What I'm saying is that trying to monitor user actions after the fact when you suspect some of the users are "rogue" is not the best approach: it might be too late to review once damage is done. A better strategy is to use existing security mechanisms in PostgreSQL in order to prevent rogue users from logging in (pg_hba.conf) or perform undesirable actions (GRANT/REVOKE).

"Sort tables"?.. What is that?

The president of a company that works late would have a problem with that.

That was actually one of the points I was making: I see no harm in people working with the database outside of work hours. Sometimes it is necessary and frankly, the most important work quite often happens outside of hours. Moreover, it is not only the people that use the DB, what about applications?

Also, the same applies for developers in the development database, but sometimes they forget to drop experimental tables and/or document them.

I see absolutely no value in DBA reviewing anything in a dev database unless asked for specifically by developer.

The point is to be able to review without hindering.

My point would be to secure the system properly to avoid the constant need for "review". Or, if you are so concerned, setup a proper audit solution. None of these involves addition of "relcreated" attribute in pg_class.

>If you are going to review the logs anyway, why not just set log_statements=ddl and use logs

That is already done, but a SQL query is faster than a grep, awk and sort of a large log.

What stops you from importing the parsed log (I can recommend CSV format) to a DB and perform SQL queries on that? We already do that on a scale of hundreds of DB servers at my workplace, for example.

>No one is going to make even a "simple change" just for you to try

I am not asking for a change just for me, I am asking to consider the benefit to the PostgreSQL community.

That was done and so far no one has expressed any enthusiasm in having this feature exactly as suggested.

>the idea fails in practice due to all the other things you didn't think about.

Really, please state how this will not work. I believe I have successfully countered all negative arguments so far.

Not at all.

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

I have already stated I cannot. I am not a C coder and attempting to ALTER the pg_class system catalog causes an error.

Commercial support is available for PostgreSQL from a plenty of "vendors". At times such commercial support can include writing a new feature into the database engine. I've heard that companies created around such commercial support usually employ some pretty qualified people. ;-)

I am hoping the PostgreSQL developers http://www.postgresql.org/community/contributors/ will review and either say yea or provide a specific reason for rejection.

A handful of reasons were given this time and when you've made this request earlier. I don't believe there is any justification in saying no specific reason was given.

Have a great day!

Alex