Re: problem/bug in drop tablespace?

Michael Nolan wrote:
> While researching a problem reported on the -general list by a user
who lost a disk containing his
> index tablespace, I ran into something, but I'm not sure is a serious
bug or just an inconsistency in
> how \d shows tables.
>
> Here are the steps I took.
>
> 1. Create a new database 'MYDB' and connect to it.
> 2. Create a new tablespace 'MYTBLSP'
> 3. Create a table 'MYTABLE' and populate it.
> 4. Create an index 'MYIND' on that table, with the index in the new
tablespace, MYTBLSP.
>
> Now, exit psql and delete the files in the tablespace directory
created in step 2, simulating the
> problem the user had.
>
> Trying to execute an SQL command on the table MYTABLE will, as
expected, generate an error.
>
> Now, drop tablespace MYTBLSP. This will produce an error, but it will
delete the tablespace according
> to \db.
>
> Recreate tablespace MYTBLSP.
>
> Regenerate the index on MYTABLE. Queries will work on this table
again, as expected.
>
> Now, here's the problem I ran into:
>
> The index will be rebuilt in tablespace MYTBLSP, but \d on table
MYTABLE will not show the index as
> being in that tablespace.

I cannot reproduce this on 9.1.3:

test=# CREATE TABLESPACE mytbsp LOCATION '/home/laurenz/x';
CREATE TABLESPACE

test=# CREATE TABLE mytable(id integer PRIMARY KEY USING INDEX
TABLESPACE mytbsp, val text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"mytable_pkey" for table "mytable"
CREATE TABLE

test=# INSERT INTO mytable VALUES (1, 'eins'), (2, 'zwei');
INSERT 0 2

test=# \d mytable
Table "laurenz.mytable"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
val | text |
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id), tablespace "mytbsp"

$ rm -rf /home/laurenz/x/PG_9.1_201105231/*

test=# SELECT * FROM mytable;
ERROR: could not open file
"pg_tblspc/46752/PG_9.1_201105231/16420/46759": No such file or
directory

Ok, that's expected.

test=# DROP TABLESPACE mytbsp;
DROP TABLESPACE

No error.

test=# CREATE TABLESPACE mytbsp LOCATION '/home/laurenz/x';
CREATE TABLESPACE

test=# REINDEX INDEX mytable_pkey;
ERROR: could not create directory
"pg_tblspc/46752/PG_9.1_201105231/16420": No such file or directory

Sure, the tablespace OID has changed.

test=# ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE

test=# ALTER TABLE mytable ADD PRIMARY KEY (id) USING INDEX TABLESPACE
mytbsp;
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"mytable_pkey" for table "mytable"
ALTER TABLE

test=# \d mytable
Table "laurenz.mytable"
Column | Type | Modifiers
--------+---------+-----------
id | integer | not null
val | text |
Indexes:
"mytable_pkey" PRIMARY KEY, btree (id), tablespace "mytbsp"

Looks ok.

Yours,
Laurenz Albe