From: | Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> |
---|---|
To: | Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Interesting bug in tablespaces |
Date: | 2004-07-28 03:44:28 |
Message-ID: | 4107211C.2050508@familyhealth.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Sherry | 2004-07-28 04:17:35 | Create database and tablespaces |
Previous Message | Marc G. Fournier | 2004-07-28 02:42:56 | Re: No mail? |