Re: Index file got removed

On Wed, Nov 16, 2016 at 3:16 AM, sudalai <sudalait2@gmail.com> wrote:
> 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

That's easily reproducible, your test case is just missing the
tablespace creation to be complete:
set default_tablespace to '';
\! rm -rf /home/ioltas/Desktop/tbspace/*
create tablespace tblsp location '/home/ioltas/ioltas/tbspace/';
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;
select * from test; -- boom

I am just digging into it, instinctively that would be something in
tablecmds.c..
--
Michael