Date created for tables

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Date created for tables

Chloe Dives

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?

 

Thanks in advance,

Chloe

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Tom Lane-2
Chloe Dives <[hidden email]> writes:
> 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?

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


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Justin-2
In reply to this post by Chloe Dives
Hi Tom 

can't we get access to this information in a backwards way by using pg_xact_commit_timestamp()  then query the system catalog tables xmin entry for the relevant object???

this requires turning on pg_xact_commit_timestamp  https://www.postgresql.org/docs/current/runtime-config-replication.html

will not show the creation date as it will be lost after an update and vacuum ...





On Thu, Dec 5, 2019 at 12:10 PM Chloe Dives <[hidden email]> 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?

 

Thanks in advance,

Chloe

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Adrian Klaver-4
In reply to this post by Chloe Dives
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Ron-2
In reply to this post by Tom Lane-2


On 12/5/19 1:01 PM, Tom Lane wrote:
> Chloe Dives <[hidden email]> writes:
>> 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?
> 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.


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Michael Paquier-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

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


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Bruce Momjian
In reply to this post by Chloe Dives
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  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

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

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.
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Melvin Davidson-5
>You all are grossly over-complicating this.
Agree +1


On Mon, Dec 23, 2019 at 9:14 PM Ron <[hidden email]> 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.

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.


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Thomas Kellerer
In reply to this post by Ron-2
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) or DML 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 be enough.




Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Fabrízio de Royes Mello-2
In reply to this post by Ron-2

Em seg., 23 de dez. de 2019 às 23:14, Ron <[hidden email]> escreveu:
>
> You all are grossly over-complicating this.
>

Maybe we are really very conservative, but everyone needs to understand that every single piece of code added to core is our responsibility to maintain and make sure don't break the whole thing.

I know it is a desired feature but on the other hand we put a lot of effort to make PostgreSQL very extensible, so IMHO why don't put effort to create an extension to implement this feature instead of trying repeatedly to get it into the core without any success.

Using EventTriggers is very easy to get a very first version tracking local objects and if we need to add shared objects (databases, roles, tablespaces) we can use hooks and some piece of C code to do the job.

Regards,

--
   Fabrízio de Royes Mello         Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Adrian Klaver-4
In reply to this post by Ron-2
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Ron-2
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:
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.

This is directly akin to Henry Ford refusing to build cars because people will someday want computerized fuel injection, crumple zones and air bags.

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.



--
Angular momentum makes the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

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


lup
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

lup


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


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


Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

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

--
Angular momentum makes the world go 'round.
lup
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

lup


On Dec 24, 2019, at 11:48 AM, Ron <[hidden email]> 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? 

Angular momentum the world go 'round.
Reply | Threaded
Open this post in threaded view
|

Re: Date created for tables

Ron-2
On 12/24/19 8:58 PM, Rob Sargent wrote:

On Dec 24, 2019, at 11:48 AM, Ron [hidden email] 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?

And bug highlight flaws in the development process.  We're human, after all.

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