> On 23 Nov 2019, at 3:24, Martin Mueller <martinmuel...@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 use SQL 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't find 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 sufficient privileges 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 have been 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. Having an actual creation-date of a file
would be nice too, but that doesn’t necessarily mean much when growing tables
create extra files too.
Apparently, someone already turned the process into a number of queries[2]. As
they mention though, it’s not 100% reliable though, 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.
