I just posted this idea with a new heading. ALTER seems like the right
approach, but notice we need to complete another TODO item to allow
tablespace movement of databases and schemas.
---------------------------------------------------------------------------
Fabien COELHO wrote:
>
> Dear Tom,
>
> >> 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.
>
> I agree that the "set" approach is error prone.
>
> Another idea was to issue an "ALTER" after the CREATE.
>
> That would move the empty table from one tablespace to another, at small
> cost. If it fails, it is simply ignored by the restoration process,
> but the table was already created so it exists.
>
> > 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.
>
> (1) --notablespace would be useful, but it would not fix the problem
> I had in mind, i.e. the transfer (possibly after a crash) of data
> to another base which would not have these tablespaces. If the disk
> is crashed, I cannot redo the pg_dump.
>
> (2) thus it would help to be able to decide this at "restore" time.
> I think that one of the implementation idea was to store the
> information into some headers.
>
> (3) possible current workaround for the desperate admin:
> (a) create fake tablespaces as necessary...
> (b) pg_restore ... | sed 's/TABLESPACE .*//' | psql ...
>
> Have a nice day,
>
> --
> Fabien Coelho - coelho@cri.ensmp.fr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
-- 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