Re: Using ALTER TABLESPACE in pg_dump

On Mon, 25 Oct 2004, Tom Lane wrote:

> Gavin Sherry <swm@linuxworld.com.au> writes:
> > Hmmm.. despite that problem, I was rather fond of schema default
> > tablespaces because they allow DBAs to set a policy for a particular
> > schema. The cases I've discussed with people so far are things
> > like creating a schema for a (closed source) application and associating
> > that with a tablespace. There by, all new objects created will be in that
> > tablespace without the need for DBA intervention. Its not necessary, but
> > its nice I think.
>
> On the other hand, driving it from a GUC variable would allow you to
> easily set a per-user default, which might be at least as useful.
>
> >> It seems like we still need some notion of a database's schema, to put
> >> the system catalogs in, but perhaps that need not be the same as the
> >> default schema for user tables created in the database?
>
> > By schema here, do you mean tablespace?
>
> Sorry, fingers faster than brain obviously.  Time to take a break...
>
> > I think a viable solution is to go with the latter (ie, for CREATE TABLE
> > foo(i int primary key) TABLESPACE ts; the index on i is created in
> > default_tablespace). However, I might be nice to be able to specify the
> > tablespace as part of the primary key clause. I say nice, but not
> > necessary.
>
> We already have that don't we?
>
> create table foo (f1 int,
>                   primary key (f1) using index tablespace its)
>                   tablespace tts;
>
> The question is where to put foo_pkey when "using index tablespace"
> isn't there but "tablespace" is.

Hah. I wasn't sure if that ever got in -- guess I should have checked.

>
> (BTW, since we stole that syntax from Oracle, maybe we should check what
> they do...)

As an aside -- I'm not quite sure we stole the syntax from Oracle. Oracle
has *a lot* more functionality and nothing like the parent's tablespace
system.

Basically, more than one database object can be stored in a single data
file in oracle. A tablespace is a group of such files. You can have two
files in a tablespace in diferent locations. That is, tablespace foo might
consist of /data1/a.dat and /data2/b.dat.

So, when you create a new database, you can determine where the 'system
catalogs' are by setting the datafile location for the system catalog
tablespaces. You can *also* set a default tablespace for the database --
default in the sense that all newly created objects with no explicit
tablespace clause are created in the tablespace. With an exception as
follows: Oracle relies heavily on the concept of a user's default
tablespace. Interestingly, this is what you just mentioned above :-).

Gavin