Dropping Tablespace Error: "Tablespace "xxxx" is not Empty"

Products

• VMware Tanzu Greenplum

Issue/Introduction

Symptoms:

Attempting to drop a tablespace ends up with the error message:

gpadmin=# drop tablespace ts3;
ERROR:  tablespace "ts3" is not empty 

 

Cause

There are two causes for why the tablespace cannot be dropped.

Cause 1:

Tablespace has an object from some other database. To verify if there is an object in the tablespace use the query below and replace <tablespace_name> with the tablespace in question.

psql -d template1 -t -c "select datname from pg_database where datname!='template0'" | while read x
do 
echo --- $x ---   
psql -d $x -c "select count(*) from pg_class where reltablespace in ( select oid from pg_tablespace where spcname='<tablespace_name>')"
done 

Cause 2:

The database in the cluster which is using the tablespace has a default tablespace.

psql -d template1 -c "select datname from pg_database where datname!='template0' and dattablespace in ( select oid from pg_tablespace where spcname='<tablespace_name>')" 

When you have the tablespace that you attempting to drop as a default tablespace to the database, the catalog table and information for that database are stored in that location. This does not allow tablespace to be dropped. To cross verify this, follow the steps below:

• The tablespace "ts3" oid is

  gpadmin=# select oid,spcname,spcfsoid from pg_tablespace;
     oid    |  spcname   | spcfsoid 
  ----------+------------+----------
       1663 | pg_default |     3052
       1664 | pg_global  |     3052
   33730629 | fs10       | 33730626
   33731447 | ts3        | 33730626
  (4 rows)
  
  Time: 3.064 ms 
  

• The database with default tablespace ts3

  gpadmin=# Select oid,datname,dattablespace from pg_database where dattablespace=33731447;
     oid    | datname | dattablespace 
  ----------+---------+---------------
   33731528 | testdb  |      33731447
  (1 row)
  
  Time: 2.443 ms 
  

• The location of the files are in the following log:

  gpadmin=# select * from pg_filespace_entry where fsefsoid=33730626;
   fsefsoid | fsedbid |      fselocation       
  ----------+---------+------------------------
   33730626 |       1 | /data/master/gpseg-1
   33730626 |       2 | /data1/primary/gpseg0
   33730626 |       3 | /data1/primary/gpseg1
   33730626 |       4 | /data1/primary/gpseg2
   33730626 |       5 | /data1/primary/gpseg3
   33730626 |       6 | /data1/primary/gpseg4 
  

• Navigate to the <fselocation>/<tablespace_oid>/<database_oid>

  cd /data/master/gpseg-1/33731447/33731528 
  

• In this example, the file count available at the location is:

  gpadmin:Fullrack@mdw $ ls -ltr | wc -l
  169 
  

Note: For a tablespace that is default to the database, the reltablespace value in pg_class is 0 (zero), which is the reason you are not able to see the object with the query as discussed in cause 1. More information can be found here.

 

Resolution

For cause 1:

If there is an object in the tablespace that you are attempting to drop, please move to a new location using the command below:

alter table <table_name> set tablespace <tablespace_name>; 

For cause 2:

Pivotal Greenplum is builds on postgres 8.2 so there is no easy way to change the default tablespace. The command below was not part of the postgres 8.2.

alter database <database_name> set tablespace <tablespace_name>; 

In order to change the default tablespace, you will need to follow the steps mentioned in the article here.