Pivotal Knowledge Base


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


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.


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

    objid::regclass as relation,
    staactionname As Command,
    Statime as "Time when executed"  

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


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


Powered by Zendesk