Обсуждение: Strange syntax for create/drop index

Hello,

  I have tried create/drop index in separate schema :

example :
create table :

    create table test.test(id integer not null,name varchar(30),constraint test_pkey primary key (id))  - ok
    create index test_name on test.test(name)  - ok
    drop index test_name - not ok
    drop index test.test_name - ok
    create index test.test_name on test.test(name)  - not ok

'drop index' request schema prefix, but 'create index' doesn't accept schema prefix

this is strange for me

Comments ?

Best
Peco

On Apr 9, 2006, at 12:56 , Haris Peco wrote:

> 'drop index' request schema prefix, but 'create index' doesn't
> accept schema prefix

Currently indexes must be in the same schema as the table they index,
so no schema is accepted for CREATE INDEX. Indeed, the documentation
for CREATE INDEX describes the name parameter so:

http://www.postgresql.org/docs/current/interactive/sql-
createindex.html#AEN42146

> name
>
>     The name of the index to be created. No schema name can be
> included here; the index is always created in the same schema as
> its parent table.

However, you could have two or more indexes with the same name, but
in different schemas, so you need to be able to schema-qualify an
index when you drop it, so DROP INDEX accepts a schema-qualified name.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

Michael,

  Thank you for answer, but you don't understand me
I understood syntax and reason for this, but why postgreSQL doesn't accept this :

create index test.test_name on test.test(name)

  schema prefix in 'create index'

I know that it isn't necessary, because postgreSQL know that index is (must be)
 in table's schema, but this is natural for sql writers

I expect that 'create ...' and 'drop ...' allow/request/don't accept schema prefix on same way

Thanks
Peco

On Sunday 09 April 2006 02:08 am, Michael Glaesemann wrote:
>
> On Apr 9, 2006, at 12:56 , Haris Peco wrote:
>
> > 'drop index' request schema prefix, but 'create index' doesn't
> > accept schema prefix
>
> Currently indexes must be in the same schema as the table they index,
> so no schema is accepted for CREATE INDEX. Indeed, the documentation
> for CREATE INDEX describes the name parameter so:
>
> http://www.postgresql.org/docs/current/interactive/sql-
> createindex.html#AEN42146
>
> > name
> >
> >     The name of the index to be created. No schema name can be
> > included here; the index is always created in the same schema as
> > its parent table.
>
> However, you could have two or more indexes with the same name, but
> in different schemas, so you need to be able to schema-qualify an
> index when you drop it, so DROP INDEX accepts a schema-qualified name.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>

On Apr 9, 2006, at 13:33 , Haris Peco wrote:

> create index test.test_name on test.test(name)
>
>   schema prefix in 'create index'
>
> I know that it isn't necessary, because postgreSQL know that index
> is (must be)
>  in table's schema, but this is natural for sql writers

Allowing a schema-qualified name for CREATE INDEX implies that there
is a choice of schema you could choose. By disallowing the schema, it
makes developers aware of the limitation of where the index can be
created. If the schema was allowed, some people would infer that they
can place the index in a schema other than the schema the table
resides in, and they would get bitten when they try to do so.

Michael Glaesemann
grzm myrealbox com

Michael,

  You have name A for 'create index' and name B for 'drop index'
I can think that it isn't same index

This is illogically for me

make object with name A and drop it with name B

> If the schema was allowed, some people would infer that they
> can place the index in a schema other than the schema the table
> resides in, and they would get bitten when they try to do so.

you can just return error (and you will enable indexes cross schema
later, maybe and you will not change syntax)

Thanks

On Sunday 09 April 2006 04:48 am, Michael Glaesemann wrote:
>
> On Apr 9, 2006, at 13:33 , Haris Peco wrote:
>
> > create index test.test_name on test.test(name)
> >
> >   schema prefix in 'create index'
> >
> > I know that it isn't necessary, because postgreSQL know that index
> > is (must be)
> >  in table's schema, but this is natural for sql writers
>
> Allowing a schema-qualified name for CREATE INDEX implies that there
> is a choice of schema you could choose. By disallowing the schema, it
> makes developers aware of the limitation of where the index can be
> created. If the schema was allowed, some people would infer that they
> can place the index in a schema other than the schema the table
> resides in, and they would get bitten when they try to do so.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>