Thread

creating index names automatically?

Peter Eisentraut <peter_e@gmx.net> — 2009-12-19T21:06:41Z

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?

Re: creating index names automatically?

Brendan Jurd <direvus@gmail.com> — 2009-12-19T21:08:25Z

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 write
>
> CREATE INDEX ON foo (a);

Yes, please.

Cheers,
BJ

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-19T21:28:51Z

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

Re: creating index names automatically?

A. Kretschmer <andreas.kretschmer@schollglas.com> — 2009-12-19T21:30:42Z

In response to Peter Eisentraut :
> 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?

+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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-19T22:04:55Z

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

Re: creating index names automatically?

Ron Mayer <rm_pg@cheapcomplexdevices.com> — 2009-12-20T09:10:36Z

Peter Eisentraut wrote:
> 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.  

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.

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-20T18:58:33Z

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

Re: creating index names automatically?

Michael Glaesemann <grzm@seespotcode.net> — 2009-12-20T19:22:51Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-20T23:02:17Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T03:01:40Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T03:09:13Z

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

Re: creating index names automatically?

Alvaro Herrera <alvherre@commandprompt.com> — 2009-12-23T03:17:31Z

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

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.

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T03:24:13Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T03:31:07Z

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

Re: creating index names automatically?

David E. Wheeler <david@kineticode.com> — 2009-12-23T03:37:01Z

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

Re: creating index names automatically?

Alvaro Herrera <alvherre@commandprompt.com> — 2009-12-23T03:52:08Z

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.

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T03:54:35Z

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

Re: creating index names automatically?

Greg Stark <gsstark@mit.edu> — 2009-12-23T11:37:44Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T14:58:40Z

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

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T16:45:33Z

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

Re: creating index names automatically?

Greg Stark <gsstark@mit.edu> — 2009-12-23T18:01:25Z

On Wed, Dec 23, 2009 at 2:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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.

Well still in the realm of overthinking.... Is there anything to be
gained by having a class of reserved word which can be used for
columns but not relations? I think most of the conflicts we worry
about are with column names, not table names, and reserving names from
use as index names isn't even a standards violation.

-- 
greg

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T18:13:44Z

Greg Stark <gsstark@mit.edu> writes:
?> Well still in the realm of overthinking.... Is there anything to be
> gained by having a class of reserved word which can be used for
> columns but not relations?

If there were more than a single member of the class, I might think
it was worth the trouble ...

			regards, tom lane

Re: creating index names automatically?

Greg Stark <gsstark@mit.edu> — 2009-12-23T18:26:13Z

On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> ?> Well still in the realm of overthinking.... Is there anything to be
>> gained by having a class of reserved word which can be used for
>> columns but not relations?
>
> If there were more than a single member of the class, I might think
> it was worth the trouble ...
>

Er, that was kind of my question. I kind of have the impression that
we've reserved things in the past that were somewhat close decisions
but only because people could conceivably have had columns by those
names but they would never have had tables or indexes by those names.

A quick glace at the list shows relatively few that actually need to
be barred from columns. I could easily see people wanting to use
columns named LEFT and RIGHT or VERBOSE.  I suppose they've been there
for a long time already though.

-- 
greg

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T18:42:36Z

Greg Stark <gsstark@mit.edu> writes:
> On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> If there were more than a single member of the class, I might think
>> it was worth the trouble ...

> A quick glace at the list shows relatively few that actually need to
> be barred from columns. I could easily see people wanting to use
> columns named LEFT and RIGHT or VERBOSE.  I suppose they've been there
> for a long time already though.

Hm.  I do not believe it'd work for LEFT/RIGHT because of join condition
syntax, but it might for ANALYZE, FREEZE, and VERBOSE; which would
actually amount to a pretty significant percentage of our
totally-outside-any-spec reserved words.

I'm still not really eager to introduce yet another category of
keywords, but perhaps it is worth doing.  Comments?

			regards, tom lane

Re: creating index names automatically?

Guillaume Smet <guillaume.smet@gmail.com> — 2009-12-23T19:26:18Z

On Wed, Dec 23, 2009 at 7:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I'm still not really eager to introduce yet another category of
> keywords, but perhaps it is worth doing.  Comments?

If we consider that they can now be a problem in pl/pgsql, it might be
a good idea to consider it for this release.

(I'm thinking of
http://archives.postgresql.org/message-id/17728.1261002579@sss.pgh.pa.us
for instance)

