Re: 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 :
>>>>
>>>> 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
>> [...]
>>
>> 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.
>
> VALUES=`find /path/100456/ -type f -exec basename {} \;|xargs|sed -e 's/ /,/g'`
> for db in `/usr/lib/postgresql/8.2/bin/oid2name|grep -v template|tail -n
> +4|awk '{print $2}'`; do psql $db -c "select count(*) from pg_shdepend
> where objid in ($VALUES) or refobjid in ($VALUES)"; done
>
> nothing :-(

Did you also look in pg_depend in all the other databases?
You could run the following in all databases:

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;

You could also try the following in all databases:

SELECT oid, relname, relkind FROM pg_catalog.pg_class
WHERE oid IN (100738, 102333, 103442);

(hoping that some of the objects are tables or views or sequences).

Yours,
Laurenz Albe