Re: Tablespaces and primary keys

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