Pivotal Knowledge Base


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


Pivotal Greenplum Database (GPDB)


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

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


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 below query (replace <tablespace_name> with concerned tablespace).

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

Cause 2:

The database in the cluster is using the tablespace has 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 default tablespace to the database, the catalog table/information of that database are stored on that location which is avoiding the tablespace to be dropped, to cross verify

  • 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
  • so the location of the files are
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
  • Navigating to the <fselocation>/<tablespace_oid>/<database_oid>
cd /data/master/gpseg-1/33731447/33731528
  • so in the example, the file count available at the location is
gpadmin:Fullrack@mdw $ ls -ltr | wc -l

NOTE: For tablespace that is a 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 (above). More of it is discussed in the article here.


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 below command.

alter table <table_name> set tablespace <tablespace_name>;

For cause 2:

As Pivotal Greenplum is building on postgres 8.2, there is no easy way to change the default tablespace, i.e the command ( below ) was not part of the postgres 8.2

alter database <database_name> set tablespace <tablespace_name>;

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


Powered by Zendesk