-- 
Guillaume

Re: creating index names automatically?

Robert Haas <robertmhaas@gmail.com> — 2009-12-23T20:15:14Z

On Wed, Dec 23, 2009 at 1:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Greg Stark <gsstark@mit.edu> writes:
>> On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> If there were more than a single member of the class, I might think
>>> it was worth the trouble ...
>
>> A quick glace at the list shows relatively few that actually need to
>> be barred from columns. I could easily see people wanting to use
>> columns named LEFT and RIGHT or VERBOSE.  I suppose they've been there
>> for a long time already though.
>
> Hm.  I do not believe it'd work for LEFT/RIGHT because of join condition
> syntax, but it might for ANALYZE, FREEZE, and VERBOSE; which would
> actually amount to a pretty significant percentage of our
> totally-outside-any-spec reserved words.
>
> I'm still not really eager to introduce yet another category of
> keywords, but perhaps it is worth doing.  Comments?

I'm about to get on a plane, but just to make you hurl here's another
half-assed approach.

...Robert

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T20:52:06Z

Robert Haas <robertmhaas@gmail.com> writes:
> I'm about to get on a plane, but just to make you hurl here's another
> half-assed approach.

Uh ... I don't see what that fixes?  If CONCURRENTLY can be a column
name this is still ambiguous.

			regards, tom lane

Re: creating index names automatically?

Robert Haas <robertmhaas@gmail.com> — 2009-12-23T20:57:57Z

On Dec 23, 2009, at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
>> I'm about to get on a plane, but just to make you hurl here's another
>> half-assed approach.
>
> Uh ... I don't see what that fixes?  If CONCURRENTLY can be a column
> name this is still ambiguous.

How?

...Robert

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-23T21:15:44Z

Robert Haas <robertmhaas@gmail.com> writes:
> On Dec 23, 2009, at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Uh ... I don't see what that fixes?  If CONCURRENTLY can be a column
>> name this is still ambiguous.

> How?

Because CONCURRENTLY can still be reduced as tricky_index_name, so
it still doesn't know how to parse CREATE INDEX CONCURRENTLY ON ...

			regards, tom lane

Re: creating index names automatically?

Robert Haas <robertmhaas@gmail.com> — 2009-12-24T02:41:26Z

On Dec 23, 2009, at 1:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
>> On Dec 23, 2009, at 3:52 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Uh ... I don't see what that fixes?  If CONCURRENTLY can be a column
>>> name this is still ambiguous.
>
>> How?
>
> Because CONCURRENTLY can still be reduced as tricky_index_name, so
> it still doesn't know how to parse CREATE INDEX CONCURRENTLY ON ...

It compiles without warnings for me. There's only one production that  
allows exactly one word between INDEX and ON.

...Robert

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-24T05:07:31Z

Robert Haas <robertmhaas@gmail.com> writes:
> It compiles without warnings for me. There's only one production that  
> allows exactly one word between INDEX and ON.

In that case you broke something.  I'm too tired to work out exactly
what.

			regards, tom lane

Re: creating index names automatically?

Robert Haas <robertmhaas@gmail.com> — 2009-12-25T00:47:32Z

On Thu, Dec 24, 2009 at 12:07 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> It compiles without warnings for me. There's only one production that
>> allows exactly one word between INDEX and ON.
>
> In that case you broke something.  I'm too tired to work out exactly
> what.

Heh.  Well, I almost certainly did, since it wasn't a complete patch
and I didn't test it, but I am not sure that proves that the idea was
bad.  Upthread Greg said:

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

And your response was:

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

But it is merely an accident of the way the grammer happens to be
built that CONCURRENTLY and "concurrently" happen to evaluate to
equivalent values.  It's easy to make a set of productions that treat
them differently, which is what I did here.   It doesn't even require
precedence.  AIUI, there are four constructs that we wish to support:

1. CREATE INDEX ON table (columns);
2. CREATE INDEX CONCURRENTLY ON table (columns);
3. CREATE INDEX index_name ON table (columns);
4. CREATE INDEX CONCURRENTLY index_name ON table (columns);

If we create these as four separate productions, then after shifting
CREATE INDEX CONCURRENTLY and seeing that the next token is ON, we
don't know whether to reduce CONCURRENTLY to index_name or shift.  But
if we unify (2) and (3) into a single production and sort it out when
we reduce the whole statement, then we end up with:

