Thread: Minor DROP TABLESPACE issue
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
On Fri, 18 Jun 2004, Tom Lane wrote: > 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: > Of course. Never thought of that one. > 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. Is it really that bad? NamespaceCreate() could just touch a file at pg_tablespaces/<tablespaceoid>/<MyDatabaseId>/<namespace oid> and RemoveSchema() could remove it. Is there anything ugly that I've missed? Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
> Is it really that bad? NamespaceCreate() could just touch a file at
> pg_tablespaces/<tablespaceoid>/<MyDatabaseId>/<namespace oid> and
> RemoveSchema() could remove it.
No, because that wouldn't be rollback-safe. You'd have to make the
placeholder file act enough like a real relation that the smgr
delete-at-commit-or-abort mechanisms could handle it. So then you
have to buy into not having its name actually collide with any real
relations, which is where I started feeling like I didn't want to
pursue that solution.
This is certainly doable in theory, it just seems like much more
complexity than the problem is worth ...
regards, tom lane
On Fri, 18 Jun 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > Is it really that bad? NamespaceCreate() could just touch a file at > > pg_tablespaces/<tablespaceoid>/<MyDatabaseId>/<namespace oid> and > > RemoveSchema() could remove it. > > No, because that wouldn't be rollback-safe. You'd have to make the > placeholder file act enough like a real relation that the smgr > delete-at-commit-or-abort mechanisms could handle it. So then you > have to buy into not having its name actually collide with any real > relations, which is where I started feeling like I didn't want to > pursue that solution. > > This is certainly doable in theory, it just seems like much more > complexity than the problem is worth ... Oh yeah... I forgot about OID wrap around issues and integration into WAL, etc, wouldn't be great fun. If we just wanted to make the error message more user friendly, perhaps we could *gulp* store the tablespace name in PG_VERSION. Then again, all of this can probably be handled better in the manual. Gavin
Gavin Sherry <swm@linuxworld.com.au> writes:
> If we just wanted to make the error message more user friendly, perhaps we
> could *gulp* store the tablespace name in PG_VERSION.
That's actually not a bad idea from a debugging standpoint. But I don't
see that it helps any for this particular problem. The PG_VERSION file
will be gone when we need the info.
regards, tom lane
On Fri, 18 Jun 2004, Tom Lane wrote: > Gavin Sherry <swm@linuxworld.com.au> writes: > > If we just wanted to make the error message more user friendly, perhaps we > > could *gulp* store the tablespace name in PG_VERSION. > > That's actually not a bad idea from a debugging standpoint. But I don't > see that it helps any for this particular problem. The PG_VERSION file > will be gone when we need the info. Duh. I best get some coffee into me. Gavin
> ERROR: tablespace 292909 has been deleted How about "schema default tablespace 292909 has been deleted"? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> ERROR: tablespace 292909 has been deleted
> How about "schema default tablespace 292909 has been deleted"?
Yeah, I was wondering if we could finger the specific schema that
was causing the problem, but the place that would detect the error
couldn't really know that. Perhaps we could add a HINT advising
to look in pg_namespace for the relevant entry.
regards, tom lane
Tom Lane wrote: > 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 Why this doesn't fail? The junk schema depend on tablespace junk, is there no dependencies between these two objects. G.
Gaetano Mendola <mendola@bigfoot.com> writes:
> is there no dependencies between these two objects.
No, and there's little point in adding one, since it wouldn't prevent
the problem from happening if you issue the DROP TABLESPACE from a
different database.
regards, tom lane