Pivotal Knowledge Base

Follow

Query reltablespace under pg_class return 0 (zero) for relation.

Problem

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

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.

Solution

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

Comments

Powered by Zendesk