Pivotal Knowledge Base

Follow

How to get the Creation Date of a Table

Environment

 Product  Version
 Pivotal Greenplum  4.3.x
 OS  RHEL 6.x

Purpose

This article describes how to get the creation date of a table.

Procedure

Follow the steps to find the creation date of a table:

1. Get the OID of the table from pg_class

template1=# select oid,* from pg_class where relname='test';
oid | relname | relnamespace | reltype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relaosegrelid | relaosegidxid
| relhasindex | relisshared | relkind | relstorage | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass
| relfrozenxid | relacl | reloptions
-------+---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+---------------+--------------
-+-------------+-------------+---------+------------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+---------------
-+--------------+--------+------------
42187 | test | 2200 | 42188 | 10 | 0 | 42187 | 0 | 2 | 1002 | 42224 | 0 | 0 | 0
| f | f | r | h | 2 | 0 | 0 | 0 | 0 | 0 | f | f | f | f
| 1123863 | |
(1 row)

2. Get the creation date from pg_stat_last_operation

template1=# select * from pg_stat_last_operation where objid=42187;
classid | objid | staactionname | stasysid | stausename | stasubtype | statime
---------+-------+---------------+----------+------------+------------+-------------------------------
1259 | 42187 | CREATE | 10 | gpadmin | TABLE | 2016-10-13 04:28:06.213131+08
1259 | 42187 | ANALYZE | 10 | gpadmin | | 2016-10-13 04:32:13.200262+08
1259 | 42187 | ALTER | 10 | gpadmin | ADD COLUMN | 2016-10-27 01:48:19.849112+08
1259 | 42187 | VACUUM | 10 | gpadmin | | 2016-11-09 00:30:54.177818+08
(4 rows) 

Additional Information 

Here's the action which will be recorded in pg_stat_last_operation.

template1=# select distinct staactionname from pg_stat_last_operation;
staactionname
---------------
ALTER
PRIVILEGE
ANALYZE
PARTITION
CREATE
VACUUM

 

 

 

Comments

Powered by Zendesk