Skip site navigation (1) Skip section navigation (2)

Re: Using ALTER TABLESPACE in pg_dump

From: Gavin Sherry <swm(at)linuxworld(dot)com(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Philip Warner <pjw(at)rhyme(dot)com(dot)au>,Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>,Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr>,Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Using ALTER TABLESPACE in pg_dump
Date: 2004-10-26 00:25:46
Message-ID: Pine.LNX.4.58.0410261009550.11890@linuxworld.com.au (view raw or flat)
Thread:
Lists: pgsql-hackers
On Mon, 25 Oct 2004, Tom Lane wrote:

> Gavin Sherry <swm(at)linuxworld(dot)com(dot)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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2004-10-26 00:53:29
Subject: Re: Using ALTER TABLESPACE in pg_dump
Previous:From: Tom LaneDate: 2004-10-26 00:07:34
Subject: Re: Using ALTER TABLESPACE in pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group