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

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 <Chloe.Dives@cantabcapital.com>
wrote:

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

/messages/by-id/ABA5562F-56A9-4AB1-95D6-398215015DBD@gmail.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 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.

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

You all are *grossly* over-complicating this.

Agree +1

On Mon, Dec 23, 2019 at 9:14 PM Ron <ronljohnsonjr@gmail.com> 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!

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.

Em seg., 23 de dez. de 2019 às 23:14, Ron <ronljohnsonjr@gmail.com>
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

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

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:

/messages/by-id/20191223051726.GA30778@fetter.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:

/messages/by-id/20191223051726.GA30778@fetter.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, 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.

--
Angular momentum makes the world go 'round.

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?

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

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?

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

--
Angular momentum makes the world go 'round.