Re: 'DROP INDEX' kills stored rpocedures

"scott.marlowe" <scott.marlowe@ihs.com> writes:
> On Thu, 3 Apr 2003, Vlad Krupin wrote:
>> consider this sequence of commands:
>>
>> #1 BEGIN;
>> #2 BEGIN;
>> #2 DROP INDEX "bar_idx";
>> #1 EXPLAIN ANALYZE SELECT * FROM "foo" WHERE "bar"='hello';
>>
>> This performs a sequential scan for me, even though I have not committed
>> the transaction on client #2 yet! If I do not drop the index (no #2
>> statements), it performes an indexed scan.

> On my 7.2.x box, this results in #1 waiting for #2 to commit.  It just
> pauses #1 indefinitely.  Are you running 7.3.x?  Might explain the
> differences.

I overlooked this part of Vlad's message.  AFAIK the above should cause #1
to wait for #2's commit in *any* version of Postgres; certainly anything
released in the last several years.  DROP INDEX will take an exclusive
lock on the table owning the index, and that will prevent EXPLAIN from
accessing the table even just to plan a query on it.

[ thinks... ]  Um, Scott and I are both assuming that bar_idx is indeed
an index on table foo.  Perhaps this was just pilot error about what
index belonged to what table?

            regards, tom lane