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 :
>>>>>>
>>>>> You can find the dependent objects with:
>>>>>
>>>>> 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;
>>>>>
>>>>>
>>>> postgres=# SELECT t.relname AS catalog, d.objid AS oid_dependent
>>>> postgres-# FROM pg_catalog.pg_class t JOIN
>>>> postgres-# pg_catalog.pg_depend d ON (t.oid = d.classid)
>>>> postgres-# WHERE refobjid = 100456;
>>>> catalog | oid_dependent
>>>> ---------+---------------
>>>> (0 rows)
>>>>
>>>> nothing...
>>>>
>>> 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
>>
> [...]
>
> Good! that's a starting point!
>
> 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.
>
> Can you find tables or other objects with OID 100738, 102333 etc. in this
> or other databases?
>
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

count
-------
0
(1 row)

count
-------
0
(1 row)

count
-------
0
(1 row)

nothing :-(

> Yours,
> Laurenz Albe
>

--
Cyril SCETBON - Ingénieur bases de données
AUSY pour France Télécom - OPF/PORTAILS/DOP/HEBEX

Tél : +33 (0)4 97 12 87 60
Jabber : cscetbon(at)jabber(dot)org
France Telecom - Orange
790 Avenue du Docteur Maurice Donat
Bâtiment Marco Polo C2 - Bureau 202
06250 Mougins
France

***********************************
Ce message et toutes les pieces jointes (ci-apres le 'message') sont
confidentiels et etablis a l'intention exclusive de ses destinataires.
Toute utilisation ou diffusion non autorisee est interdite.
Tout message electronique est susceptible d'alteration. Le Groupe France
Telecom decline toute responsabilite au titre de ce message s'il a ete
altere, deforme ou falsifie.
Si vous n'etes pas destinataire de ce message, merci de le detruire
immediatement et d'avertir l'expediteur.
***********************************
This message and any attachments (the 'message') are confidential and
intended solely for the addressees.
Any unauthorised use or dissemination is prohibited.
Messages are susceptible to alteration. France Telecom Group shall not be
liable for the message if altered, changed or falsified.
If you are not recipient of this message, please cancel it immediately and
inform the sender.
************************************