Re: Using ALTER TABLESPACE in pg_dump - Mailing list pgsql-hackers

On Sun, 31 Oct 2004, Bruce Momjian wrote:

> Tom Lane wrote:
> > I wrote:
> > > 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.
> >
> > After further thought it seems to me that using a default_tablespace
> > GUC variable doesn't eliminate all the open issues.  In particular
> > it is no help for the problem of merging two different tablespaces
> > during CREATE DATABASE, ie, creating a new DB with a dattablespace
> > that is different from the template DB's default when the template
> > DB already has some tables explicitly placed into that tablespace.
> > In this situation we have the problem that the cloned DB would
> > have pg_class rows with different references to the same tablespace
> > (either zero for the database default, or the explicit OID of the
> > tablespace).  Among other things this would make it impossible to
> > use the cloned DB again as a template for CREATE DATABASE.
>
> Right.  I would say 99% of people are using template1 as the template
> for new databases, and if we clearly give an error message when they use
> a database not in the default tablespace (which we do now), it seems
> just fine.  Let's see how many people complain and make adjustments in
> 8.1 if needed.

I agree.

>
> > AFAICS this problem stems ultimately from the choice to have a
> > special representation (zero) in pg_class for the database's default
> > tablespace.  The only way to really get rid of it would be to eliminate
> > that provision and say that pg_class.reltablespace is always the correct
> > explicit OID.  What that would mean in turn is that we could not copy a
> > database and move its tables into a different tablespace, at least not
> > without very major work on CREATE DATABASE to make it alter pg_class
> > on-the-fly while copying.
>
> Agreed.  That is just too much work for so little gain.

I agree. Although, I think having a createdb() with transaction semantics
and the ability to modify data on the fly would be useful -- not just for
tablespace handling. As you say, it is a fair bit of work, however.

>
> > We might want to think about doing that eventually, but for now I'd
> > say that the restriction on merging tablespaces is just something
> > we have to live with.  It's less annoying than not being able to
> > relocate a database, for sure.
>
> One downside that came up yesterday in a discussion is that once shemas
> don't have default tablespaces we can't easily have default tablespaces
> for toast and temporary table system schemas.  Now we can't actually do
> that now anyway because they are created by the system but it might
> limit how we can control these in the future.  I am just throwing this
> out as a point.

Neil has been talking to me about being able to set a tablespace for
temporary tables at or after create database time.

I'm not sure about TOAST however. I considered the idea of adding
something to CREATE TABLE like TOASTSPACE <tablespace>, such that all
TOAST tables would be put in the 'toastspace'. But I think the syntax is
ugly and would confuse many users who do not know what toast is.

Thanks,

Gavin