Обсуждение: get last timestamp of table ddl
Hi all, I think I already know the answer, however I came across this table in Oracle <https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352> that has two columns that triggered my attention: CREATED and LAST_DDL_TIME. Apart from being dates (in the Oracle way), they store the time of creation and last modification to the table structure. I don't have any particular use case except from blaming someone about a change in the database structure, however I'm curious: is there a smarter way to achieve this in PostgreSQL than simply relying on logs and commit timestamps? Luca
On Wed, Nov 24, 2021 at 02:53:24PM +0100, Luca Ferrari wrote: > Hi all, > I think I already know the answer, however I came across this table in > Oracle <https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352> > that has two columns that triggered my attention: CREATED and > LAST_DDL_TIME. > Apart from being dates (in the Oracle way), they store the time of > creation and last modification to the table structure. > I don't have any particular use case except from blaming someone about > a change in the database structure, however I'm curious: is there a > smarter way to achieve this in PostgreSQL than simply relying on logs > and commit timestamps? You could add event triggers to achieve similar functionality. https://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/ and https://www.postgresql.org/docs/current/sql-createeventtrigger.html depesz
On 24/11/21 3:53 μ.μ., Luca Ferrari wrote: > Hi all, > I think I already know the answer, however I came across this table in > Oracle <https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352> > that has two columns that triggered my attention: CREATED and > LAST_DDL_TIME. That would be handy .. :( > Apart from being dates (in the Oracle way), they store the time of > creation and last modification to the table structure. > I don't have any particular use case except from blaming someone about > a change in the database structure, however I'm curious: is there a > smarter way to achieve this in PostgreSQL than simply relying on logs > and commit timestamps? You mean like trying to correlate pg_class.xmin with some timestamp via track_commit_timestamp or other means? > > Luca > > -- Achilleas Mantzios DBA, Analyst, IT Lead IT DEPT Dynacom Tankers Mgmt
On Wed, Nov 24, 2021 at 3:09 PM hubert depesz lubaczewski <depesz@depesz.com> wrote: > You could add event triggers to achieve similar functionality. > https://www.depesz.com/2012/07/29/waiting-for-9-3-event-triggers/ > and > https://www.postgresql.org/docs/current/sql-createeventtrigger.html Thanks, I was ware of event triggers, and of course it is pretty much the same approach I see (quite often) in "userland" to track (audit) changes in data: I mean a table trigger that logs some data change. However, I was curious about already available ways to do that. Luca
On Wed, Nov 24, 2021 at 3:30 PM Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > You mean like trying to correlate pg_class.xmin with some timestamp via track_commit_timestamp or other means? Pretty much yes: since pg_class and pg_attribute comes to my mind. Luca