Improve the names generated for indexes on expressions. If the user doesn't specify a name for an index, it's generated based on the names chosen for the index columns (which the user has no direct control over). For index columns that are just columns of the base relation, the index column name is the same as the base column name; but for index columns that are expressions, it's less clear what to do. Up to now, what we have done is equivalent to the heuristics used to choose SELECT output column names, except that we fall back to "expr" not "?column?" in the numerous cases that FigureColname doesn't know what to do with. This is not tremendously helpful. More, it frequently leads to collisions of generated index names, which we can handle but only at the cost of user confusion; also there's some risk of concurrent index creations trying to use the same name. Let's try to do better.
Messing with the FigureColname heuristics would have a very large blast radius, since that affects the column headings that applications see. That doesn't seem wise, but fortunately SQL queries are seldom directly concerned with index names. So we should be able to change the index-name generation rules as long as we decouple them from FigureColname. The method used in this patch is to dig through the expression, extract the names of Vars, the string representations of Consts, and the names of functions, and run those together with underscores between. Other expression node types are ignored but descended through. We could work harder by handling more node types, but it seems like this is likely to be sufficient to arrive at unique index names in many cases. Notably, this rule ignores the names of operators, for example both "a + b" and "a * b" will be rendered as "a_b". This choice was made to reduce the probability of having to double-quote the index name. I've also chosen to strip Const representations down to only alphanumeric characters (plus non-ASCII characters, which our parser treats as alphabetic anyway). So for example "x + 1.0" would be represented as "x_10". This likewise avoids possible quoting problems. I also considered limiting how many characters we'd take from each Const, but didn't do that here. We might tweak these rules some more after we get some experience with this patch. It's being committed at the start of a development cycle to provide as much time as possible to gather feedback. Author: Tom Lane <[email protected]> Reviewed-by: Robert Haas <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/181b6185c79e09e6ac94428189d9afac807244ac Modified Files -------------- contrib/seg/expected/partition.out | 2 +- src/backend/commands/indexcmds.c | 137 ++++++++++++++++++- src/backend/parser/parse_target.c | 16 --- src/backend/parser/parse_utilcmd.c | 6 +- src/include/nodes/parsenodes.h | 3 +- src/include/parser/parse_target.h | 1 - src/test/regress/expected/alter_table.out | 6 +- src/test/regress/expected/create_index.out | 18 +-- src/test/regress/expected/create_table.out | 4 +- src/test/regress/expected/create_table_like.out | 8 +- src/test/regress/expected/indexing.out | 168 ++++++++++++------------ src/test/regress/expected/inherit.out | 114 ++++++++-------- src/test/regress/expected/rangetypes.out | 6 +- src/test/regress/expected/stats_import.out | 16 +-- src/test/regress/sql/create_index.sql | 2 +- src/test/regress/sql/indexing.sql | 18 +-- src/tools/pgindent/typedefs.list | 1 + 17 files changed, 316 insertions(+), 210 deletions(-)
