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
