Re: Error when trying to drop a tablespace

Albe Laurenz wrote:
> 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;
>>>
>> catalog | oid_dependent
>> ---------+---------------
>> (0 rows)
>>
>> catalog | oid_dependent
>> ---------+---------------
>> (0 rows)
>>
>> catalog | oid_dependent
>> ---------+---------------
>> (0 rows)
>>
>>
>>> You could also try the following in all databases:
>>>
>>> SELECT oid, relname, relkind FROM pg_catalog.pg_class
>>> WHERE oid IN (100738, 102333, 103442);
>>>
>>>
>> oid | relname | relkind
>> -----+---------+---------
>> (0 rows)
>>
>> oid | relname | relkind
>> -----+---------+---------
>> (0 rows)
>>
>> oid | relname | relkind
>> -----+---------+---------
>> (0 rows)
>>
>
> I'm at the end of my wits.
> If there is nothing in pg_depends and pg_shdepends referring to that
> tablespace, I don't know what the files in the tablespace directory could be.
>
> Maybe somebody else has an idea.
>
> Could it be that they are garbage left behind by - e.g. - a database restore?
>
> Were any of them used recently (file access times)?
>
> Ah, there is another, rather tedious thing you could try:
> - Take a pg_dumpall of the cluster
> - Install PostgreSQL on a second machine and create the tablespace directories
> (same path as on the original machine).
> - Restore the dump there and see if any objects get created in the directories.
>
I did pg_dumpall but not yet restored it
> Oh, one more question: What is the absolute path of the tablespace directory?
> It is not a subdirectory of the cluster directory, is it?
>
> Yours,
> Laurenz Albe
>

--
Cyril SCETBON