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=#
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)
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)
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)
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
/var/lib/pgsql/custom_tbs/PG_9.6_201608131/16406
ls -l | wc -l
318
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)
;
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)
relname | relkind | reltablespace
---------+---------+---------------
(0 rows)
Now all those who are exist located in reltablespace 0 .
*I was working on pg9.6 .
bug ?