1. CREATE INDEX ON table (columns);
2/3. CREATE INDEX tricky_index_name ON table (columns);
4. CREATE INDEX CONCURRENTLY index_name ON table (columns);

Unless I'm missing something, this eliminates the problem.  Now, after
shifting CREATE INDEX CONCURRENTLY, if the next token is ON, we reduce
(matching case 2/3); otherwise, we shift again (hoping to match case
4).  The remaining problem is to define tricky_index_name in a way
that allows us to distinguish CONCURRENTLY from "concurrently", which
is easy enough to do.

Still another way to solve this problem would be to create a
production called unreserved_keywords_except_concurrently, so that
index_name could be defined not to include CONCURRENTLY without quotes
as one of the possibilities.  But I think this way is cleaner.

Having said all this, I don't really object to the alternate proposal
of creating a set of words that are reserved as relation names but not
as column names, either, especially if it would allow us to make some
other existing keywords less-reserved.  But I don't really understand
the justification for thinking that CONCURRENTLY is OK to make more
reserved, but, say, EXPLAIN would not be OK.  This is one, pretty
marginal production - there's nothing else in the grammar that even
uses CONCURRENTLY, let alone needs it to be reserved.  The whole
problem here comes from what seems like a pretty poor choice about
where to put the word CONCURRENTLY.   It would have been a lot more
robust to put this in a section of the statement where any additional
verbiage was inevitably going to be introduced by a keyword, like just
before or after the storage parameters.

I think what we should learn from this case, as well as the recent
changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to
commands by creating keywords is not very scalable, and that putting
the modifier immediately after the command name is an especially poor
positioning.  Without explicit delimiters, it's easy to get parser
conflicts, and as the number of options grows (even to a relatively
modest value like 2 or 3), the fact that they have to appear in a
fixed order becomes a real pain.

...Robert

Re: creating index names automatically?

Tom Lane <tgl@sss.pgh.pa.us> — 2009-12-25T21:13:21Z

Robert Haas <robertmhaas@gmail.com> writes:
> Having said all this, I don't really object to the alternate proposal
> of creating a set of words that are reserved as relation names but not
> as column names, either, especially if it would allow us to make some
> other existing keywords less-reserved.  But I don't really understand
> the justification for thinking that CONCURRENTLY is OK to make more
> reserved, but, say, EXPLAIN would not be OK.

You're attacking a straw man --- no such comparison was made or
implied.  In practice, if we were up against a situation where we seemed
to need to make EXPLAIN more reserved, we'd consider that and the
alternatives on their own merits, not by reference to whether it should
be more reserved than CONCURRENTLY.  IMO these are always going to be
one-of-a-kind decisions; I feel no desire to propose a hard and fast
rule about them.

The basic problem I've got with kluges such as you proposed is that it's
impossible to explain them to users.  "CONCURRENTLY is unreserved,
except that in the context of a CREATE INDEX target it'll be interpreted
as an option not an index name"?  Ugh.  If we make a separate keyword
category for it, at least we can document that in a reasonably
straightforward fashion: "unreserved (cannot be table name)".

> I think what we should learn from this case, as well as the recent
> changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to
> commands by creating keywords is not very scalable, and that putting
> the modifier immediately after the command name is an especially poor
> positioning.

Perhaps.  The original VACUUM syntax is a pretty bad piece of design,
dating from a time when we didn't even have a clear notion of which
keywords were reserved and which weren't; if it were proposed today
I'm confident we'd notice the problem and reject the syntax.  It's less
obvious that CREATE INDEX CONCURRENTLY was a bad idea.  We did consider
alternative syntaxes and rejected them on (IIRC) the grounds that they
didn't read well.  Even now, the only thing you can really say against
it is that it got in the way of making the index name optional, but
every syntax choice forecloses some other choices.  Complaining because
we didn't have the 20-20 foresight needed to realize that we'd want to
make the index name optional later on isn't very useful.

			regards, tom lane

Re: creating index names automatically?

Robert Haas <robertmhaas@gmail.com> — 2009-12-25T22:27:44Z

