We have migrated to new Google sites!
Post date: Dec 21, 2012 12:34:17 PM
Table creation time can be obtained from pg_stat_last_operation
select statime from pg_stat_last_operation where classid = 'pg_class'::regclass and objid = 'schemaname.tablename'::regclass and staactionname = 'CREATE';
When did anybody last use it?
The following snippet from pg_cookbook..
CREATE LANGUAGE plpythonu;
CREATE TYPE fileinfo AS (
filename text,
filesize bigint,
ctime abstime,
mtime abstime,
atime abstime
);
CREATE OR REPLACE FUNCTION table_file_info(schemaname text, tablename
text)
RETURNS SETOF fileinfo
AS $
import datetime, glob, os
db_info = plpy.execute("""
select datname as database_name,
current_setting('data_directory') || '/base/' || db.oid as
data_directory
from pg_database db
where datname = current_database()
""")
#return db_info[0]['data_directory']
table_info_plan = plpy.prepare("""
select nspname as schemaname,
relname as tablename,
relfilenode as filename
from pg_class c
join pg_namespace ns on c.relnamespace=ns.oid
where nspname = $1
and relname = $2;
""", ['text', 'text'])
table_info = plpy.execute(table_info_plan, [schemaname, tablename])
filemask = '%s/%s*' % (db_info[0]['data_directory'], table_info[0]
['filename'])
res = []
for filename in glob.glob(filemask):
fstat = os.stat(filename)
res.append((
filename,
fstat.st_size,
datetime.datetime.fromtimestamp(fstat.st_ctime).isoformat(),
datetime.datetime.fromtimestamp(fstat.st_mtime).isoformat(),
datetime.datetime.fromtimestamp(fstat.st_atime).isoformat()
))
return res
$ LANGUAGE plpythonu;
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.