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

Tue, 06 Jul 2004 17:00:06 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
bug in DROP TABLESPACE
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

Tue, 6 Jul 2004 20:51:50 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: bug in DROP TABLESPACE
> 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


Wed, 07 Jul 2004 10:28:47 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: bug in DROP TABLESPACE
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



Wed, 07 Jul 2004 09:07:53 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
ALTER xxx SET TABLESPACE (was bug in DROP TABLESPACE)
> 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


Wed, 07 Jul 2004 15:19:34 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: ALTER xxx SET TABLESPACE (was bug in DROP TABLESPACE)
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

Wed, 7 Jul 2004 17:44:18 +1000 (EST)
Gavin Sherry <swm@linuxworld.com.au>
Re: ALTER xxx SET TABLESPACE (was bug in DROP TABLESPACE)
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



Wed, 07 Jul 2004 09:46:45 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: ALTER xx SET TABLESPACE (was bug in DROP TABLESPACE)
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

Fri, 09 Jul 2004 21:58:45 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in DROP TABLESPACE 

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, Pennsylvania 19073

Fri, 9 Jul 2004 22:07:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: bug in DROP TABLESPACE
> 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


Sat, 10 Jul 2004 15:59:14 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Re: bug in DROP TABLESPACE
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


Sat, 10 Jul 2004 10:33:58 +0200
Andreas Pflug <pgadmin@pse-consulting.de>
Re: bug in DROP TABLESPACE
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

Sat, 10 Jul 2004 15:10:49 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in DROP TABLESPACE 

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, Pennsylvania 19073

Sat, 10 Jul 2004 22:02:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: bug in DROP TABLESPACE