What are different ways to find the table creation time in Greenplum?
The gadget spec URL could not be found
sachi=# create table test (like pg_stat_last_shoperation);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'classid' as the Greenplum Database data distribution key for this table.
HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
sachi=# \dt test
List of relations
Schema | Name | Type | Owner | Storage
--------+------+-------+---------+---------
public | test | table | gpadmin | heap
(1 row)
The gadget spec URL could not be found

sachi=# \d test
Table "public.test"
Column | Type | Modifiers
---------------+--------------------------+-----------
classid | oid | not null
objid | oid | not null
staactionname | name | not null
stasysid | oid | not null
stausename | name | not null
stasubtype | text |
statime | timestamp with time zone |
Distributed by: (classid)
The gadget spec URL could not be found

sachi=# select * from pg_stat_operations where actionname='CREATE' and subtype='TABLE' and objname='test' and schemaname='public';
classname | objname | objid | schemaname | usestatus | usename | actionname | subtype | statime
-----------+---------+--------+------------+-----------+---------+------------+---------+-------------------------------
pg_class | test | 117299 | public | CURRENT | gpadmin | CREATE | TABLE | 2014-12-03 12:50:58.126198-05
(1 row)


sachi=#

[gpadmin@sachi base]$ find . -name 117299
./16993/117299
[gpadmin@sachi base]$ ls -ltr ./16993/117299
-rw-------. 1 gpadmin gpadmin 0 Dec  3 12:50 ./16993/117299
[gpadmin@sachi base]$ 

Now let's alter this table and see if timestamp changes.

sachi=# alter table test add column newcol varchar(2);
ALTER TABLE
sachi=# \d test
Table "public.test"
Column | Type | Modifiers
---------------+--------------------------+-----------
classid | oid | not null
objid | oid | not null
staactionname | name | not null
stasysid | oid | not null
stausename | name | not null
stasubtype | text |
statime | timestamp with time zone |
newcol | character varying(2) |
Distributed by: (classid)
The gadget spec URL could not be found

sachi=# select * from pg_stat_operations where actionname='CREATE' and subtype='TABLE' and objname='test' and schemaname='public';
classname | objname | objid | schemaname | usestatus | usename | actionname | subtype | statime
-----------+---------+--------+------------+-----------+---------+------------+---------+-------------------------------
pg_class | test | 117299 | public | CURRENT | gpadmin | CREATE | TABLE | 2014-12-03 12:50:58.126198-05
(1 row)

sachi=#
The gadget spec URL could not be found
[gpadmin@sachi base]$ ls -ltr ./16993/117299
-rw-------. 1 gpadmin gpadmin 0 Dec  3 12:50 ./16993/117299
[gpadmin@sachi base]$ 

sachi=# select oid, datname from pg_database;
  oid  |  datname  
-------+-----------
 16992 | gpadmin
 10900 | postgres
     1 | template1
 10899 | template0
 33476 | himanshu
 16993 | sachi
 33553 | gpperfmon

(10 rows)

sachi=# 



The gadget spec URL could not be found


The gadget spec URL could not be found