Re: [PATCHES] ALTER TABLE ... SET TABLESPACE

On Mon, 21 Jun 2004, Tatsuo Ishii wrote:

> > On Sun, 20 Jun 2004, Tatsuo Ishii wrote:
> >
> > > > > Attached is a patch implementing this functionality.
> > > > >
> > > > > I've modified make_new_heap() as well as swap_relfilenodes() to not assume
> > > > > that tablespaces remain the same from old to new heap. I thought it better
> > > > > to go down this road than introduce a lot of duplicate code.
> > > >
> > > > I have tried your patches and it works great. Thanks.
> > > >
> > > > One thing I noticed was if I change tablespace for a table having
> > > > indexes, they are left in the old tablespace and the table itself was
> > > > moved to the new tablespace. I regard this is a good thing since I
> > > > could assign different table spaces for table and indexes.
> > > > It would be even better to assign different tablespaces for each
> > > > index.
> > >
> > > Hm. It seems there's a problem with tablespaces. What I did was:
> > >
> > > pgbench -i test
> > > alter table accounts set tablespace mydb2;
> > > \d accounts
> > >
> > > backend crashes by signal 11...
> >
> > I seem to be clobbering memory some where but I cannot get assert or
> > valgrind to tell me. Anyone got any ideas?
>
> First of all I would like to ask you if you intend to leave indexes in
> the old tables space or not.

Yes, that is intentional.

>
> Also I think we need to enhance ALTER INDEX to assign new table spaces
> for indexes. Assigning different tables spaces for tables and indexes
> are essential to gain more I/O speed IMO.

I thought about this. ALTER INDEX doesn't exist yet and I figured that,
unlike the case of tables, its easy to drop and recreate indexes in new
tablespaces.

I'm still stumped as to where I am corrupting memory with this patch
though. (There was another bug: I wasn't detecting the case where users
set tablespace to the tablespace that the table is already in).

gavin