cant drop tablespace although it is empty

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: cant drop tablespace although it is empty
Date: 2020-03-24 12:38:33
Message-ID: CA+t6e1nmtH88yuhRRRCVCAJw4KatJexDK=4vZiJobK6i5UokVw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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 ?

Browse pgsql-admin by date

  From Date Subject
Next Message Douglas Reed 2020-03-24 16:24:07 Problem with replication
Previous Message Michael Paquier 2020-03-24 06:12:38 Re: PG12 autovac issues