Using ALTER TABLESPACE in pg_dump

One additional idea for this item is to use CREATE to first create the
object, then move it using ALTER, and the ALTER might fail if the
tablespace doesn't exist. The only problem with that is this TODO item:

o Allow databases and schemas to be moved to different tablespaces

One complexity is whether moving a schema should move all existing
schema objects or just define the location for future object creation.

If we add a new SET variable and use it in pg_dump we will have to
support it forever even if there is no practical use for it.

(ALTER was originally part of the open item but I removed it thinking we
might not have space to load the table in the default location, but I
forgot we create it empty and could move it before we load it.)

One interesting side-affect of allowing tablespace specification to fail
is that it might give users enough control that we can mark this item as
done:

* Allow database recovery where tablespaces can't be created

When a pg_dump is restored, all tablespaces will attempt to be created
in their original locations. If this fails, the user must be able to
adjust the restore process.

One idea would be that users could create any tablespaces or objects
they want to change before they do the restore and the restore would use
their new configuration and just error/skip the items they already
created. (This would also behave well if you load the dump and say stop
on any errors.)

For example, if you want to eliminate a tablespace, you just don't
create the directory and load your dump. Tablespace create will fail,
and all objects that use that tablespace will fail their ALTER and will
remain in their default locations. This actually seems less error-prone
than the idea of them manually changing things in the dump file.

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

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > o remove non-portable TABLESPACE clause from CREATE TABLE and
> > > use a new default_tablespace SET variable
> >
> > I'm coming around to the conclusion that this is simply a bad idea.
> >
> > The problem with having such a SET variable is that it plays hob with
> > the existing definition about where schemas and tables get a default
> > tablespace from. Which source wins (the database or schema default
> > tablespace, or the SET variable)? And why? The only really clean way
> > to have a SET variable for this is to forget about schema- or
> > table-based defaults. Do we want to do that? (Hey, it'd solve the
> > problem with schema tablespaces being droppable, because there wouldn't
> > *be* any such thing as a schema's tablespace anymore. But on the whole
> > this seems like a step backward in usability.)
>
> Agreed, a step backwards, but see below.
>
> > What we might want to do is invent a --notablespace option for pg_dump,
> > comparable to --noowner, to let someone make a dump that contains no
> > TABLESPACE clauses.
>
> Yea, that would work, but we went through so much work to allow SQL
> standard DDL statements, and it seems a shame to break it just for
> tablespaces.
>
> And, having it be a separate SET would also allow the tablespace
> creation to fail and still get the objects created. (If the
> explicit_tablespace doesn't exist during CREATE, we throw a warning.
> This would contrast with a create _failure_ when the tablespace doesn't
> exist and you say 'TABLESPACE t1' in CREATE.)
>
> So there were actually two uses for this, one for standards compliance,
> and the other was for flexibility in restoring to a system where the
> tablespaces can't be created. The SET could give us different behavior
> (warning vs. error) which would be useful for pg_dump.
>
> Could we call it "explicit_tablespace" and when it is "", it is the
> default, but when it isn't it is just like using 'TABLESPACE t1' in the
> CREATE, but throws a warning instead of an error if the tablespace
> doesn't exist?
>
> My assumption is that it would not be like the default_with_oids
> variable usage by pg_dump because it would be reset to '' (default) by
> pg_dump after each time it is used. I assume explicit_tablespace would
> always override the schema or database tablespace because it is
> "explicit".
>
> In fact this would partially fix the TODO we have:
>
> * Allow database recovery where tablespaces can't be created
>
> When a pg_dump is restored, all tablespaces will attempt to be created
> in their original locations. If this fails, the user must be able to
> adjust the restore process.
>
>
> --
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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