Horton

Thread

  1. Plans for index names unique to a table?

    Sean Chittenden <sean@chittenden.org> — 2003-05-08T20:06:24Z

    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

  2. Re: Plans for index names unique to a table?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-09T04:07:13Z

    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

  3. Re: Plans for index names unique to a table?

    Don Baccus <dhogaza@pacifier.com> — 2003-05-10T10:32:06Z

    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.

  4. Re: Plans for index names unique to a table?

    Jim Nasby <jim@nasby.net> — 2003-05-10T15:53:09Z

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

  5. Re: Plans for index names unique to a table?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-10T16:10:08Z

    "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

  6. Re: Plans for index names unique to a table?

    Jim Nasby <jim@nasby.net> — 2003-05-10T17:12:23Z

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

  7. Re: Plans for index names unique to a table?

    Your Name <cbbrowne@cbbrowne.com> — 2003-05-10T17:44:42Z

    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

  8. Re: Plans for index names unique to a table?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-11T02:40:49Z

    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

  9. Re: Plans for index names unique to a table?

    Andrew Dunstan <andrew@dunslane.net> — 2003-05-12T00:25:09Z

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