Index file got removed

Hi,

Please look at a below sequence of SQL.

        set default_tablespace to '';
        create table test(id int primary key,  processid bigint not null ,
ruleid bigint not null );
        create index on test (ruleid );
        create index on test (processid );
        set default_tablespace to tblsp;
        alter table test alter processid type bigint, alter processid drop
not null;

I am creating a table test and index for that table on  default tablespace.
Then i'm changing default_tablespace to some other tablespace.
After that altering one of the index column.
This alter operation moves the index to new default_tablespace, but index
file is missing in new tablespace.
So we can not access the table.

----
db=# select * from test;
ERROR:  could not open file "pg_tblspc/16703/PG_9.6_201608131/16385/16710":
No such file or directory

db=# \d+ test
                          Table "public.test"
  Column   |  Type   | Modifiers | Storage | Stats target | Description
-----------+---------+-----------+---------+--------------+-------------
 id        | integer | not null  | plain   |              |
 processid | bigint  |           | plain   |              |
 ruleid    | bigint  | not null  | plain   |              |
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)
    "test_processid_idx" btree (processid), tablespace "tblsp"
    "test_ruleid_idx" btree (ruleid)

---

I tried it on 9.5.1 & 9.6.0, index got missed in both version.

-sudalai



-----
sudalai
--
View this message in context: http://postgresql.nabble.com/Index-file-got-removed-tp5930602.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.