Thread: 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@echo.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
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@jabber.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 : >>>> >>>> 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@jabber.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
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@echo.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
Cyril SCETBON <scetbon@echo.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@echo.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@jabber.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. ************************************
Tom Lane wrote: > Cyril SCETBON <scetbon@echo.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
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
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