Re: Using ALTER TABLESPACE in pg_dump

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.

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

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

> Despite this, the default_tablespace GUC variable seems more attractive
> than what we have now. Last call for objections ...

Sounds good.

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