Query "reltablespace" under "pg_class" Returns "0" (zero) for Relation

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Querying the pg_class for the tablespace information for a table returns 0.

g4=# select relname,reltablespace from pg_class where relname='a1';
 relname | reltablespace 
---------+---------------
 a1      |             0
(1 row)

Time: 1.327 ms

Environment

Cause

This is as per the design, when you create the table under the default tablespace on which the database is created you will be returned with the value 0, so you can use the steps on the solution section to find the tablespace that the object has been created.

Resolution

In order to check the tablespace where the object has been created, follow the below steps.

• Create the table

  g4=# create table a1 as select * from pg_class;
  NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'relname' 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.
  SELECT 385
  Time: 2032.320 ms

• The tablespace information on pg_class is

  g4=# select relname,reltablespace from pg_class where relname='a1';
   relname | reltablespace 
  ---------+---------------
   a1      |             0
  (1 row)
  
  Time: 1.327 ms

• The default tablespace of the database is

  g4=# select datname,dattablespace from pg_database where datname='g4';
   datname | dattablespace 
  ---------+---------------
   g4      |          1663
  (1 row)
  
  Time: 1.418 ms

• So, the table has been created on the tablespace pg_default ( as per the above example.)

  g4=# select oid,spcname from pg_tablespace where oid=1663;
   oid  |  spcname   
  ------+------------
   1663 | pg_default
  (1 row)
  Time: 1.160 ms