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

Michael Paquier <michael.paquier@gmail.com> writes:
> On Wed, Aug 2, 2017 at 12:39 PM,  <mba.ogolny@gmail.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs