Skip to content
By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post
About re:Post

Missing creation time information in PG_CLASS_INFO

0

Hello, I was wondering if there was any known reason why there would be creation time information missing from PG_CLASS_INFO. There doesn't seem to be anything special about the missing tables in particular (they are not external tables, and they are a combination of base tables/views). Any insight would be appreciated.

Topics
Analytics
Tags
Amazon Redshift
Language
English

asked 3 years ago819 views

1 Answer
1

In my testing, I found that objects in pg_catalog, pg_toast, and information_schema do not have a creation time set. I also see that for views, there isn't a creation time set. Lastly, primary key constraints don't have a creation time. All other objects do have a creation time set.

select n.nspname, c.relkind, count(distinct c.relname) 
from pg_class_info c 
join pg_namespace n on c.relnamespace = n.oid 
where c.relcreationtime is null 
group by n.nspname, c.relkind 
order by 1, 2;

v = view

r = ordinary table

i = index

s = sequence (only in pg_catalog)

t = toast table (only in pg_toast)

In my test database, I have the following object counts with null creation time.

      nspname       | relkind | count 
--------------------+---------+-------
 admin              | v       |     1
 information_schema | r       |     6
 information_schema | v       |    34
 pg_catalog         | i       |   131
 pg_catalog         | r       |  1155
 pg_catalog         | s       |     1
 pg_catalog         | v       |   656
 pg_toast           | i       |    54
 pg_toast           | t       |    54
 public             | i       |     7
 public             | v       |     7
 tpcds              | i       |    23
 tpcds              | v       |     4
(13 rows)
AWS
EXPERT

answered 3 years ago

  • rePost-User-7659471
    3 years ago

    I ran the query you provided above with an additional filter for ordinary tables since that's primarily what I'm concerned with

    select n.nspname, c.relkind, count(distinct c.relname) 
from pg_class_info c 
join pg_namespace n on c.relnamespace = n.oid 
where c.relcreationtime is null 
and c.relkind = 'r'
group by n.nspname, c.relkind 
order by 1, 2;

    And am getting this output, and there are some tables in the prod/google_analytics schemas do not have creation time.

    +-------------------------+---------+-------+
| nspname                 | relkind | count |
+-------------------------+---------+-------+
| information_schema      | r       | 6     |
| pg_catalog                      | r       | 1155  |
| public                               | r       | 1     |
| prod                                 | r       | 10    |
| google_analytics            | r       | 4     |
+-------------------------+---------+-------+

    By chance do you happen to know when Redshift started supporting PG_CLASS_INFO? Is it possible these tables are missing the creation time because they were created before PG_CLASS_INFO was established?

  • Jon Roberts EXPERT
    3 years ago

    Can you examine the tables in "prod" without a creation time? I'm wondering if these are the tables that back a materialized view or maybe a table with an interleaved sort key.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Relevant content