Re: Improving the names generated for indexes on expressions

On Tue, Sep 16, 2025 at 8:32 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Question is, why should we care about that?

It's a fair question, and I can't think of any hard-and-fast reason.
However, I suspect that some users may not like it; the quick -1 from
Pavel lends credence to that theory, IMHO. People do use DDL commands
to operate on indexes, so it's not crazy that quoting could be a
hassle. AFAICT, there's no precedent for the exact thing you've done
here: e.g. the column alias for generate_series(1,10) ends up as the
function name, without the arguments. On the other hand, I confess I'm
not sure we've made the right decision there: limiting it to just the
function name can help to keep the length reasonable, but it can also
often make the column name pretty meaningless, as when the expression
was something like round(interesting_calculation(whatever)) and you
end up with "round" as the column name.

Do you think there's any way of doing this usefully while not
autogenerating names that require quoting, or is that a hopeless
endeavor? If it's hopeless, is it better to accept autogenerated names
that require quoting, or is it better to solve the problem on the
other thread with something more like what you proposed there?

Maybe those are questions that you were hoping I was going to have an
answer to, so I'll tell you my bias. I would be inclined to smush any
series of quote-requiring characters that appear in the expression
down to a single underscore. If that results in name collisions, then
the user should consider specifying names for each partition
themselves, or writing the index expressions so they're less similar,
or not concurrently creating indexes on near-identical expressions.
Maybe that's too harsh a position, but this patch with that change
would still solve the complained-of case (because the alphanumeric
constants differ) and would I believe considerably improve the
intelligibility of index names derived from expression indexes. As
previously stated, I like distinguishing different index expressions
more than I like revising the naming convention for partition-child
indexes vs. indexes created directly on a child table. But of course,
these are all arguable positions.

--
Robert Haas
EDB: http://www.enterprisedb.com