Skip site navigation (1) Skip section navigation (2)

Interesting bug in tablespaces

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 (view raw or flat)
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


Responses

pgsql-hackers by date

Next:From: Gavin SherryDate: 2004-07-28 04:17:35
Subject: Create database and tablespaces
Previous:From: Marc G. FournierDate: 2004-07-28 02:42:56
Subject: Re: No mail?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group