How to find table creation/access time in Greenplum

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.