Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
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 NolanOn 11/12/06, Jorge Godoy <[EMAIL PROTECTED]> wrote: I've seen it now... I just can't specify where the index should go, it alwaysgoes with the table... That's fine...
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
Tom Lane <[EMAIL PROTECTED]> writes: > Jorge Godoy <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
Martijn van Oosterhout 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 <[EMAIL PROTECTED]> pgpluK2pMTfOX.pgp Description: PGP signature
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
Jorge Godoy <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why isn't it allowed to create an index in a schema
> > 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
On Sun, Nov 12, 2006 at 01:38:30PM -0200, Jorge Godoy wrote: > Chris Mair <[EMAIL PROTECTED]> 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 http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Why isn't it allowed to create an index in a schema other than public?
Chris Mair <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why isn't it allowed to create an index in a schema
> 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) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq