We have migrated to new Google sites!
Post date: Dec 03, 2014 6:8:45 PM
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=#