Re: Using ALTER TABLESPACE in pg_dump

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.

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

regards, tom lane