Re: 'DROP INDEX' kills stored rpocedures

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.

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?

Any hints?

Vlad

--
Vlad Krupin
Software Engineer
echospace.com