Finding table creation time in Greenplum

posted Apr 28, 2017, 9:59 AM by Sachchida Ojha
What are different ways to find the table creation time in Greenplum?
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)

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)

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)

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]$ 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=# 



Comments