Re: Interesting bug in tablespaces

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Interesting bug in tablespaces
Date: 2004-10-18 03:31:56
Message-ID: 200410180331.i9I3VuV04481@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


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(at)postgresql(dot)org
>

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2004-10-18 03:48:39 Re: Open Items
Previous Message Bruce Momjian 2004-10-18 03:31:40 Re: Open Items