Re: Possible patch for better index name choosing

On Mon, Dec 21, 2009 at 12:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Sun, Dec 20, 2009 at 10:17 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Attached is a WIP patch for addressing the problems mentioned in this
>>> thread:
>>> http://archives.postgresql.org/pgsql-hackers/2009-12/msg01764.php
>
>> I'm not really sure there's any point to this.  Anyone who cares about
>> giving their index an intelligible name should manually assign one.
>> If they don't bother doing that, I don't really see why we should
>> worry about it either.
>
> Mainly because we historically *have* put some work into it, and it
> would be inconsistent to not pay attention to the point now as we extend
> the set of possible index-building constraints further.  In particular
> we're going to see a lot of exclusion constraints named foo_exclusionN
> if we don't expend any effort on it now.

Maybe that's worth fixing and maybe it isn't. My first reaction is
"so what"? In all likelihood, you're going to have to look at the
index definition to see what the thing does anyway.

> I also claim that this is
> necessary infrastructure if we are going to accept Peter's proposal of
> allowing CREATE INDEX without an explicit index name.  That is really
> dependent on the assumption that the system will expend more than no
> effort on picking useful names.

That's a point to consider, though perhaps if they aren't specifying a
name it means they don't care that much.

>> Maybe I'll reserve final judgement pending further discussion, but my
>> first reaction is to say it's not worth the risk.  Probably this
>> shouldn't be an issue for a well-designed application, but the world
>> is full of badly-written code.  We shouldn't throw up barriers (even
>> relatively trivial ones) to updating applications unless we get
>> something out of it, and I'm not convinced that's the case here.
>
> Well, we could tamp down the risks considerably if we undid my point
> (1), namely to still consider only the first index column when
> generating a name.  I am not really happy with that answer though.
> I could turn your first point back on you: if an app is concerned about
> the exact names assigned to indexes, why isn't it specifying them?
>
> It's worth noting that pg_dump does preserve index names, so this isn't
> going to be an issue in any case for existing apps that dump and reload
> their databases.  AFAICS the only case where it would actually create a
> compatibility issue is if an existing app creates multi-column UNIQUE
> (non-PKEY) constraints on-the-fly, without a constraint name, and
> depends on the generated name being the same as before.

Right. Imagine, for example, a poorly written initialization script
for an app. Existing instances that are dumped and reloaded will be
OK, but new instances might not come out as expected.

I don't think that what you're proposing here is completely stupid;
I'm just wondering if it's not an ultimately somewhat pointless
activity. I'm not convinced that it's possible or sensible to try to
stringify all the things that people put in their index definitions,
or that we're going to be able to do it well enough to really add any
value. Perhaps I should RTFP before sticking my neck out too far,
but... will you serialize EXCLUDE (a =), EXCLUDE (a &&), and EXCLUDE
(a <some other operator>) differently? And if so, do you expect the
user to be able to reconstruct what the constraint is doing by looking
at the serialized version? It seems like something reasonably sane
can be done when the definition uses mostly column names and
functions, but operators seem like more of a problem. I think mostly
people are going to see the constraint name that got violated and then
run \d on the table and look for it. foo_exclusion3 may not be very
informative, but it's easy to remember for long enough to find it in
the \d output, whereas something long and hairy may not be.

...Robert