Thread: bug in DROP TABLESPACE
There is bad breakage in the DROP TABLESPACE command if the only thing "in" that tablespace is the default tablespaces for a schema: test=# create tablespace myspace location '/home/chriskl/loc'; CREATE TABLESPACE test=# create schema myschema tablespace myspace; CREATE SCHEMA test=# drop tablespace myspace; DROP TABLESPACE test=# select * from pg_namespace where nspname='myschema'; nspname | nspowner | nsptablespace | nspacl ----------+----------+---------------+-------- myschema | 1 | 17228 | (1 row) test=# select * from pg_tablespace where oid=17228; spcname | spcowner | spclocation | spcacl ---------+----------+-------------+-------- (0 rows) test=# create table myschema.blah (a int4); ERROR: could not create directory "/home/chriskl/local/data/pg_tblspc/17228/17227": No such file or directory This is probably pretty nasty because it means there's no way to check if dropping a tablespace is safe :( Maybe the only solution is if nsptablespace is invalid when creating an object in the schema, then fix it and ignore it? Or fix it when first connecting to the database? Chris
On Tue, 6 Jul 2004, Christopher Kings-Lynne wrote: > There is bad breakage in the DROP TABLESPACE command if the only thing > "in" that tablespace is the default tablespaces for a schema: > > test=# create tablespace myspace location '/home/chriskl/loc'; > CREATE TABLESPACE > test=# create schema myschema tablespace myspace; > CREATE SCHEMA > test=# drop tablespace myspace; > DROP TABLESPACE > test=# select * from pg_namespace where nspname='myschema'; > nspname | nspowner | nsptablespace | nspacl > ----------+----------+---------------+-------- > myschema | 1 | 17228 | > (1 row) > > test=# select * from pg_tablespace where oid=17228; > spcname | spcowner | spclocation | spcacl > ---------+----------+-------------+-------- > (0 rows) > > test=# create table myschema.blah (a int4); > ERROR: could not create directory > "/home/chriskl/local/data/pg_tblspc/17228/17227": No such file or directory Tom already mentioned this just after committing tablespaces: 'Minor DROP TABLESPACE issue' http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg46540.html In fact, I see that you contributed to the thread :-). I think the result of the thread was to make the error message a little more helpful and that adding empty files to represent schemas would be a pain (think WAL and name collision). Thanks, Gavin
> Tom already mentioned this just after committing tablespaces: 'Minor DROP > TABLESPACE issue' > > http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg46540.html > > In fact, I see that you contributed to the thread :-). > > I think the result of the thread was to make the error message a little > more helpful and that adding empty files to represent schemas would be a > pain (think WAL and name collision). Ah, I must have been in a dream state. The other thing we need are these two commands: ALTER DATABASE foo SET TABLESPACE spc; ALTER SCHEMA foo SET TABLESPACE spc; I think these should not be considered new features but essential functionality left out of the original patch. Chris
Christopher Kings-Lynne wrote: > > The other thing we need are these two commands: > > ALTER DATABASE foo SET TABLESPACE spc; > ALTER SCHEMA foo SET TABLESPACE spc; > > I think these should not be considered new features but essential > functionality left out of the original patch. Its questionable if these are to be interpreted as just changing the default tablespace for subsequent creates, or also moving all objects that were created using the previous tablespace. Since it's indistinguishable whether an object was created using the default from schema/database or given a tablespace explicitely (unless default was 0), I'd opt for version one. Regards, Andreas
> Its questionable if these are to be interpreted as just changing the > default tablespace for subsequent creates, or also moving all objects > that were created using the previous tablespace. Since it's > indistinguishable whether an object was created using the default from > schema/database or given a tablespace explicitely (unless default was > 0), I'd opt for version one. I meant option one - I hadn't considered the second. The thing is though you have to maybe move some stuff on disk as well. Chris
Christopher Kings-Lynne wrote: >> Its questionable if these are to be interpreted as just changing the >> default tablespace for subsequent creates, or also moving all objects >> that were created using the previous tablespace. Since it's >> indistinguishable whether an object was created using the default >> from schema/database or given a tablespace explicitely (unless >> default was 0), I'd opt for version one. > > > I meant option one - I hadn't considered the second. The thing is > though you have to maybe move some stuff on disk as well. Currently not in case of schema (until we have a placeholder file); for database, certainly the directory structure needs to be created and the old removed if empty. AFAICS no move of data is necessary. Regards, Andreas
On Wed, 7 Jul 2004, Christopher Kings-Lynne wrote: > > Its questionable if these are to be interpreted as just changing the > > default tablespace for subsequent creates, or also moving all objects > > that were created using the previous tablespace. Since it's > > indistinguishable whether an object was created using the default from > > schema/database or given a tablespace explicitely (unless default was > > 0), I'd opt for version one. > > I meant option one - I hadn't considered the second. The thing is > though you have to maybe move some stuff on disk as well. Maybe? You definately do. In the ALTER DATABASE case you'd also need to lock users out of the database. I'm not so sure these additions are necessary for 7.5 though... Does anyone else have thoughts? Gavin
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> The other thing we need are these two commands:
> ALTER DATABASE foo SET TABLESPACE spc;
> ALTER SCHEMA foo SET TABLESPACE spc;
> I think these should not be considered new features but essential
> functionality left out of the original patch.
The latter is relatively trivial as long as you interpret it as just
changing the default TS for tables created later in that schema.
(On the other hand, you can fake that with a simple UPDATE to
pg_namespace, so I'm not sure it qualifies as a "must have".)
The former is a real mess since it would require moving some of the
system catalogs. (At least the nailed-in-cache ones, but I'm not sure
we've yet determined which ones *have* to be in the database's default
tablespace.) It'll be fairly entertaining to move pg_class in
particular, but I doubt you can build such a thing out of the available
spare parts at all.
My vote would be to postpone any such patch to 7.6 (or 8.1 as the case
may be), because it's a nontrivial addition of functionality and we had
no prototype as of July 1.
regards, tom lane
TODO item? --------------------------------------------------------------------------- Tom Lane wrote: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes: > > The other thing we need are these two commands: > > > ALTER DATABASE foo SET TABLESPACE spc; > > ALTER SCHEMA foo SET TABLESPACE spc; > > > I think these should not be considered new features but essential > > functionality left out of the original patch. > > The latter is relatively trivial as long as you interpret it as just > changing the default TS for tables created later in that schema. > (On the other hand, you can fake that with a simple UPDATE to > pg_namespace, so I'm not sure it qualifies as a "must have".) > > The former is a real mess since it would require moving some of the > system catalogs. (At least the nailed-in-cache ones, but I'm not sure > we've yet determined which ones *have* to be in the database's default > tablespace.) It'll be fairly entertaining to move pg_class in > particular, but I doubt you can build such a thing out of the available > spare parts at all. > > My vote would be to postpone any such patch to 7.6 (or 8.1 as the case > may be), because it's a nontrivial addition of functionality and we had > no prototype as of July 1. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
> The latter is relatively trivial as long as you interpret it as just > changing the default TS for tables created later in that schema. > (On the other hand, you can fake that with a simple UPDATE to > pg_namespace, so I'm not sure it qualifies as a "must have".) > > The former is a real mess since it would require moving some of the > system catalogs. (At least the nailed-in-cache ones, but I'm not sure > we've yet determined which ones *have* to be in the database's default > tablespace.) It'll be fairly entertaining to move pg_class in > particular, but I doubt you can build such a thing out of the available > spare parts at all. > > My vote would be to postpone any such patch to 7.6 (or 8.1 as the case > may be), because it's a nontrivial addition of functionality and we had > no prototype as of July 1. It seems to me that we have mixed up two concepts: the tablespaces that a database is IN and the default tablespaces for any schemas created in that tablespace. This will probably cause trouble further down the track :( Chris
Christopher Kings-Lynne wrote: >> > > It seems to me that we have mixed up two concepts: the tablespaces > that a database is IN and the default tablespaces for any schemas > created in that tablespace. This will probably cause trouble further > down the track :( As long as we interpret schema and database tablespace just as default for future object creation, hopefully not. This leaves moving objects from one tablespace to another up to us tool suppliers, which seems reasonable. Regards, Andreas
Andreas Pflug <pgadmin@pse-consulting.de> writes:
> Christopher Kings-Lynne wrote:
>> It seems to me that we have mixed up two concepts: the tablespaces
>> that a database is IN and the default tablespaces for any schemas
>> created in that tablespace. This will probably cause trouble further
>> down the track :(
> As long as we interpret schema and database tablespace just as default
> for future object creation, hopefully not.
But his point is that we don't --- the database tablespace is not "just
a default", it is *the place* where the system catalogs are stored, as
well as where anything else with pg_class.reltablespace = 0.
I think what we have is fine for 7.5. Later on we could look at
separating the two concepts. I'm not entirely sure what to call them
however. Also it'd be less than clear just what CREATE DATABASE should
do with cases where the values are being changed.
regards, tom lane
Added to TODO: * Allow changing of already-created database and schema tablespaces --------------------------------------------------------------------------- Christopher Kings-Lynne wrote: > > Tom already mentioned this just after committing tablespaces: 'Minor DROP > > TABLESPACE issue' > > > > http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg46540.html > > > > In fact, I see that you contributed to the thread :-). > > > > I think the result of the thread was to make the error message a little > > more helpful and that adding empty files to represent schemas would be a > > pain (think WAL and name collision). > > Ah, I must have been in a dream state. > > The other thing we need are these two commands: > > ALTER DATABASE foo SET TABLESPACE spc; > ALTER SCHEMA foo SET TABLESPACE spc; > > I think these should not be considered new features but essential > functionality left out of the original patch. > > Chris > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073