Re: Using ALTER TABLESPACE in pg_dump

Added to open items list:

* Tablespace
o add new GUC default_tablespace to control object creation when
no explicit TABLESPACE clause exists

Use it in pg_dump.

o Remove tablespace default for databases and schemas

Place objects as specified by the TABLESPACE clause or
default_tablespace. The database tablespace controls only
the system objects.

---------------------------------------------------------------------------

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

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073