Re: Using ALTER TABLESPACE in pg_dump

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 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.
> 
> This seems fairly impractical, at least for indexes where there is no
> way to do the ALTER before the object is filled with data.
> 
> > 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.
> 
> Yeah, that's one thing that bothers me.
> 
> > 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:
> 
> Hmm, here's a variant idea: how about a GUC variable named something like
> "soft_tablespace_specs" which when TRUE would mean that a nonexistent
> tablespace name in a TABLESPACE clause is ignored (maybe with a WARNING)
> rather than being an error, and so the object is created in whatever the
> default tablespace for it would be.  You wouldn't even necessarily want
> to have pg_dump set this true for itself, but people could turn it on
> when they needed to load a dump with wrong tablespace names in it.
> (If we didn't have pg_dump turn it on automatically, then we'd not be
> beholden to support it forever.)

That's a nice idea in that it doesn't require a SET for every object
that uses tablespace, and allows user control over tablespace failure.

The only downside is that it prevents SQL-compliant CREATE syntax in
dumps.  I was thinking you could use ALTER just for tables but then the
tablespace failure rules would be different for tables and other
objects, which is unacceptable.

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