Search Postgresql Archives

Re: Error when trying to drop a tablespace

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux