Thread: Plans for index names unique to a table?
Are there any plans to make index names unique to a table instead of
to a schema? I was thinking similar to the way that constraints are
handled. It's not really necessary, but it would be kinda nice when
indexing a column across all tables in a schema that share the same
column name.
CREATE SCHEMA s;
CREATE TABLE s.a (x_id INT);
CREATE TABLE s.b (x_id INT);
CREATE TABLE s.c (x_id INT);
CREATE INDEX x_id_idx ON s.a (x_id);
CREATE INDEX x_id_idx ON s.b (x_id);
CREATE INDEX x_id_idx ON s.c (x_id);
Instead of:
CREATE INDEX a_x_id_idx ON s.a (x_id);
CREATE INDEX b_x_id_idx ON s.b (x_id);
CREATE INDEX c_x_id_idx ON s.c (x_id);
It's cosmetic like I said, but I couldn't figure out a reason for why
index names weren't unique to a given table like constraints.
Curious,Sean
--
Sean Chittenden
Sean Chittenden <sean@chittenden.org> writes:
> Are there any plans to make index names unique to a table instead of
> to a schema?
None on my plate ... I don't see any very reasonable way to do it.
Do you?
regards, tom lane
On Fri, May 09, 2003 at 12:07:13AM -0400, Tom Lane wrote: > Sean Chittenden <sean@chittenden.org> writes: > > Are there any plans to make index names unique to a table instead of > > to a schema? > > None on my plate ... I don't see any very reasonable way to do it. > Do you? What makes it unreasonable? How are constraints handled differently that makes it reasonable for them? I can think of two ways to handle this... a) Leave indexes in pg_class and add a field to indicate the table/object that the item belongs to. relnamespace could very possebly be used for this. b) Create a seperate table for indexes. Of course, it's trivial to change a few system tables, it's changing everything else in code that will be the fun part. :) I'm hoping that plan a wouldn't impact the internals too badly, but I really have no idea. FWIW, DB2 and (I think) Oracle both use a global namespace for indexes. Sybase and (I think) MSSQL don't. I really don't understand why you'd want indexes in a global namespace and wish IBM and Oracle would change their products. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <jim@nasby.net> writes:
> What makes it unreasonable? How are constraints handled differently that
> makes it reasonable for them?
Constraints aren't relations and don't need pg_class entries.
> I can think of two ways to handle this...
> a) Leave indexes in pg_class and add a field to indicate the
> table/object that the item belongs to. relnamespace could very possebly
> be used for this.
> b) Create a seperate table for indexes.
Either of these cures strikes me as worse than the disease. Now that we
have schemas, I don't think that the index name collision problem is
near as bad as it used to be. I'm not eager to uglify the catalog
structure to eliminate the problem.
We'd also be creating some compatibility headaches --- for instance,
DROP INDEX would have to change syntax to include the table name.
regards, tom lane
On Sat, May 10, 2003 at 12:10:08PM -0400, Tom Lane wrote: > > I can think of two ways to handle this... > > a) Leave indexes in pg_class and add a field to indicate the > > table/object that the item belongs to. relnamespace could very possebly > > be used for this. > > b) Create a seperate table for indexes. > > Either of these cures strikes me as worse than the disease. Now that we > have schemas, I don't think that the index name collision problem is > near as bad as it used to be. I'm not eager to uglify the catalog > structure to eliminate the problem. It doesn't seem that it would be very ugly if relnamespace could be used. The fact that it's called 'relnamespace' and not 'schemaoid' seems to indicate this sort of thing was envisioned when it was designed... > We'd also be creating some compatibility headaches --- for instance, > DROP INDEX would have to change syntax to include the table name. True... maybe a compatability mode, or making the table name optional as long as you identify a unique index name. Personally, I think the ugliest thing is to leave it as-is; globally named indexes just seem really stupid, imho. -- Jim C. Nasby (aka Decibel!) jim@nasby.net Member: Triangle Fraternity, Sports Car Club of America Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Saturday 10 May 2003 10:12 am, Jim C. Nasby wrote: > On Sat, May 10, 2003 at 12:10:08PM -0400, Tom Lane wrote: > > We'd also be creating some compatibility headaches --- for instance, > > DROP INDEX would have to change syntax to include the table name. > > True... maybe a compatability mode, or making the table name optional as > long as you identify a unique index name. > > Personally, I think the ugliest thing is to leave it as-is; globally > named indexes just seem really stupid, imho. Some of us are a lot more interested in being able to support datamodels in multiple RDBMS's and for us, compatibility with SQL99 is far more important than "fixing" things that might seem really stupid to various people. SQL is full of such things, I'm sure we can each shortlist a half-dozen of our favorite pet peeves, but personally I'd rather deal with the stupidity than sacrifice the portability that standards support brings. Indexes, views, tables - all are global namespace thingies and therefore required to be globally unique in SQL. At least SQL's consistent in its treatment of names.
Jim C. Nasby wrote:
> FWIW, DB2 and (I think) Oracle both use a global namespace for
> indexes. Sybase and (I think) MSSQL don't. I really don't understand
> why you'd want indexes in a global namespace and wish IBM and Oracle
> would change their products.
There are at least four compelling disadvantages to implementing a
"table" namespace for indices:
a) It is a change from current functionality, and would break things that work now;
b) It introduces a new namespace, and the need to manage it.
c) It is incompatible with several other databases' treatment of indices, and probably the SQL standards, too.
d) It introduces the need to change syntax on indices, requiring something like DROP INDEX "index_name" ON
TABLE"some_table"; which parallels the handling of RI triggers.
They may not all be individually compelling, but they add up...
--
output = ("cbbrowne" "@acm.org")
http://www3.sympatico.ca/cbbrowne/lisp.html
"More computing sins are committed in the name of efficiency (without
necessarily achieving it) than for any other single reason - including
blind stupidity." -- W.A. Wulf
Don Baccus <dhogaza@pacifier.com> writes:
> Indexes, views, tables - all are global namespace thingies and therefore
> required to be globally unique in SQL. At least SQL's consistent in its
> treatment of names.
Actually, indexes do not exist as a concept at all in the SQL standard.
They're swept under the rug as an implementation detail. So we're quite
on our own to do what we like.
If I were designing in a green field I'd probably make indexes have
table-local names --- but historically they've not been so in Postgres,
and it does not seem worth the work or compatibility hit to change it.
regards, tom lane
----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> > Either of these cures strikes me as worse than the disease. Now that we > have schemas, I don't think that the index name collision problem is > near as bad as it used to be. I'm not eager to uglify the catalog > structure to eliminate the problem. > > We'd also be creating some compatibility headaches --- for instance, > DROP INDEX would have to change syntax to include the table name. > I'm not suggesting this needs to be done, since localising names is in the end a convenience, albeit a mighty big one. But it did occur to me that if this were deemed necessary, backwards compatibility might be handled by having the existing syntax work where the index name is unique, and some extension (like "drop index foo from table bar") be required where it isn't. In the end the cost might well be greater than the benefit, though. andrew