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

On Thu, Jun 20, 2019 at 9:54 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>
>

Can't really provide a thorough code review, but I did apply the patch to
the base 11.4 code (not HEAD from github) and the compound ALTER table
statement that was failing before now works without error. Thank you for
the quick fix!

--
Keith Fiske
Senior Database Engineer
Crunchy Data - http://crunchydata.com