Re: DDL statement blocked on long-running query with no runtime dependancy - Mailing list pgsql-general

From Tom Lane
Subject Re: DDL statement blocked on long-running query with no runtime dependancy
Date
Msg-id 17181.1399385132@sss.pgh.pa.us
Whole thread Raw
In response to DDL statement blocked on long-running query with no runtime dependancy  (Tim Kane <tim.kane@gmail.com>)
Responses Re: DDL statement blocked on long-running query with no runtime dependancy  (Tim Kane <tim.kane@gmail.com>)
List pgsql-general
Tim Kane <tim.kane@gmail.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


pgsql-general by date:

Previous
From: Marcus Engene
Date:
Subject: copy expensive local view to an RDS instance
Next
From: Leif Jensen
Date:
Subject: Server process crash - Segmentation fault