Обсуждение: DDL statement blocked on long-running query with no runtime dependancy
Hi all,
I’ve just noticed that some DDL statements will block if there are other queries accessing the relation in question, even where those queries are not dependant on the underlying structures (such as a new index, created after the initial query).
For instance…
SELECT long_running_query FROM foo; -- Query A,-- meanwhile…CREATE UNIQUE INDEX ON foo USING btree (blah);-- Fine, no problemDROP INDEX foo_blah_idx;-- The above DDL statement will block until Query A completes, even though the query cannot possibly rely on the newly created index
Is this behaviour by design? Is it worth allowing DDL statements to occur where it can be determined that no currently running query will be affected?
Cheers,
Tim
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
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@sss.pgh.pa.us>
Date: Tuesday, 6 May 2014 15:05
To: Tim Kane <tim.kane@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] DDL statement blocked on long-running query with no runtime dependancy
Date: Tuesday, 6 May 2014 15:05
To: Tim Kane <tim.kane@gmail.com>
Cc: pgsql-general General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] DDL statement blocked on long-running query with no runtime dependancy
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