Re: BUG #14768: CREATE INDEX CONCURRENTLY IF NOT EXISTS cancels autovacuum even if the index already exists.

Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
> On Wed, Aug 2, 2017 at 12:39 PM, <mba(dot)ogolny(at)gmail(dot)com> wrote:
>> I am perfectly aware of the fact that CREATE INDEX CONCURRENTLY on a table
>> cancels a running autovacuum process on that table.
>> But CREATE INDEX CONCURRENTLY IF NOT EXISTS should take
>> ShareUpdateExclusiveLock only after checking that the index doesn't exist.

> Logically the checks in index_create could happen in DefineIndex() as
> there is no if_not_exists logic for toast indexes. But do we want to
> skip all the sanity checks done before that, particularly for
> exclusion constraints with concurrent creation?

I'm afraid this complaint is just wishful/sloppy thinking. It's useless
to perform an "index doesn't exist" check without holding a lock that's
sufficient to prevent such an index from being created by a concurrent
transaction. There is no lock level less than SHARE UPDATE EXCLUSIVE
that would prevent that; and even if there was, taking that level to
make the check and then upgrading to SHARE UPDATE EXCLUSIVE would
constitute a deadlock risk in itself.

Perhaps the OP's problem --- which he failed to state exactly, but
I suppose can be written as "I wish a failed CREATE INDEX CONCURRENTLY
didn't kill a concurrent autovacuum before failing" --- could be resolved
by subdividing SHARE UPDATE EXCLUSIVE into more than one lock level.
But that's not exactly a trivial change. And it's not very clear why
this is such a big problem that we need to be making a delicate redesign
of the locking logic to avoid it. Autovacuum cancels are pretty routine,
while I'm having a hard time understanding why index builds would happen
so often that they'd lock out autovacuum for problematic amounts of time.

regards, tom lane