Finding table creation time in Greenplum

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';

-----------+---------+--------+------------+-----------+---------+------------+---------+-------------------------------

(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';

-----------+---------+--------+------------+-----------+---------+------------+---------+-------------------------------

(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=#