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

Hi!


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...


TIA,
--
Jorge Godoy      <jgodoy@gmail.com>

> 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/
> From each according to his ability. To each according to his ability to litigate.

> >     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

Martijn van Oosterhout <kleptog@svana.org> writes:

> 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.

As Homer Simpson says: D'oh! :-)

I've seen it now...  I just can't specify where the index should go, it always
goes with the table...  That's fine...

My fault...  I need to rest on the weekend, not start having those crazy ideas
about where indices go... :-)

--
Jorge Godoy      <jgodoy@gmail.com>

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