Thread: USING INDEX TABLESPACE

The attached patch allows the specification of the tablespace the index
is to be created in for unique and pk constraints.  It also fixes the
dumping of such constraints so that they are restored into the correct
tablespace, after they have been moved with SET TABLESPACE.  This is
currently an outstanding pg_dump bug.

The syntax is exactly what Oracle uses as far as i can tell, and I think
that we need 'using index' in there, because it's the index that's in a
tablespace, NOT the constraint itself.

Includes docs.

eg:

create table test (a integer primary key index tablespace loc);

create table test (a integer unique index tablespace loc);

create table test (a integer);
alter table test add primary key(a) index tablespace loc;

create table test (a integer);
alter table test add unique(a) index tablespace loc;

Chris

> create table test (a integer primary key index tablespace loc);
>
> create table test (a integer unique index tablespace loc);
>
> create table test (a integer);
> alter table test add primary key(a) index tablespace loc;
>
> create table test (a integer);
> alter table test add unique(a) index tablespace loc;

Crap!

I left out the word 'using' on all those examples :/

create table test (a integer primary key using index tablespace loc);

create table test (a integer unique using index tablespace loc);

create table test (a integer);
alter table test add primary key(a) using index tablespace loc;

create table test (a integer);
alter table test add unique(a) using index tablespace loc;

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> The attached patch allows the specification of the tablespace the index
> is to be created in for unique and pk constraints.  It also fixes the
> dumping of such constraints so that they are restored into the correct
> tablespace, after they have been moved with SET TABLESPACE.  This is
> currently an outstanding pg_dump bug.

Applied.  I also added some code to ruleutils.c to make
pg_get_constraintdef output the USING INDEX TABLESPACE clause at need.

We might want to think about getting pg_dump to rely more on
pg_get_constraintdef and friends, instead of native wit ...

            regards, tom lane

> Applied.  I also added some code to ruleutils.c to make
> pg_get_constraintdef output the USING INDEX TABLESPACE clause at need.
>
> We might want to think about getting pg_dump to rely more on
> pg_get_constraintdef and friends, instead of native wit ...

Yes, that was really dumb of me not to think of that :/

Especially since I wrote the primary key and unique parts of
pg_get_constraintdef... :P

I would like to have pg_get_xxxdef for everything.  Tables, whole
databases, etc. :)

Chris