creating index names automatically?
Could we create an option to create index names automatically, so you'd
only have to write
CREATE INDEX ON foo (a);
which would pick a name like foo_a_idx. We already do this in a number
of places such as constraint names and sequences without much trouble.
In most cases you don't really need to give an index a smart name since
the purpose is obvious.
Comments?
2009/12/20 Peter Eisentraut <peter_e@gmx.net>:
Could we create an option to create index names automatically, so you'd
only have to writeCREATE INDEX ON foo (a);
Yes, please.
Cheers,
BJ
Peter Eisentraut <peter_e@gmx.net> writes:
Could we create an option to create index names automatically, so you'd
only have to write
CREATE INDEX ON foo (a);
which would pick a name like foo_a_idx. We already do this in a number
of places such as constraint names and sequences without much trouble.
In most cases you don't really need to give an index a smart name since
the purpose is obvious.
In the cases where that's sensible, you can use constraint syntax, no?
I really doubt that it's that easy to pick a sensible name for an index
on an expression, for example.
regards, tom lane
In response to Peter Eisentraut :
Could we create an option to create index names automatically, so you'd
only have to writeCREATE INDEX ON foo (a);
which would pick a name like foo_a_idx. We already do this in a number
of places such as constraint names and sequences without much trouble.
In most cases you don't really need to give an index a smart name since
the purpose is obvious.Comments?
+1, as an additional option, only if no index-name specified.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
I wrote:
In the cases where that's sensible, you can use constraint syntax, no?
I really doubt that it's that easy to pick a sensible name for an index
on an expression, for example.
Although, having said that, I realize we just opened that can of worms
with the exclusion-constraint patch:
regression=# create table foo (f1 text, exclude (lower(f1) with =));
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "foo_exclusion" for table "foo"
CREATE TABLE
The above behavior seems to need improvement already.
regards, tom lane
Peter Eisentraut wrote:
Could we create an option to create index names automatically, so you'd
only have to writeCREATE INDEX ON foo (a);
which would pick a name like foo_a_idx.
Why wouldn't it default to a name more like:
CREATE INDEX "foo(a)" on foo(a);
which would extend pretty nicely to things like:
CREATE INDEX "foo USING GIN(hstore)" ON foo USING GIN(hstore);'
Seems to be both more readable and less chance for arbitrary
collisions if I have column names with underscores. Otherwise
what would the rule distinguishing "create index on foo(a_b)"
from "create index on foo(a,b)", etc.
I wrote:
Although, having said that, I realize we just opened that can of worms
with the exclusion-constraint patch:
regression=# create table foo (f1 text, exclude (lower(f1) with =));
NOTICE: CREATE TABLE / EXCLUDE will create implicit index "foo_exclusion" for table "foo"
CREATE TABLE
The above behavior seems to need improvement already.
And poking further, CREATE TABLE LIKE INCLUDING INDEXES is another place
where we've already bought into automatically generating index names for
arbitrary non-constraint indexes. And it's even dumber --- you get
names involving "_key" for indexes that aren't even unique. So it seems
like we already have a bit of a problem here.
The first thoughts I have about this are:
* Use FigureColname to derive a name for an expression column, except
I'd be inclined to have the fallback case be "expr" not "?column?".
* Append "_index" not "_key" if it's not a constraint-related index.
I'm also a bit tempted to propose that we start using FigureColname
for the actual attribute names of expression indexes, instead of the
not terribly helpful "pg_expression_n" convention. In this case we'd
have to append a number if necessary to make the name unique among the
column names of the index.
Comments?
regards, tom lane
On Dec 20, 2009, at 13:58 , Tom Lane wrote:
* Append "_index" not "_key" if it's not a constraint-related index.
"_idx" instead of "_index" keeps things a bit shorter (and a couple of
keystrokes further from NAMEDATALEN). There's precedent for
abbreviations with automatic naming in Postgres, e.g., "_fkey".
Michael Glaesemann
grzm seespotcode net
Michael Glaesemann <grzm@seespotcode.net> writes:
On Dec 20, 2009, at 13:58 , Tom Lane wrote:
* Append "_index" not "_key" if it's not a constraint-related index.
"_idx" instead of "_index" keeps things a bit shorter (and a couple of
keystrokes further from NAMEDATALEN). There's precedent for
abbreviations with automatic naming in Postgres, e.g., "_fkey".
No objection here.
BTW, I'm having second thoughts about the last part of my proposal:
I'm also a bit tempted to propose that we start using FigureColname
for the actual attribute names of expression indexes, instead of the
not terribly helpful "pg_expression_n" convention.
The trouble with changing the index attnames for expressions is that it
increases the risk of collisions with attnames for regular index
columns. You can hit that case today:
regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create index fooi on foo(f1, lower(f2));
CREATE INDEX
regression=# \d fooi
Index "public.fooi"
Column | Type | Definition
-----------------+---------+------------
f1 | integer | f1
pg_expression_2 | text | lower(f2)
btree, for table "public.foo"
regression=# alter table foo rename f1 to pg_expression_2;
ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnam_index"
DETAIL: Key (attrelid, attname)=(64621, pg_expression_2) already exists.
but it's not exactly probable that someone would name a column
pg_expression_N. The risk goes up quite a lot if we might use simple
names like "abs" or "lower" for expression columns.
We could work around this by being willing to rename index columns on
the fly, but that creates a big risk of failing to dump and reload
comments on index columns, because the columns might not get the same
names in a newer PG version. (I seem to remember having objected to the
whole concept of comments on index columns on the grounds that it would
lock us into the current index column naming scheme, and that's exactly
what it's doing here.)
So I think we're stuck with the current column naming rule, but we do
have wiggle room on the name of the index itself.
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
Could we create an option to create index names automatically, so you'd
only have to write
CREATE INDEX ON foo (a);
which would pick a name like foo_a_idx.
Having done all the groundwork to support that nicely, I find that it
doesn't work because of bison limitations :-(. AFAICT, the only way
we could support this syntax would be to make ON a reserved word.
Or at least more reserved than it is now. We used up all the wiggle
room we had by making CONCURRENTLY non-reserved.
Now ON is reserved according to SQL99, but I'm a bit hesitant to
make it so in our grammar for such a marginal feature as this.
Thoughts?
regards, tom lane
I wrote:
The trouble with changing the index attnames for expressions is that it
increases the risk of collisions with attnames for regular index
columns. You can hit that case today:
regression=# create table foo (f1 int, f2 text);
CREATE TABLE
regression=# create index fooi on foo(f1, lower(f2));
CREATE INDEX
regression=# \d fooi
Index "public.fooi"
Column | Type | Definition
-----------------+---------+------------
f1 | integer | f1
pg_expression_2 | text | lower(f2)
btree, for table "public.foo"
regression=# alter table foo rename f1 to pg_expression_2;
ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnam_index"
DETAIL: Key (attrelid, attname)=(64621, pg_expression_2) already exists.
but it's not exactly probable that someone would name a column
pg_expression_N. The risk goes up quite a lot if we might use simple
names like "abs" or "lower" for expression columns.
It strikes me that the easiest way to deal with this is just to get rid
of the code in renameatt() that tries to rename index columns to agree
with the underlying table columns. That code is not nearly bright
enough to deal with collisions, and furthermore it seems rather
inconsistent to try to rename index columns (which are not very
user-visible in the first place) while not renaming the indexes
themselves (which surely are user-visible). There was some marginal
excuse for doing it back when \d didn't show the index column
definition; but now that it does, I don't think the behavior is worth
expending effort on.
regards, tom lane
Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
Could we create an option to create index names automatically, so you'd
only have to writeCREATE INDEX ON foo (a);
which would pick a name like foo_a_idx.
Having done all the groundwork to support that nicely, I find that it
doesn't work because of bison limitations :-(. AFAICT, the only way
we could support this syntax would be to make ON a reserved word.
Or at least more reserved than it is now. We used up all the wiggle
room we had by making CONCURRENTLY non-reserved.
And here's Simon talking about making CONCURRENTLY more reserved so that
people stop creating indexes named "concurrently" ...
http://database-explorer.blogspot.com/2009/09/create-index-concurrently.html
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
Tom Lane wrote:
... AFAICT, the only way
we could support this syntax would be to make ON a reserved word.
Or at least more reserved than it is now. We used up all the wiggle
room we had by making CONCURRENTLY non-reserved.
And here's Simon talking about making CONCURRENTLY more reserved so that
people stop creating indexes named "concurrently" ...
http://database-explorer.blogspot.com/2009/09/create-index-concurrently.html
Hmm. It would actually work if we made CONCURRENTLY reserved instead;
and that would fix Simon's gripe too. That's kind of weird from a
standards-compliance POV, but in terms of the risk of breaking
applications it might be better than reserving ON.
regards, tom lane
I wrote:
Hmm. It would actually work if we made CONCURRENTLY reserved instead;
and that would fix Simon's gripe too. That's kind of weird from a
standards-compliance POV, but in terms of the risk of breaking
applications it might be better than reserving ON.
Wait a minute. I must have been looking at the wrong keyword list
--- ON already is reserved. The problem is exactly that it can't
tell whether CREATE INDEX CONCURRENTLY ON ... means to default the
index name or to create an index named CONCURRENTLY. So really the
*only* way to fix this is to make CONCURRENTLY be at least
type_func_name_keyword.regards, tom lane
On Dec 22, 2009, at 7:31 PM, Tom Lane wrote:
Wait a minute. I must have been looking at the wrong keyword list --- ON already is reserved. The problem is exactly that it can't tell whether CREATE INDEX CONCURRENTLY ON ... means to default the index name or to create an index named CONCURRENTLY. So really the *only* way to fix this is to make CONCURRENTLY be at least type_func_name_keyword.
+1 if it prevents indexes from being named "CONCURRENTLY".
Best,
David
David E. Wheeler wrote:
On Dec 22, 2009, at 7:31 PM, Tom Lane wrote:
Wait a minute. I must have been looking at the wrong keyword list --- ON already is reserved. The problem is exactly that it can't tell whether CREATE INDEX CONCURRENTLY ON ... means to default the index name or to create an index named CONCURRENTLY. So really the *only* way to fix this is to make CONCURRENTLY be at least type_func_name_keyword.+1 if it prevents indexes from being named "CONCURRENTLY".
Yeah, if you really want to have an index named like that you can use
double quotes. Seems a sensible compromise.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
David E. Wheeler wrote:
+1 if it prevents indexes from being named "CONCURRENTLY".
Yeah, if you really want to have an index named like that you can use
double quotes. Seems a sensible compromise.
Well, this will also break tables and columns named "concurrently".
I think the odds of it being a problem are small, but still it is
a reserved word that shouldn't be reserved according to the SQL spec.
regards, tom lane
On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Alvaro Herrera <alvherre@commandprompt.com> writes:
David E. Wheeler wrote:
+1 if it prevents indexes from being named "CONCURRENTLY".
Yeah, if you really want to have an index named like that you can use
double quotes. Seems a sensible compromise.Well, this will also break tables and columns named "concurrently".
I think the odds of it being a problem are small, but still it is
a reserved word that shouldn't be reserved according to the SQL spec.
I suppose we could fix this by specifying a precedence and then
explicitly checking if you're trying to make an index named
concurrently and fixing it up later. Not unlike how you suggested we
avoid making WITH a reserved word with the comment that there was more
than one way to skin a cat
--
greg
Greg Stark <gsstark@mit.edu> writes:
On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, this will also break tables and columns named "concurrently".
I think the odds of it being a problem are small, but still it is
a reserved word that shouldn't be reserved according to the SQL spec.
I suppose we could fix this by specifying a precedence and then
explicitly checking if you're trying to make an index named
concurrently and fixing it up later.
No, not really. Past the grammar there is no way to tell concurrently
from "concurrently", ie, if we did it like that then you couldn't even
use double quotes to get around it. Don't overthink this: either we
reserve the word or we don't put in the feature.
regards, tom lane
I wrote:
Greg Stark <gsstark@mit.edu> writes:
On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, this will also break tables and columns named "concurrently".
I think the odds of it being a problem are small, but still it is
a reserved word that shouldn't be reserved according to the SQL spec.
I suppose we could fix this by specifying a precedence and then
explicitly checking if you're trying to make an index named
concurrently and fixing it up later.
No, not really. Past the grammar there is no way to tell concurrently
from "concurrently", ie, if we did it like that then you couldn't even
use double quotes to get around it. Don't overthink this: either we
reserve the word or we don't put in the feature.
I haven't heard anyone speak against making CONCURRENTLY semi-reserved,
so I'll go ahead and do it that way.
regards, tom lane
