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. |
|
|