Although DROP TABLESPACE can detect tables existing in the target
tablespace, it doesn't have any way to detect schemas that reference
that tablespace as their default tablespace. Thus you can get
implementation-level failures like this one:
$ mkdir /tmp/junk
regression=# create tablespace junk location '/tmp/junk';
CREATE TABLESPACE
regression=# create schema junk tablespace junk;
CREATE SCHEMA
regression=# drop tablespace junk;
DROP TABLESPACE
regression=# create table junk.foo(f1 text);
ERROR: could not create directory "/u/pg_data/pg_tablespaces/292909/155056": No such file or directory
regression=#
The fact that it fails isn't a big problem, but the error message is
pretty unclear if you're unfamiliar with the implementation.
The same problem would exist with respect to a database's default
tablespace, except that a database will always have its system catalogs
stored there and so the file-level check prevents dropping the
tablespace.
I don't think we can directly prevent the DROP TABLESPACE, since we
can't see what's in pg_namespace of other databases. I thought about
creating a placeholder file associated with every schema that has a
nondefault tablespace, thereby allowing the file-level check to detect
the problem. But that looks very ugly, not least because namespaces
don't have relfilenode values. What might be the most appropriate
solution is just to issue a specialized error message in
TablespaceCreateDbspace(): if mkdir fails with ENOENT, we could say
something likeERROR: tablespace 292909 has been deleted
after making an appropriate stat() test to verify that the symlink is
indeed gone. It's not great that we'd have to use the OID in this
message, but since the pg_tablespace row is (presumably) gone I don't
see any way to get the actual name of the tablespace.
Anyone see other alternatives?
regards, tom lane