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