Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Fri, Dec 25, 2009 at 4:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
-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 t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Thu, Dec 24, 2009 at 12:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
I wrote: Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 3:54 AM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 2:58 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 7:42 PM, Tom Lane t...@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.1261002...@sss.pgh.pa.us for instance) -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Wed, Dec 23, 2009 at 1:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark gsst...@mit.edu writes: On Wed, Dec 23, 2009 at 6:13 PM, Tom Lane t...@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 gross.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Dec 23, 2009, at 3:52 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Robert Haas robertmh...@gmail.com writes: On Dec 23, 2009, at 3:52 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
On Dec 23, 2009, at 1:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Dec 23, 2009, at 3:52 PM, Tom Lane t...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Tom Lane wrote: Peter Eisentraut pete...@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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Alvaro Herrera alvhe...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Michael Glaesemann g...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 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? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
2009/12/20 Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
Peter Eisentraut pete...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] creating index names automatically?
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
