Skip site navigation (1) Skip section navigation (2)

Re: Using ALTER TABLESPACE in pg_dump

From: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Philip Warner <pjw(at)rhyme(dot)com(dot)au>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>,Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using ALTER TABLESPACE in pg_dump
Date: 2004-10-26 00:01:04
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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

> 			regards, tom lane



In response to


pgsql-hackers by date

Next:From: Tom LaneDate: 2004-10-26 00:07:34
Subject: Re: Using ALTER TABLESPACE in pg_dump
Previous:From: Philip WarnerDate: 2004-10-25 23:47:10
Subject: Re: Using ALTER TABLESPACE in pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group