Pivotal Knowledge Base

Follow

Tip - Check the operation/Modification (DDL's) executed on a relation

Goal

Sometimes we need to know what all DDLs action has been performed on a relation. It could be the case when you want to know when was the table last Analyzed/Altered or Vacuum etc.

Solution

The view"pg_stat_last_operation" catalog table holds all the DDLs executed on all the relations for a specific database. This table is not shared so you have to be in the same database where the relation exists.

The below query can help you to witness the same

select
    objid::regclass as relation,
    staactionname As Command,
    Statime as "Time when executed"  
from
    pg_stat_last_operation
where
    objid='<Relation_name>'::regclass;

where Relation_name is the name of the table you want to search. 

Example:

gpadmin=# select
gpadmin-#     objid::regclass as relation, 
gpadmin-#     staactionname As Command, 
gpadmin-#     Statime as "Time when executed"  
gpadmin-# from 
gpadmin-#     pg_stat_last_operation 
gpadmin-# where 
gpadmin-#     objid='test'::regclass;
relation | command |Time when executed
----------+---------+-------------------------------
test     | CREATE  | 2014-04-25 03:19:53.265247-04
test     | ALTER   | 2014-04-25 03:20:06.286818-04
test     | VACUUM  | 2014-04-25 05:43:26.48119-04
test     | ANALYZE | 2014-04-25 05:43:32.560722-04

Comments

Powered by Zendesk