Re: Finding out about the dates of table modification - Mailing list pgsql-general

> On 23 Nov 2019, at 3:24, Martin Mueller <martinmueller@northwestern.edu> wrote:
>
> I've moved happily from MySQL to Postgres but miss one really good feature of MYSQL: the table of tables that let you
useSQL queries to find out metadata about your table. Thus looking at the table of tables and sorting it by last
change,lets you quickly look at the most recently modified table. Which is really useful if you have a bad memory, as I
do,and can't remember the name of a particular table that I worked on last Wednesday.  
>
> Are those capabilities hidden somewhere in Postgres?  There isn't an obvious section in the documentation. At least I
can'tfind it.  

AFAIK, there’s nothing like that built-in, but it’s not impossible to deduce.

You could start with getting a list of files in $PG_DATA/base that were modified in that period (provided you have
sufficientprivileges on that directory): 

    find base/ -mtime -2 -type f -print

For figuring out to what tables these files belong [1]:

    pg_filenode_relation(0, <filename>);

and:

    pg_relation_filepath(<table_name>);

For example, I did:
# create table creation_date(test text);

[/home/postgres/10/data]$ find base/ -mtime -2 -type f -print
base/16403/2608
base/16403/29784
base/16403/2659
base/16403/29789
base/16403/2678
base/16403/29787
base/16403/2662
base/16403/2703
base/16403/2679
base/16403/2673
base/16403/2658
base/16403/1249
base/16403/2610
base/16403/2704
base/16403/2674
base/16403/3455
base/16403/2663
base/16403/1247
base/16403/1259

The lower numbers are probably core tables, such as pg_depend:
# SELECT pg_filenode_relation(0, 2608); -- 0 being the default table-space
 pg_filenode_relation
----------------------
 pg_depend
(1 row)

But!:
# SELECT pg_filenode_relation(0, 29784);
 pg_filenode_relation
----------------------
 creation_date
(1 row)


And indeed:
# select pg_relation_filepath('creation_date');
 pg_relation_filepath
----------------------
 base/16403/29784
(1 row)


I was looking for the inverse function pg_filepath_relation(<filepath>), but that does not appear to exist; That would
havebeen useful in combination with file listings like those from `find`. 

Mind that larger tables consist of multiple files. I’m sure this would become a head-ache quick on a larger database.
Havingan actual creation-date of a file would be nice too, but that doesn’t necessarily mean much when growing tables
createextra files too. 

Apparently, someone already turned the process into a number of queries[2]. As they mention though, it’s not 100%
reliablethough, as there are operations that recreate table files, such as CLUSTER. 

Then again, if you’re just looking for the table you created last Wednesday, that’s probably not a major concern.


Another option is to add a DDL Event trigger on create table statements and log that to some table[3].

Regards,
Alban Hertroys

[1]: https://www.2ndquadrant.com/en/blog/postgresql-filename-to-table/
[2]:
https://stackoverflow.com/questions/18849756/automatically-drop-tables-and-indexes-older-than-90-days/18852752#18852752
[3]: https://www.postgresql.org/docs/current/event-triggers.html

--
There is always an exception to always.