Re: 'DROP INDEX' kills stored rpocedures

On Thu, 3 Apr 2003, Vlad Krupin wrote:

> Thanks for response, Scott
>
> scott.marlowe wrote:
> [snip]
>
> >>1. I understand that working with indexes is outside the scope of
> >>transaction. That is, if you start a transaction, then drop an index, at
> >>that very moment, before the transaction is committed, the index will be
> >>unavailable to any other concurrently running queries. I didn't find
> >>that in documentation, but a small experiment showed that to be true.
> >>
> >>
> >
> >Don't make assumptions like that. In postgresql, DDL is transactionable.
> >
> >begin;
> >drop index test;
> >create index test on table (field);
> >commit;
> >
> >will work just fine.
> >
> That's not really an assumption on my part. That's what I have observed
> by doing a small experiment. Consider two clients: #1 and #2 that are
> connected at the same time. Table "foo" is indexed on "bar". Now,
> 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.

> Does that seem to make sense? Why does it behave like that?
>
> Also, I am still trying to figure out why I see my stored procedure
> bailing with 'Relation [OID of index dropped] does not exist' error. Is
> that because the planner somehow remembers that there used to be an
> index with that OID, but, since I dropped and re-created it, it's not
> there anymore and I need to tell the planner to re-analyze how to
> execute that query, e.g. 'VACUUM ANALYZE'? Or am I totally off track here?

Not sure. Might be one of things that you just can't do, run both the
index change and your stored proc. Can we see the source of your stored
procedure?