Tip- Check the Operation and Modification (DDL's) Executed on a Relation

Products

• VMware Tanzu Greenplum

Issue/Introduction

Sometimes there is a need to know what all DDLs action has been performed on a relation. It can happen when you want to know when was the table last analyzed, altered or vacuumed etc.

Resolution

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

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions