Database Reference
In-Depth Information
Now, you can see the latest modification and access times for a table using the following query:
select
max(mtime) as latest_mod,
max(atime) as latest_read
from table_file_info(<schemaname>, <tablename>);
How it works...
The function
table_file_info(schemaname,
tablename
) returns creation, modification,
and access times for files used by PostgreSQL to store the table data.
The last query uses this data to get the latest time any of these files were modified or read by
PostgreSQL. This is not a very reliable way to get information about the latest
use
of any table,
but it gives you a rough upper-limit estimate about when it was last modified or read.
If you have shell access to the database host, then you can carry out the preceding steps by
hand, say in case you can't or don't want to install PL/PythonU for some reason.
You can also get the information using built-in functions
pg_ls_dir(dirname
text)
and
pg_stat_file(filename
text)
. For example, the following query:
select pg_ls_dir, (select modification from pg_stat_file(pg_ls_dir))
as modtime from pg_ls_dir('.');
lists all files and directories in PostgreSQL data directory.
There's more...
There may be last-use information in future version of PostgreSQL
There has been some discussion recently about adding last-used data to the information
PostgreSQL keeps about tables, so it is entirely possible that answering the question "When
did anybody last use this table?" will be much easier in the next version of PostgreSQL.
How much disk space is used by temporary
data?
In addition to ordinary stable tables, you can also create temporary tables.
Also, PostgreSQL may use temporary files for query processing if it can't it all the needed data
to memory.
So, how do you find out how much data is used by temporary tables and files?
