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

Added to open items:
* Add a GUC variable to control temporary and TOAST tablespace usage


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

Gavin Sherry wrote:
> 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
> 

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