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@xxxxxxxxxxxxx> Date: Tuesday, 6 May 2014 15:05 To: Tim Kane <tim.kane@xxxxxxxxx> Cc: pgsql-general General <pgsql-general@xxxxxxxxxxxxxx> Subject: Re: DDL statement blocked on long-running query with no runtime dependancy Tim Kane <tim.kane@xxxxxxxxx> writes:
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 |
