Weird index ordering in psql's \d (was Re: BUG #15865: ALTER TABLE statements causing "relation already exists" errors when some indexes exist)

I wrote:
>> BTW, has anyone got an explanation for the order in which psql is
>> listing the indexes of "anothertab" in this test case?

> Ah, here's the explanation: describe.c is sorting the indexes
> with this:
> "ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;"
> I can see the point of putting the pkey first, I guess, but the preference
> for uniques second seems pretty bizarre, especially since
> (a) it doesn't distinguish unique constraints from plain unique indexes and
> (b) there's no similar preference for exclusion constraints, even though
> those might be morally equivalent to a unique constraint.
> What do people think of dropping the indisunique sort column here?
> Obviously not back-patch material, but it might be more sensible
> behavior going forward.

Here's a proposed patch that does this. The changes it causes in the
existing regression test results seem to be sufficient illustration,
so I didn't add new tests. There is of course no documentation
touching on this point ...

With the patch, psql's rule for listing indexes is "pkey first, then
everything else in name order". The traditional rule is basically
crazytown IMO when you consider mixes of unique constraints and plain
(non-constraint-syntax) indexes and exclusion constraints.

A different idea that might make it slightly less crazytown is to
include exclusion constraints in the secondary preference group, along
the lines of
"ORDER BY i.indisprimary DESC, i.indisunique|i.indisexclusion DESC, c2.relname;"
This'd restore what I think was the original design intention, that
the secondary preference group includes all indexes that impose
constraints on what the table can hold. But this'd be doubling down
on what I think is fundamentally not a very good idea, so I didn't
pursue it.

Alternatively we could go further and drop the pkey preference too,
making it pure index name order, but I don't feel a need to do that.

Thoughts?

regards, tom lane