Why isn't it allowed to create an index in a schema other than public?

I'd like to know if there's any reasoning for not allowing creating an index
inside the same schema where the table is. For example, if I have a
multi-company database where each company has its own schema and its employees
table, shouldn't I have a different index for each of those? What if I have
some slightly different columns on some of these tables?

================================================================================
teste=# create schema testing;
CREATE SCHEMA
teste=# create table testing.testing123 (something serial primary key, otherthing float);
NOTICE: CREATE TABLE will create implicit sequence "testing123_something_seq" for serial column "testing123.something"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testing123_pkey" for table "testing123"
CREATE TABLE
teste=# create index testing.testing123_index on testing.testing123 (otherthing);
ERROR: syntax error at or near "." no caracter 21
LINHA 1: create index testing.testing123_index on testing.testing123 ...
^
teste=#
================================================================================

(I wouldn't mind if the autogenerated index for the PK was created on the
public schema if no specific name was supplied.)

This would also help identifying all objects to make a certain feature
available and where they belong to on the database...

Just say
create index testing123_index on testing.testing123 (otherthing);
and you'll otain exactly what you want (see below).

Bye, Chris.

chris=> create schema testing;
CREATE SCHEMA
chris=> create table testing.testing123 (something serial primary key, otherthing float);
NOTICE: CREATE TABLE will create implicit sequence "testing123_something_seq" for serial column "testing123.something"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "testing123_pkey" for table "testing123"
CREATE TABLE
chris=> create index testing123_index on testing.testing123 (otherthing);
CREATE INDEX
chris=> \di *.*
List of relations
Schema | Name | Type | Owner | Table
---------+------------------+-------+-------+------------
testing | testing123_index | index | chris | testing123
testing | testing123_pkey | index | chris | testing123
(2 rows)

Chris Mair <chrisnospam@1006.org> writes:

Just say
create index testing123_index on testing.testing123 (otherthing);
and you'll otain exactly what you want (see below).

Bye, Chris.

I know I can workaround such debilitation. What I wanted to know is if
there's some reason (such as performance gain, for example) for that
decision.

Using this approach, though, doesn't group all items that belong to a schema
inside of it.

--
Jorge Godoy <jgodoy@gmail.com>

On Sun, Nov 12, 2006 at 01:38:30PM -0200, Jorge Godoy wrote:

Chris Mair <chrisnospam@1006.org> writes:

Just say
create index testing123_index on testing.testing123 (otherthing);
and you'll otain exactly what you want (see below).

Bye, Chris.

I know I can workaround such debilitation. What I wanted to know is if
there's some reason (such as performance gain, for example) for that
decision.

I think his point was that the index is always in the same schema as
the table itself. It states this quite clearly in the documentation. So
what exactly is the debilitation? It seems to be doing exactly what you
want.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

create index testing123_index on testing.testing123 (otherthing);
and you'll otain exactly what you want (see below).

Bye, Chris.

I know I can workaround such debilitation. What I wanted to know is if
there's some reason (such as performance gain, for example) for that
decision.

Read what \di *.* shows: the index *is* inside schema testing.

Using this approach, though, doesn't group all items that belong to a schema
inside of it.

Bye, Chris.

Jorge Godoy <jgodoy@gmail.com> writes:

I'd like to know if there's any reasoning for not allowing creating an index
inside the same schema where the table is.

Actually, you've got that exactly backwards: it's not allowed to have
the index in a *different* schema from its parent table. Hence there
is no need for the CREATE INDEX command to accept a schema attached
to the index name --- the only one that counts is the one attached to
the table name.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Jorge Godoy <jgodoy@gmail.com> writes:

I'd like to know if there's any reasoning for not allowing creating an index
inside the same schema where the table is.

Actually, you've got that exactly backwards: it's not allowed to have
the index in a *different* schema from its parent table. Hence there
is no need for the CREATE INDEX command to accept a schema attached
to the index name --- the only one that counts is the one attached to
the table name.

Indeed, Tom... As I said to Martijn I need to stop thinking about those thing
and rest more on weekends :-)

Sorry for the noise... :-)

--
Jorge Godoy <jgodoy@gmail.com>

I'm a little confused about what you mean when you say you can't specify
where the index should go. Schemas are a logical division, not a physical
one. There's no logical reason to have the index for a table in a separate
schema. (And if one were limiting which schemas a user could access, there
are good reasons NOT to have the index in a separate schema.)

If, on the other hand, you want to control where the index is physically
stored, for example to optimized disk access times, you should use
tablespaces.
--
Mike Nolan