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

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;
>
for db in `/usr/lib/postgresql/8.2/bin/oid2name|grep -v template|tail -n
+4|awk '{print $2}'`; do   psql $db -c "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"; done
 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);
>

VALUES=`find /path/100456/ -type f -exec basename {} \;|sort -n|awk
'$_>16000 {print $1}'|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 oid, relname, relkind
FROM pg_catalog.pg_class WHERE oid IN ($VALUES)"; done

oid | relname | relkind
-----+---------+---------
(0 rows)

 oid | relname | relkind
-----+---------+---------
(0 rows)

 oid | relname | relkind
-----+---------+---------
(0 rows)

> (hoping that some of the objects are tables or views or sequences).
>
not better :-(
> Yours,
> Laurenz Albe
>

--
Cyril SCETBON