Gavin,

#1:  I really think that we should have a way to set a "default tablespace" 
for any database in a cluster.    This property would be vitally important 
for anyone wishing to use tablespaces to impose quotas.   First, the 
superuser would:
ALTER DATABASE db1 ALTER DEFAULT_TABLESPACE partition2;
then any regular users creating tables in that database would, by default, 
have TABLESPACE partition2 automatically appended to them by the parser 
unless overridden in the creation statement by specifying another, specific, 
tablespace.

Alternately, the default tablespace could be set through a GUC.   In my mind, 
this would be inferior on 2 counts:
1) It would require adding Yet Another Miscellaneos GUC Variable.
2) It would preclude large, multisuer installations from seamlessly using 
tablespaces for quotas, becuase there would be no way to transparently set 
the GUC differently for each user or database.


#2: Permissions:
I see the permissions issue as quite transparent.   First, I agree that only 
the superuser should have the right to create, alter, or drop tablespaces. 
'nuff said.
Second, as far as I can see, there is only one relevant permission for regular 
users:  USE.   Either the user is permitted to create objects in that 
tablespace, or he/she is not.  Other permissions, such as read access, should 
NOT be set by tablespace, as such permissions are already governed by 
database, table, and schema; to add a SELECT restriction to tablespaces would 
frequently result in paralytic snarls of conflicting permissions on complex 
installations.
Thus, by my proposal, the only GRANT for tablespaces (executed by a superuser) 
would be:
GRANT USE ON tablespace1 TO user;
This permission would ONLY be accessed for CREATE/ALTER TABLE, and CREATE 
INDEX statements.
Easy, neh?

#3: ALTER TABLE .... CHANGE TABLESPACE:
This is strictly in the class of "would be a very nice & useful feature if 
it's not too difficult".   

Given how painful it is to drop & replace a table with multiple dependencies 
(on some databases, only possible by droping & re-loading the entire 
database) it would be nice to have an ALTER TABLE command that moved the 
table to another tablespace.    It doesn't *seem* to me that this would be a 
very challenging bit of programming, as the operation would be very similar 
to REINDEX in the manipulation of files.   (But what I know, really?)

Once tablespaces are a feature and some users start using them for quota 
management, there will quickly develop situations where the original 
tablespace for a db runs out of room and can't be resized.   Being able to 
move the table "in situ" then becomes vital, especially on very large 
databases ... and when someday combined with partitioned tables, will become 
essential.

Further, we will get an *immediate* flurry of requests from users who just 
upgraded to 7.5 and want to make use of the tablespaces feature on an 
existing production database.

ALTER INDEX ... CHANGE TABLESPACE is *not* needed, though, as there are no 
issues other than time which I know of with dropping & re-creating an index.

If ALTER TABLE CHANGE TABLESPACE has some major technical hurdles, then I 
think it's one of those things that could be put off until the next version 
of tablespaces, or even held until Partition Tables is developed for a 
combined solution.    But it would be nice to have.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco


Sun, 29 Feb 2004 14:27:21 -0800
Josh Berkus <josh@agliodbs.com>
Re: Tablespaces
Josh Berkus wrote:
> #3: ALTER TABLE .... CHANGE TABLESPACE:
> This is strictly in the class of "would be a very nice & useful feature if 
> it's not too difficult". ? 
> 
> Given how painful it is to drop & replace a table with multiple dependencies 
> (on some databases, only possible by droping & re-loading the entire 
> database) it would be nice to have an ALTER TABLE command that moved the 
> table to another tablespace. ? ?It doesn't *seem* to me that this would be a 
> very challenging bit of programming, as the operation would be very similar 
> to REINDEX in the manipulation of files. ? (But what I know, really?)
> 
> Once tablespaces are a feature and some users start using them for quota 
> management, there will quickly develop situations where the original 
> tablespace for a db runs out of room and can't be resized. ? Being able to 
> move the table "in situ" then becomes vital, especially on very large 
> databases ... and when someday combined with partitioned tables, will become 
> essential.
> 
> Further, we will get an *immediate* flurry of requests from users who just 
> upgraded to 7.5 and want to make use of the tablespaces feature on an 
> existing production database.

If we don't implement moving tables between tablespaces, we should add a
stub for it in the grammer and mention it is not implemented yet,
because if we don't, we will get tons of questions.

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

Tue, 2 Mar 2004 23:37:15 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Tablespaces