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-21T01:54:46Z
Lists: pgsql-bugs, pgsql-hackers

Attachments

I wrote:
>> Yeah, obviously I fat-fingered something there.  Looking ...

After further review it seems like I was led into this error by a siren
singing something about how we could skip collecting the index definition
string for an index we were going to ignore later.  (Cue standard lecture
about premature optimization...)  That absolutely *does not* work, because
we might not find out till we're considering some later ALTER TYPE
subcommand that the index depends on a relevant constraint.  And we have
to capture the index definition before we alter the type of any column it
depends on, or pg_get_indexdef_string will get very confused.  That little
dependency wasn't documented anywhere.  I also found a pre-existing
comment that contradicted the new reality but I'd missed removing in
e76de8861.

Here's a patch against HEAD --- since I'm feeling more mortal than usual
right now, I'll put this out for review rather than just pushing it.
It might be easier to review the code changes by just ignoring e76de8861
and diffing against tablecmds.c from before that, as I've done in the
second attachment.

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

			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.