Thread: Tablespaces and primary keys

Implicit indexes created by a constraint do not appear to honor the default
tablespace.  The index gets created in the "null" tablespace.

I took pg_dumpall output and modified the schema to place everything in
specific table spaces.   When the statement:

  ALTER TABLE ONLY addresses
    ADD CONSTRAINT addresses_pkey PRIMARY KEY (address_key);

is executed, this results in an implicitly created index:

   NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
      "addresses_pkey" for table "addresses"

However, it ignores my previous command:

  SET default_tablespace = indexes;

and creates the index without a tablespace.  Of course, I can do an

  ALTER INDEX addresses_pkey SET tablespace indexes;

but that's a bit of a hassle where there are a number of very large indexes.

I can't pre-create the index, as the constraint will try to create it's own
index anyway.

Is there something I'm overlooking, or is this a bug?

Wes

On Mon, 2005-01-24 at 21:03 -0600, Wes wrote:
> Implicit indexes created by a constraint do not appear to honor the default
> tablespace.  The index gets created in the "null" tablespace.
>
> I took pg_dumpall output and modified the schema to place everything in
> specific table spaces.   When the statement:
>
>   ALTER TABLE ONLY addresses
>     ADD CONSTRAINT addresses_pkey PRIMARY KEY (address_key);
>
> is executed, this results in an implicitly created index:
>
>    NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
>       "addresses_pkey" for table "addresses"
>
> However, it ignores my previous command:
>
>   SET default_tablespace = indexes;
>
> and creates the index without a tablespace.

I can't repro this (with current sources):

neilc=# create tablespace foo location '/tmp/foo';
CREATE TABLESPACE
neilc=# set default_tablespace = 'foo';
SET
neilc=# create table xyz (a int primary key);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"xyz_pkey" for table "xyz"
CREATE TABLE
Time: 45.838 ms
neilc=# \d xyz
      Table "public.xyz"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer | not null
Indexes:
    "xyz_pkey" PRIMARY KEY, btree (a)
Tablespace: "foo"

neilc=# \d xyz_pkey
Index "public.xyz_pkey"
 Column |  Type
--------+---------
 a      | integer
primary key, btree, for table "public.xyz"
Tablespace: "foo"

neilc=# alter table xyz drop constraint xyz_pkey;
ALTER TABLE
neilc=# alter table xyz add constraint "xyz_pkey2" primary key (a);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
"xyz_pkey2" for table "xyz"
ALTER TABLE
neilc=# \d xyz_pkey2
Index "public.xyz_pkey2"
 Column |  Type
--------+---------
 a      | integer
primary key, btree, for table "public.xyz"
Tablespace: "foo"

-Neil