Re: Using ALTER TABLESPACE in pg_dump

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