Re: [HACKERS] Simultaneous index creates on different schemas cause deadlock?

On 2013-04-25 13:17:31 -0400, Tom Lane wrote:
> Paul Hinze <paul(dot)t(dot)hinze(at)gmail(dot)com> writes:
> > [ multiple CREATE INDEX CONCURRENTLY commands will deadlock with each other ]
>
> Hm. I guess the reason nobody noticed this before now is that generally
> the idea with CREATE INDEX CONCURRENTLY is to minimize the impact on
> system load, hence you wouldn't do more than one at a time. Still, it's
> surely a POLA violation that you *can't* do more than one at a time.
>
> The cause is that each one will wait for all older snapshots to be
> gone --- and it does that before dropping its own snapshot, so that the
> other ones will see it as something to be waited out too.

Makes sense.

> Since we know that C.I.C. executes in its own transaction, and there
> can't be more than one on the same table due to locking, it seems to me
> that it'd be safe to drop our own snapshot before waiting for other
> xacts to end. That is, we could just rearrange the last few steps in
> DefineIndex(), taking care to save snapshot->xmin before we destroy the
> snapshot so that we still have that value to pass to
> GetCurrentVirtualXIDs().
>
> Anybody see a flaw in that solution?

Except that it still will unnecessarily wait for other CICs, just not
deadlock, I don't see a problem. We could have a PROC_IN_CIC flag or
something so we can ignore other index creations, but I am not sure if
its worth the complication.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services