On Fri, Dec 25, 2009 at 4:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Having said all this, I don't really object to the alternate proposal
>> of creating a set of words that are reserved as relation names but not
>> as column names, either, especially if it would allow us to make some
>> other existing keywords less-reserved.  But I don't really understand
>> the justification for thinking that CONCURRENTLY is OK to make more
>> reserved, but, say, EXPLAIN would not be OK.
>
> You're attacking a straw man --- no such comparison was made or
> implied.  In practice, if we were up against a situation where we seemed
> to need to make EXPLAIN more reserved, we'd consider that and the
> alternatives on their own merits, not by reference to whether it should
> be more reserved than CONCURRENTLY.  IMO these are always going to be
> one-of-a-kind decisions; I feel no desire to propose a hard and fast
> rule about them.

The particular case of EXPLAIN was discussed previously.  Although I
think we have other ways to work around the problems discussed on that
thread, I came away from that discussion with the impression that you
were categorically opposed to adding any more non-standard reserved
words.  Evidently that's not the case.

> The basic problem I've got with kluges such as you proposed is that it's
> impossible to explain them to users.  "CONCURRENTLY is unreserved,
> except that in the context of a CREATE INDEX target it'll be interpreted
> as an option not an index name"?  Ugh.  If we make a separate keyword
> category for it, at least we can document that in a reasonably
> straightforward fashion: "unreserved (cannot be table name)".

That's a valid concern.  I admitted it was gross right from the start
- I just thought it might be better than having a non-standard
reserved word, especially for such a minor feature.  If it isn't, it
isn't.

>> I think what we should learn from this case, as well as the recent
>> changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to
>> commands by creating keywords is not very scalable, and that putting
>> the modifier immediately after the command name is an especially poor
>> positioning.
>
> Perhaps.  The original VACUUM syntax is a pretty bad piece of design,
> dating from a time when we didn't even have a clear notion of which
> keywords were reserved and which weren't; if it were proposed today
> I'm confident we'd notice the problem and reject the syntax.  It's less
> obvious that CREATE INDEX CONCURRENTLY was a bad idea.  We did consider
> alternative syntaxes and rejected them on (IIRC) the grounds that they
> didn't read well.  Even now, the only thing you can really say against
> it is that it got in the way of making the index name optional, but
> every syntax choice forecloses some other choices.  Complaining because
> we didn't have the 20-20 foresight needed to realize that we'd want to
> make the index name optional later on isn't very useful.

I wasn't intending to engage in pointless bellyaching.  What I was
trying to do was point out that there are some common problems in all
of these cases, and trying to extract a design principle.  I'm not
really sure why CREATE INDEX [CONCURRENTLY] is any different from
VACUUM [FULL] [FREEZE] [ANALYZE].  In both cases, the command and its
modifiers are immediately followed by a name, without any intervening
keyword or punctuation.  In retrospect, that doesn't seem like a good
choice, at least to me, so, it might be something to look out for in
the future.  YMMV, of course.

...Robert

Re: creating index names automatically?

tomas@tuxteam.de — 2009-12-26T06:24:29Z

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, Dec 25, 2009 at 05:27:44PM -0500, Robert Haas wrote:
> On Fri, Dec 25, 2009 at 4:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:

[...]

> >> I think what we should learn from this case, as well as the recent
> >> changes to EXPLAIN, COPY, and VACUUM syntax, is that adding options to
> >> commands by creating keywords is not very scalable, and that putting
> >> the modifier immediately after the command name is an especially poor
> >> positioning.
> >
> > Perhaps.  The original VACUUM syntax is a pretty bad piece of design,

[...]

> I wasn't intending to engage in pointless bellyaching.  What I was
> trying to do was point out that there are some common problems in all
> of these cases, and trying to extract a design principle.  I'm not
> really sure why CREATE INDEX [CONCURRENTLY] is any different from
> VACUUM [FULL] [FREEZE] [ANALYZE].  In both cases, the command and its
> modifiers are immediately followed by a name, without any intervening
> keyword or punctuation.  In retrospect, that doesn't seem like a good
> choice, at least to me, so, it might be something to look out for in
> the future.  YMMV, of course.

I have to concur with Robert here. There will be always a need to add
(PostgreSQL-specific, non-standard) modifiers. Having a "syntactical
place" where to put them without forcing us to introduce new
(non-standard) keywords or semi-keywords seems like a Good Thing.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFLNawdBcgs9XrR2kYRAnigAJ99c6dMhgk30hYK29ci0+WyXXCKzgCfV+c2
HLCy7BEvQYwWySMVI5n6LE0=
=PpCd
-----END PGP SIGNATURE-----