Re: Using ALTER TABLESPACE in pg_dump

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> At 08:00 AM 26/10/2004, Tom Lane wrote:
>> I don't want a GUC variable that actively changes the default
>> tablespace; at least not unless you want to abandon the current
>> mechanisms for default tablespace choices entirely, and go over to
>> making the GUC variable be the sole arbiter.

> Something consistent with Schemas does sound good to me; a tablespace
> search path (or just single default), and support for a TABLESPACE clause
> on table and INDEX definitions would be good.

I can't see what a search path would be good for.

> For the three largest databases I work on, the namespace/schema that a
> table resides in is irrelevant to the tablespace that it should be stored
> in. So default tablespaces on the schema are a bit of a pointless feature.
> The ability to have the features of schemas: default tablespace for given
> users, a GUC variable, and ACLs on tablespaces would be far more valuable.

Another nice thing is that not having default tablespaces associated
with schemas eliminates that nasty issue about being able to drop such a
tablespace while the schema is still there.

It seems like we still need some notion of a database's schema, to put
the system catalogs in, but perhaps that need not be the same as the
default schema for user tables created in the database?

I'd be willing to jump this way if we can work out the
default-tablespace inconsistencies that Bruce has on the open items
list. Does anyone want to draft a concrete proposal? It seems like the
basic elements are:

* A GUC variable named something like default_tablespace that
controls which TS objects are created in when there's
no explicit TABLESPACE clause. The factory default for this
would of course be pg_default. Otherwise it's settable just
like any other GUC var.

* Get rid of TABLESPACE clause for CREATE SCHEMA, and
pg_namespace.nsptablespace (ooops, another initdb).

* Need to define exactly what TABLESPACE clause for a database
controls; location of its catalogs of course, but anything else?

* We could possibly say that a TABLESPACE clause attached to
CREATE TABLE determines the default tablespace for indexes
created by the same command; I'm not sure if this is a good
idea, or if the indexes should go into default_tablespace
absent a TABLESPACE clause attached directly to their defining
constraints. We certainly want default_tablespace to control
indexes created by separate commands, so there'd be some
inconsistency if we do the former.

regards, tom lane