Обсуждение: Date created for tables

Chloe Dives <Chloe.Dives@cantabcapital.com> writes:
> Having moved to PostgreSQL from Oracle a few years ago I have been generally very impressed by Postgres, but there
area few things that I still miss. One of those is being able to see the created and last modified dates for database
objects.
> Is this something that has been considered for implementation?

It's been considered, and rejected, many times.  Aside from the overhead
involved, there are too many different ideas of what such dates ought to
mean (e.g., what should happen during dump/restore? does a failed
transaction update last-modified? etc etc).  You can search the
project's mailing list archives if you want to read the prior discussions.

            regards, tom lane

On 12/5/19 9:10 AM, Chloe Dives wrote:
> Having moved to PostgreSQL from Oracle a few years ago I have been 
> generally very impressed by Postgres, but there are a few things that I 
> still miss. One of those is being able to see the created and last 
> modified dates for database objects.
> 
> Is this something that has been considered for implementation?

An alternative?:

https://www.postgresql.org/message-id/ABA5562F-56A9-4AB1-95D6-398215015DBD%40gmail.com

> 
> Thanks in advance,
> 
> Chloe
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/5/19 1:01 PM, Tom Lane wrote:
> Chloe Dives <Chloe.Dives@cantabcapital.com> writes:
>> Having moved to PostgreSQL from Oracle a few years ago I have been generally very impressed by Postgres, but there
area few things that I still miss. One of those is being able to see the created and last modified dates for database
objects.
>> Is this something that has been considered for implementation?
> It's been considered, and rejected, many times.  Aside from the overhead
> involved, there are too many different ideas of what such dates ought to
> mean (e.g., what should happen during dump/restore? does a failed
> transaction update last-modified? etc etc).  You can search the
> project's mailing list archives if you want to read the prior discussions.

All the other RDBMSs seem to have figured it out.

-- 
Angular momentum makes the world go 'round.

On Thu, Dec 05, 2019 at 07:12:22PM -0600, Ron wrote:
> On 12/5/19 1:01 PM, Tom Lane wrote:
>> It's been considered, and rejected, many times.  Aside from the overhead
>> involved, there are too many different ideas of what such dates ought to
>> mean (e.g., what should happen during dump/restore? does a failed
>> transaction update last-modified? etc etc).  You can search the
>> project's mailing list archives if you want to read the prior discussions.
>
> All the other RDBMSs seem to have figured it out.

It does not necessarily mean that Postgres has to do it.  FWIW, you
can track that using an even trigger for CREATE TABLE or other objects
which inserts the following in a table of your choice for a given
database:
- The timestamp of the transaction.
- The object name.
- Its class ID, say pg_class::regclass for a table, etc.
--
Michael

On 12/5/19 7:40 PM, Michael Paquier wrote:
> On Thu, Dec 05, 2019 at 07:12:22PM -0600, Ron wrote:
>> On 12/5/19 1:01 PM, Tom Lane wrote:
>>> It's been considered, and rejected, many times.  Aside from the overhead
>>> involved, there are too many different ideas of what such dates ought to
>>> mean (e.g., what should happen during dump/restore? does a failed
>>> transaction update last-modified? etc etc).  You can search the
>>> project's mailing list archives if you want to read the prior discussions.
>> All the other RDBMSs seem to have figured it out.
> It does not necessarily mean that Postgres has to do it.

That's not what I wrote.

-- 
Angular momentum makes the world go 'round.

On Thu, Dec  5, 2019 at 05:10:20PM +0000, Chloe Dives wrote:
> Having moved to PostgreSQL from Oracle a few years ago I have been generally
> very impressed by Postgres, but there are a few things that I still miss. One
> of those is being able to see the created and last modified dates for database
> objects.
> 
>  
> 
> Is this something that has been considered for implementation?

I wrote a blog about this:

    https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

On Thu, Dec  5, 2019 at 05:10:20PM +0000, Chloe Dives wrote:

Having moved to PostgreSQL from Oracle a few years ago I have been generally
very impressed by Postgres, but there are a few things that I still miss. One
of those is being able to see the created and last modified dates for database
objects.


Is this something that has been considered for implementation?

I wrote a blog about this:
https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017

