Re: Error when trying to drop a tablespace

Hi guys,

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 :

postgres=# select * from pg_class where reltablespace=100456;
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers
| relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules
| relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
(0 rows)

100456 has been found with :

/oid2name -s|grep my_tbs

Any idea ?
--
Cyril SCETBON

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 :
>
> postgres=# select * from pg_class where reltablespace=100456;
[...]
> (0 rows)
>
> 100456 has been found with :
>
> /oid2name -s|grep my_tbs
>
> Any idea ?

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;

This will give you a list of system catalog tables and OIDs.
You can find out more about an dependent object by searching
for the OID in the system catalog.

Yours,
Laurenz Albe

Cyril SCETBON <scetbon(at)echo(dot)fr> writes:
> 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 :

Maybe there's something in a different database?

I'd just look into the filesystem and see what's underneath the
tablespace directory ...

regards, tom lane

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 :
>>>
>>> postgres=# select * from pg_class where reltablespace=100456;
>>>
>> [...]
>>
>>> (0 rows)
>>>
>>> 100456 has been found with :
>>>
>>> /oid2name -s|grep my_tbs
>>>
>>> Any idea ?
>>
>> 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...

Hmm.
Find out the directory:

SELECT oid, spclocation FROM pg_catalog.pg_tablespace WHERE spcname = 'my_tbs';

is there anything in this directory?

Yours,
Laurenz Albe

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 :
>>>>
>>>> postgres=# select * from pg_class where reltablespace=100456;
>>>>
>>>>
>>> [...]
>>>
>>>
>>>> (0 rows)
>>>>
>>>> 100456 has been found with :
>>>>
>>>> /oid2name -s|grep my_tbs
>>>>
>>>> Any idea ?
>>>>
>>> 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...
>>
>
> Hmm.
> 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
./100456/102618
./100456/104159
./100456/101234
./100456/102658
./100456/104477
./100456/101031
./100456/10746
./100456/102680
./100456/103344
./100456/100711
./100456/103519
./100456/102154
./100456/103111
./100456/102613
./100456/104210
./100456/103474
./100456/103784
./100456/103597
./100456/103173
./100456/103160
./100456/100962
./100456/100938
./100456/101375
./100456/103871
./100456/101410
./100456/102151
./100456/104910
./100456/103133
./100456/101778
./100456/102712
./100456/100586
./100456/103466
./100456/101976
./100456/103789
./100456/100911
./100456/103680
./100456/101605
./100456/101858
./100456/101840
./100456/102352
./100456/102047
./100456/104272
./100456/101949
./100456/104907
./100456/102517
./100456/103775
./100456/104527
./100456/102085
./100456/101490
./100456/103333
./100456/102592
./100456/103970
./100456/104549
./100456/101839
./100456/104175
./100456/101024
./100456/104072
./100456/101914
./100456/103677
./100456/100944
./100456/101160
./100456/101135
./100456/102296
./100456/2663
./100456/101818
./100456/104434
./100456/101928
./100456/103469
./100456/100719
./100456/101383
./100456/1259
./100456/102015
./100456/103503
./100456/100650
./100456/103255
./100456/100746
./100456/100616
./100456/2602
./100456/102479
./100456/101776
./100456/102549
./100456/101485
./100456/103559
./100456/102607
./100456/101880
./100456/102090
./100456/101061
./100456/102903
./100456/104365
./100456/103373
./100456/103584
./100456/101565
./100456/101389
./100456/102527
./100456/103888
./100456/101231
./100456/2601
./100456/103802
./100456/102519
./100456/101317
./100456/102504
./100456/104967
./100456/102423
./100456/102224
./100456/102495
./100456/103194
./100456/104931
./100456/103885
./100456/102637
./100456/102480
./100456/103552
./100456/104383
./100456/101720
./100456/103039
./100456/101397
./100456/102176
./100456/102482
./100456/101648
./100456/102552
./100456/103757
./100456/103152
./100456/104893
./100456/104037
./100456/103810
./100456/104501
./100456/104896
./100456/2608
./100456/101309
./100456/102869
./100456/101816
./100456/101127

....
> 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.
************************************

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?

Yours,
Laurenz Albe

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.
************************************

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

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

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

Cyril SCETBON <scetbon(at)echo(dot)fr> writes:
> Albe Laurenz wrote:
>> is there anything in this directory?

> find .
> .
> ./100456
> ./100456/100738
> ./100456/102333
> ./100456/103442
> ./100456/102618
> ./100456/104159
> ./100456/101234
> ./100456/102658
> ./100456/104477

So which database has OID 100456?
select datname from pg_database where oid = 100456;

> ./100456/1259

Since the output lists some system catalogs, I'm betting this tablespace
is actually the default for that database.

regards, tom lane

Tom Lane wrote:
> Cyril SCETBON <scetbon(at)echo(dot)fr> writes:
>
>> Albe Laurenz wrote:
>>
>>> is there anything in this directory?
>>>
>
>
>> find .
>> .
>> ./100456
>> ./100456/100738
>> ./100456/102333
>> ./100456/103442
>> ./100456/102618
>> ./100456/104159
>> ./100456/101234
>> ./100456/102658
>> ./100456/104477
>>
>
> So which database has OID 100456?
> select datname from pg_database where oid = 100456;
>
it's not a database oid but a tablespace oid
>
>> ./100456/1259
>>
>
> Since the output lists some system catalogs, I'm betting this tablespace
> is actually the default for that database.
>
> regards, tom lane
>
>

--
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.
************************************

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

Cyril SCETBON <scetbon(at)echo(dot)fr> writes:
> Tom Lane wrote:
>> So which database has OID 100456?
>> select datname from pg_database where oid = 100456;
>>
> it's not a database oid but a tablespace oid

[ squint... ] There shouldn't be any files directly under a tablespace
directory, except possibly a PG_VERSION marker file. What should be
there are per-database directories, and those are what contain the
files. So that's why I assumed 100456 must be a database directory.
You sure it isn't?

regards, tom lane

Tom Lane wrote:
> Cyril SCETBON <scetbon(at)echo(dot)fr> writes:
>
>> Tom Lane wrote:
>>
>>> So which database has OID 100456?
>>> select datname from pg_database where oid = 100456;
>>>
>>>
>> it's not a database oid but a tablespace oid
>>
>
> [ squint... ] There shouldn't be any files directly under a tablespace
> directory, except possibly a PG_VERSION marker file. What should be
> there are per-database directories, and those are what contain the
> files. So that's why I assumed 100456 must be a database directory.
>
under the tablespace directory I got :

PG_VERSION file and oidtbs directory
> You sure it isn't?
>
sure
> regards, tom lane
>

--
Cyril SCETBON