Обсуждение: Why is the index not created in the tablespace delivered in the create-index-command ?

On PosgreSQl 8.0.0 beta 3 (on SuSE Linux 8.1) I tried this:

mkdir /opt/pgsql/data2
mkdir /opt/pgsql/data3
psql test1

test1=# CREATE TABLESPACE ts_test_1  OWNER testuser LOCATION '/opt/pgsql/data2';
CREATE TABLESPACE
test1=# CREATE TABLESPACE ts_test_2  OWNER testuser LOCATION '/opt/pgsql/data3';
CREATE TABLESPACE
test1=# \q

psql test1 testuser
test1=> create table foobar ( foo varchar(50) ) TABLESPACE ts_test_1;
CREATE TABLE
test1=> select * from pg_tables where tablespace='ts_test_1';
  schemaname | tablename | tablespace | tableowner | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
  public     | foobar    | ts_test_1  | testuser   | f          | f        | f
(1 row)
test1=> create index ix_foobar on foobar(foo) TABLESPACE ts_test_2;
CREATE INDEX
test1=>  select * from pg_indexes  where tablename='foobar';
  schemaname | tablename | tablespace | indexname |                                indexdef

------------+-----------+------------+-----------+-------------------------------------------------------------------------
  public     | foobar    | ts_test_1  | ix_foobar | CREATE INDEX ix_foobar ON foobar USING btree (foo) TABLESPACE
ts_test_2
(1 row)

Why is index "ix_foobar" in tablespace "ts_test_1" and not in tablespace "ts_test_2" ?
Is it a bug ?

regards
  Michael Kleiser   Web.de AG

Michael Kleiser <mkl@webde-ag.de> writes:
> test1=>  select * from pg_indexes  where tablename='foobar';
>   schemaname | tablename | tablespace | indexname |                                indexdef
>
------------+-----------+------------+-----------+-------------------------------------------------------------------------
>   public     | foobar    | ts_test_1  | ix_foobar | CREATE INDEX ix_foobar ON foobar USING btree (foo) TABLESPACE
ts_test_2
> (1 row)

> Why is index "ix_foobar" in tablespace "ts_test_1" and not in tablespace "ts_test_2" ?
> Is it a bug ?

The index is in fact in the right tablespace, as you can verify by
checking its pg_class entry.  The problem is that the pg_indexes view is
showing you the table's tablespace and not the index's.  I think this is
clearly wrong, or at least not what one would find most useful.

Since we've already decided to force an initdb for beta4, there doesn't
seem to be any downside to fixing this now.  I'm going to change the
view to show the index tablespace instead of the table's, and I think
also move the tablespace one column to the right --- it seems bizarre
to place it before the index name.

Thanks for the report!

            regards, tom lane