cant drop tablespace although it is empty

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

cant drop tablespace although it is empty

Mariel Cherkassky
Hey,
One of my dbs was on a dedicated tablespace and I decided to move it back to the pg_default tablespace. I moved the db to the pg_default tbs and it was successful.
Now when I'm trying to drop the tbs I'm getting the following error : 
mydb=# drop tablespace my_custom_tbs
ERROR:  tablespace "my_custom_tbs" is not empty
mydb=#

I checked what objects are inside : 
select oid,* from pg_Tablespace;
  oid  |    spcname     | spcowner | spcacl | spcoptions
-------+----------------+----------+--------+------------
  1663 | pg_default     |       10 |        |
  1664 | pg_global      |       10 |        |
 16400 | my_custom_tbs |       10 |        |
(3 rows)

select count(*) from pg_class where reltablespace=16400;
 count
-------
     0
(1 row)

I dont have any db with this tbs as its default tbs  : 

select distinct dattablespace from pg_database;
 dattablespace
---------------
          1663
(1 row)

I went to the location on disk of the tbs and saw that it contains files : 
 pwd
/var/lib/pgsql/custom_tbs/PG_9.6_201608131/16406
 ls -l | wc -l
318

Now I tried to find the objects that belong to those files, some of them are pg objects that belong to tbs 0 which is very weird and some arent belong to any object : 

select relname,relkind,reltablespace from pg_class where relfilenode=13129
;
       relname        | relkind | reltablespace
----------------------+---------+---------------
 pg_toast_13125_index | i       |             0
(1 row)

select relname,relkind,reltablespace from pg_class where relfilenode=16533;
 relname | relkind | reltablespace
---------+---------+---------------
(0 rows)

Now all those who are exist located in reltablespace 0 .

*I was working on pg9.6 .

bug ?