There is a confusing bug in tablespaces.  Here is examples:

OK, let's create a table with the 3 possible tablespaces of indexes:

test=# create table test(a int4) tablespace loc;
CREATE TABLE
test=# create unique index test_a_idx on test(a);
CREATE INDEX
test=# create unique index test_a_idx2 on test(a) tablespace loc;
CREATE INDEX
test=# create unique index test_a_idx3 on test(a) tablespace pg_default;
CREATE INDEX
test=# \d test
      Table "public.test"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |
Indexes:
     "test_a_idx" UNIQUE, btree (a)
     "test_a_idx2" UNIQUE, btree (a)
     "test_a_idx3" UNIQUE, btree (a)
Tablespace: "loc"

test=# select relname, reltablespace from pg_class where relname like 
'test%';
    relname   | reltablespace
-------------+---------------
  test        |         17229
  test_a_idx  |         17229
  test_a_idx2 |         17229
  test_a_idx3 |             0
(4 rows)

Note that psql (and pg_dump) will (because of pg_get_indexdef()) think 
that test_a_idx3 is in tablespace 'loc', even though it's in tablespace 
'pg_default'.

Now, let's make it worse:

test=# alter table test set tablespace loc2;
ALTER TABLE
test=# \d test
      Table "public.test"
  Column |  Type   | Modifiers
--------+---------+-----------
  a      | integer |
Indexes:
     "test_a_idx" UNIQUE, btree (a) TABLESPACE loc
     "test_a_idx2" UNIQUE, btree (a) TABLESPACE loc
     "test_a_idx3" UNIQUE, btree (a)
Tablespace: "loc2"

test=# select relname, reltablespace from pg_class where relname like 
'test%';
    relname   | reltablespace
-------------+---------------
  test        |         17279
  test_a_idx  |         17229
  test_a_idx2 |         17229
  test_a_idx3 |             0
(4 rows)

Now, it thinks test_a_idx3 is in loc2.  pg_dump will dump it like that 
as well, so when it's restored, test_a_idx3 will be recreated in loc2.

Chris


Wed, 28 Jul 2004 11:44:28 +0800
Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Interesting bug in tablespaces

This has been fixed in current CVS.

---------------------------------------------------------------------------

Christopher Kings-Lynne wrote:
> There is a confusing bug in tablespaces.  Here is examples:
> 
> OK, let's create a table with the 3 possible tablespaces of indexes:
> 
> test=# create table test(a int4) tablespace loc;
> CREATE TABLE
> test=# create unique index test_a_idx on test(a);
> CREATE INDEX
> test=# create unique index test_a_idx2 on test(a) tablespace loc;
> CREATE INDEX
> test=# create unique index test_a_idx3 on test(a) tablespace pg_default;
> CREATE INDEX
> test=# \d test
>       Table "public.test"
>   Column |  Type   | Modifiers
> --------+---------+-----------
>   a      | integer |
> Indexes:
>      "test_a_idx" UNIQUE, btree (a)
>      "test_a_idx2" UNIQUE, btree (a)
>      "test_a_idx3" UNIQUE, btree (a)
> Tablespace: "loc"
> 
> test=# select relname, reltablespace from pg_class where relname like 
> 'test%';
>     relname   | reltablespace
> -------------+---------------
>   test        |         17229
>   test_a_idx  |         17229
>   test_a_idx2 |         17229
>   test_a_idx3 |             0
> (4 rows)
> 
> Note that psql (and pg_dump) will (because of pg_get_indexdef()) think 
> that test_a_idx3 is in tablespace 'loc', even though it's in tablespace 
> 'pg_default'.
> 
> Now, let's make it worse:
> 
> test=# alter table test set tablespace loc2;
> ALTER TABLE
> test=# \d test
>       Table "public.test"
>   Column |  Type   | Modifiers
> --------+---------+-----------
>   a      | integer |
> Indexes:
>      "test_a_idx" UNIQUE, btree (a) TABLESPACE loc
>      "test_a_idx2" UNIQUE, btree (a) TABLESPACE loc
>      "test_a_idx3" UNIQUE, btree (a)
> Tablespace: "loc2"
> 
> test=# select relname, reltablespace from pg_class where relname like 
> 'test%';
>     relname   | reltablespace
> -------------+---------------
>   test        |         17279
>   test_a_idx  |         17229
>   test_a_idx2 |         17229
>   test_a_idx3 |             0
> (4 rows)
> 
> Now, it thinks test_a_idx3 is in loc2.  pg_dump will dump it like that 
> as well, so when it's restored, test_a_idx3 will be recreated in loc2.
> 
> Chris
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Sun, 17 Oct 2004 23:31:56 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Interesting bug in tablespaces