Error when trying to drop a tablespace

Started by Cyril SCETBONalmost 18 years ago15 messagesgeneral

Jump to latest

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

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 :

100456 has been found with :

/oid2name -s|grep my_tbs

Any idea ?
--
Cyril SCETBON

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Cyril SCETBON (#1)

Re: Error when trying to drop a tablespace

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

Tom Lane

tgl@sss.pgh.pa.us

almost 18 years ago

In reply to: Cyril SCETBON (#1)

Re: Error when trying to drop a tablespace

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

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Cyril SCETBON (#1)

Re: Error when trying to drop a tablespace

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

Import Notes

Reply to msg id not found: 4857C4EE.6070607@echo.fr

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

In reply to: Laurenz Albe (#4)

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 :

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

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Cyril SCETBON (#5)

Re: Error when trying to drop a tablespace

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

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

In reply to: Laurenz Albe (#6)

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)

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

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Cyril SCETBON (#7)

Re: Error when trying to drop a tablespace

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

scetbon@echo.fr

almost 18 years ago

In reply to: Laurenz Albe (#8)

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 :

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)

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)

(hoping that some of the objects are tables or views or sequences).

not better :-(

Yours,
Laurenz Albe

--
Cyril SCETBON

#10

Laurenz Albe

laurenz.albe@cybertec.at

almost 18 years ago

In reply to: Cyril SCETBON (#9)

Re: Error when trying to drop a tablespace

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

#11

Tom Lane

tgl@sss.pgh.pa.us

almost 18 years ago

In reply to: Cyril SCETBON (#5)

Re: Error when trying to drop a tablespace

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

#12

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

In reply to: Tom Lane (#11)

Re: Error when trying to drop a tablespace

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

#13

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

In reply to: Laurenz Albe (#10)

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 :

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

#14

Tom Lane

tgl@sss.pgh.pa.us

almost 18 years ago

In reply to: Cyril SCETBON (#12)

Re: Error when trying to drop a tablespace

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

#15

Cyril SCETBON

scetbon@echo.fr

almost 18 years ago

In reply to: Tom Lane (#14)

Re: Error when trying to drop a tablespace

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