Re: Open Items - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Open Items
Date
Msg-id 200410180348.i9I3md206507@candle.pha.pa.us
Whole thread Raw
In response to Re: Open Items  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Using ALTER TABLESPACE in pg_dump
List pgsql-hackers
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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_dumpis restored, all tablespaces will attempt to be created  in their original locations. If this fails, the user
mustbe able to  adjust the restore process.
 


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


pgsql-hackers by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: Interesting bug in tablespaces
Next
From: Oliver Jowett
Date:
Subject: V3 protocol gets out of sync on messages that cause allocation failures