Re: DDL statement blocked on long-running query with no runtime dependancy

Thanks Tom, I see there are a lot of considerations. I guess I just found it
to be an interesting scenario, although not entirely unexpected. It’s not a
big deal of course.

Cheers,

Tim

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Date: Tuesday, 6 May 2014 15:05
To: Tim Kane <tim(dot)kane(at)gmail(dot)com>
Cc: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] DDL statement blocked on long-running query with no
runtime dependancy

Tim Kane <tim(dot)kane(at)gmail(dot)com> writes:
> [ DROP INDEX requires exclusive lock on index's table ]

> Is this behaviour by design?

Yes. Even if you assumed that the DROP had complete information about
all concurrent queries (which it does not, and we could not cheaply
provide that), it would still be possible for an incoming query to start
using the index immediately after DROP looks. Table-level locks are the
only means we have to prevent such race conditions.

In theory we could narrow the scope of the exclusive locking to just
the targeted index, but that would not actually help much in practice:
all incoming queries would still need to conflict with the DROP, because
the planner will want to inspect every index on the table to see if it's
potentially useful for the new query.

Recent versions of PG do have a DROP INDEX CONCURRENTLY operation, which
goes through a multiple-transaction sequence involving marking the index
invalid, and a lot of waiting, in order to avoid blocking other
transactions. I'm not sure that that answers your concern though, as
what you seem to want is for the DROP to not wait.

regards, tom lane