Re: [GENERAL] Error when trying to drop a tablespace

Cyril SCETBON wrote:
>>>>> I get the following error :
>>>>>
>>>>> postgres=# DROP TABLESPACE IF EXISTS my_tbs;
>>>>> ERROR: tablespace "my_tbs" is not empty
>>>>>
>>>>> I've searched in pg_class and I'm not able to find a relation which
>>>>> refers to my_tbs with :
>>>>
>>>> You can find the dependent objects with:
>>>>
>>>> SELECT t.relname AS catalog, d.objid AS oid_dependent
>>>> FROM pg_catalog.pg_class t JOIN
>>>> pg_catalog.pg_depend d ON (t.oid = d.classid)
>>>> WHERE refobjid = 100456;
>>>>
>>> postgres=# SELECT t.relname AS catalog, d.objid AS oid_dependent
>>> postgres-# FROM pg_catalog.pg_class t JOIN
>>> postgres-# pg_catalog.pg_depend d ON (t.oid = d.classid)
>>> postgres-# WHERE refobjid = 100456;
>>> catalog | oid_dependent
>>> ---------+---------------
>>> (0 rows)
>>>
>>> nothing...
>>
>> Find out the directory:
>>
>> SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';
>>
>> is there anything in this directory?
>
> cd spclocation
>
> find .
> .
> ./100456
> ./100456/100738
> ./100456/102333
> ./100456/103442
[...]

Good! that's a starting point!

A tablespace does not belong to a specific database, so it could be that
these objects belong to another database that also uses this tablespace.

Try to look for objects that depend on the tablespace in other databases.
I also forgot pg_shdepend which contains relationships to shared objects
such as roles - look there too.

Can you find tables or other objects with OID 100738, 102333 etc. in this
or other databases?

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general