Ron schrieb am 24.12.2019 um 03:14:
>>> Having moved to PostgreSQL from Oracle a few years ago I have been generally
>>> very impressed by Postgres, but there are a few things that I still miss. One
>>> of those is being able to see the created and last modified dates for database
>>> objects.
>>>
>>> Is this something that has been considered for implementation?
>> I wrote a blog about this:
>>
>>     https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017
>
> You all are *grossly* over-complicating this.
>
> By creation time, "we DBAs" think the time we ran "CREATE object", not when pg_dump, pg_basebackup and pg_update ran.
>
> Likewise, modification time is when we last ran an ALTER command ran, not when VACUUM ran (that's tracked elsewhere)
orDML ran. 
>
> That's all.

+1

Although I don't really need this, there were a few situations where this came in handy in Oracle.

I think _any_ tracking would already help those people that need something like that.
Simply picking the easiest implementation and documenting the situations where those columns are updated would probably
beenough. 

On 12/23/19 6:14 PM, Ron wrote:
> On 12/23/19 7:01 PM, Bruce Momjian wrote:
>> On Thu, Dec  5, 2019 at 05:10:20PM +0000, Chloe Dives wrote:
>>> Having moved to PostgreSQL from Oracle a few years ago I have been generally
>>> very impressed by Postgres, but there are a few things that I still miss. One
>>> of those is being able to see the created and last modified dates for database
>>> objects.
>>>
>>>   
>>>
>>> Is this something that has been considered for implementation?
>> I wrote a blog about this:
>>
>>     https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017
> 
> You all are *grossly* over-complicating this.

Not really. This discussion has come up before and it starts with the 
simple case of timestamp the initial CREATE. This would suffice for some 
folks. However, it then progresses into a request for full object audit 
system. I understand why there is no great desire to start down this 
path by the developers, they know the pressure would be on to expand the 
code. As Fabrízio mentions in another post this is something that could 
be covered in an extension. FYI, I do it by using Sqitch for my schema 
object creation.

> 
> By creation time, "we DBAs" think the time we ran "CREATE object", not 
> when pg_dump, pg_basebackup and pg_update ran.
> 
> Likewise, modification time is when we last ran an ALTER command ran, 
> not when VACUUM ran (that's tracked elsewhere) or DML ran.
> 
> That's all.
> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/23/19 6:14 PM, Ron wrote:

On 12/23/19 7:01 PM, Bruce Momjian wrote:

On Thu, Dec  5, 2019 at 05:10:20PM +0000, Chloe Dives wrote:

Having moved to PostgreSQL from Oracle a few years ago I have been generally
very impressed by Postgres, but there are a few things that I still miss. One
of those is being able to see the created and last modified dates for database
objects.

 
Is this something that has been considered for implementation?

I wrote a blog about this:

    https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017

You all are *grossly* over-complicating this.

Not really. This discussion has come up before and it starts with the simple case of timestamp the initial CREATE. This would suffice for some folks. However, it then progresses into a request for full object audit system.

I understand why there is no great desire to start down this path by the developers, they know the pressure would be on to expand the code. As Fabrízio mentions in another post this is something that could be covered in an extension. FYI, I do it by using Sqitch for my schema object creation.

By creation time, "we DBAs" think the time we ran "CREATE object", not when pg_dump, pg_basebackup and pg_update ran.

Likewise, modification time is when we last ran an ALTER command ran, not when VACUUM ran (that's tracked elsewhere) or DML ran.

That's all.

-- 
Angular momentum makes the world go 'round.

On 12/24/19 8:44 AM, Ron wrote:
> On 12/24/19 10:39 AM, Adrian Klaver wrote:
>> On 12/23/19 6:14 PM, Ron wrote:
>>> On 12/23/19 7:01 PM, Bruce Momjian wrote:

>>>>> Is this something that has been considered for implementation?
>>>> I wrote a blog about this:
>>>>
>>>> https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017
>>>
>>> You all are *grossly* over-complicating this.
>>
>> Not really. This discussion has come up before and it starts with the 
>> simple case of timestamp the initial CREATE. This would suffice for 
>> some folks. However, it then progresses into a request for full object 
>> audit system. 
> 
> This is directly akin to Henry Ford refusing to build cars because 
> people will *someday*** want computerized fuel injection, crumple zones 
> and air bags.

No it is following this:

https://commitfest.postgresql.org/26/

and this:

https://www.postgresql.org/message-id/20191223051726.GA30778%40fetter.org

and understanding there are finite resources and that not everything is 
going to get done and that choices have to be made. Given that there are 
alternatives available I can see why this choice does not rise to the 
level of imminent action.

> 
>> I understand why there is no great desire to start down this path by 
>> the developers, they know the pressure would be on to expand the code. 
>> As Fabrízio mentions in another post this is something that could be 
>> covered in an extension. FYI, I do it by using Sqitch for my schema 
>> object creation.
>>



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 12/24/19 12:14 PM, Adrian Klaver wrote:
> On 12/24/19 8:44 AM, Ron wrote:
>> On 12/24/19 10:39 AM, Adrian Klaver wrote:
>>> On 12/23/19 6:14 PM, Ron wrote:
>>>> On 12/23/19 7:01 PM, Bruce Momjian wrote:
>
>>>>>> Is this something that has been considered for implementation?
>>>>> I wrote a blog about this:
>>>>>
>>>>> https://momjian.us/main/blogs/pgblog/2017.html#November_21_2017
>>>>
>>>> You all are *grossly* over-complicating this.
>>>
>>> Not really. This discussion has come up before and it starts with the 
>>> simple case of timestamp the initial CREATE. This would suffice for some 
>>> folks. However, it then progresses into a request for full object audit 
>>> system. 
>>
>> This is directly akin to Henry Ford refusing to build cars because people 
>> will *someday*** want computerized fuel injection, crumple zones and air 
>> bags.
>
> No it is following this:
>
> https://commitfest.postgresql.org/26/
>
> and this:
>
> https://www.postgresql.org/message-id/20191223051726.GA30778%40fetter.org
>
> and understanding there are finite resources and that not everything is 
> going to get done and that choices have to be made. Given that there are 
> alternatives available I can see why this choice does not rise to the 
> level of imminent action.

If there's not enough time and motivation for the developers to implement 
CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in 
the first place.  We're adults; we understand that OSS projects have limited 
resources, and won't go off and pout in the corner.

But that's not what y'all said.  "It's too complicated, mission creep, blah 
blah blah" just extended way too long.

>
>>
>>> I understand why there is no great desire to start down this path by the 
>>> developers, they know the pressure would be on to expand the code. As 
>>> Fabrízio mentions in another post this is something that could be 
>>> covered in an extension. FYI, I do it by using Sqitch for my schema 
>>> object creation.
>>>
>
>
>

-- 
Angular momentum makes the world go 'round.

> If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class,
thenyou should have said that in the first place.  We're adults; we understand that OSS projects have limited
resources,and won't go off and pout in the corner. 
>
> But that's not what y'all said.  "It's too complicated, mission creep, blah blah blah" just extended way too long.
>>
Is there a list of purported uses cases for these two attributes (other than auditing)?  Especially anything to do with
managingthe data as they currently exist?  


>
> --
> Angular momentum makes the world go 'round.
>
>

If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in the first place.  We're adults; we understand that OSS projects have limited resources, and won't go off and pout in the corner.

But that's not what y'all said.  "It's too complicated, mission creep, blah blah blah" just extended way too long.

Is there a list of purported uses cases for these two attributes (other than auditing)?  Especially anything to do with managing the data as they currently exist? 

 On 12/24/19 1:14 PM, Rob Sargent wrote:

If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in the first place.  We're adults; we understand that OSS projects have limited resources, and won't go off and pout in the corner.

But that's not what y'all said.  "It's too complicated, mission creep, blah blah blah" just extended way too long.

Is there a list of purported uses cases for these two attributes (other than auditing)?  Especially anything to do with managing the data as they currently exist? 

I've used last_altered for comparing tables on Staging and Prod database. 

If, for example, the last_altered on a prod table is earlier than last_altered on the staging table, then that's a strong hint that the staging and prod schema are out of sync, and more detailed examination is required. 

Another example is that -- since username is also recorded in other RDBMSs --it's useful when the customer is screaming at your boss asking who made that unauthorized modification to production that's breaking their application.  You then show them that the table hasn't been altered in X months, and point the finger back at their incompetent developers.

All in all, it's not something that you use every day, but when it is useful, it's very useful.

--

On Dec 24, 2019, at 11:48 AM, Ron <ronljohnsonjr@gmail.com> wrote:

 On 12/24/19 1:14 PM, Rob Sargent wrote:

If there's not enough time and motivation for the developers to implement CREATED_ON and LAST_ALTERED in pg_class, then you should have said that in the first place.  We're adults; we understand that OSS projects have limited resources, and won't go off and pout in the corner.

But that's not what y'all said.  "It's too complicated, mission creep, blah blah blah" just extended way too long.

Is there a list of purported uses cases for these two attributes (other than auditing)?  Especially anything to do with managing the data as they currently exist? 

I've used last_altered for comparing tables on Staging and Prod database. 

If, for example, the last_altered on a prod table is earlier than last_altered on the staging table, then that's a strong hint that the staging and prod schema are out of sync, and more detailed examination is required. 

Another example is that -- since username is also recorded in other RDBMSs --it's useful when the customer is screaming at your boss asking who made that unauthorized modification to production that's breaking their application.  You then show them that the table hasn't been altered in X months, and point the finger back at their incompetent developers.

All in all, it's not something that you use every day, but when it is useful, it's very useful.

Don’t both of those examples hi-light flaws in the release procedures?