Re: Tablespaces

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