Horton

Re: BUG #15865: ALTER TABLE statements causing "relation already exists" errors when some indexes exist

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: keith.fiske@crunchydata.com, pgsql-bugs@lists.postgresql.org
Date: 2019-06-21T16:47:55Z
Lists: pgsql-bugs, pgsql-hackers 
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.

			regards, tom lane

Commits

  1. Simplify psql \d's rule for ordering the indexes of a table.

  2. Purely-cosmetic adjustments in tablecmds.c.

  3. Further fix ALTER COLUMN TYPE's handling of indexes and index constraints.

  4. Fix ALTER COLUMN TYPE failure with a partial exclusion constraint.