Re: Error when trying to drop a tablespace - Mailing list pgsql-general

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.

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