Re: Using ALTER TABLESPACE in pg_dump

On Mon, 25 Oct 2004, Tom Lane wrote:

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

I agree.

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

Hmmm.. despite that problem, I was rather fond of schema default
tablespaces because they allow DBAs to set a policy for a particular
schema. The cases I've discussed with people so far are things
like creating a schema for a (closed source) application and associating
that with a tablespace. There by, all new objects created will be in that
tablespace without the need for DBA intervention. Its not necessary, but
its nice I think.

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

By schema here, do you mean tablespace?

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

This could be a bit messy (from a user's point of view). There are two
meanings (according to your plan): 1) the tablespace clause is the default
for the catalogs AND for newly created objects (we set default_tablespace
in datconfig); OR, 2) it only sets the tablespace for the catalogs. (You
could say that it just sets the default tablespace for new objects, but
then how do you set the catalog tablespace).

I guess (1) makes sense but it limits people. If we do (2), we have two
options: a) User needs to ALTER DATABASE SET default_table.. b) we add a
new key work. I think (b) is ugly.

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

I think a viable solution is to go with the latter (ie, for CREATE TABLE
foo(i int primary key) TABLESPACE ts; the index on i is created in
default_tablespace). However, I might be nice to be able to specify the
tablespace as part of the primary key clause. I say nice, but not
necessary.

>
> regards, tom lane

Thanks,

Gavin