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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
