this is my first post to this mailing list, so if this is the wrong
list, direct me to the right one please.
also, please cc: to me because I am not (yet) subscribed.
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.
2. If (1) is correct, then I have found a problem. I have a stored
procedure that takes a few seconds to run. If, while it's running, an
index is dropped, it bails with 'Relation [OID of the index] does not
exist'. I do not know if the same is true with long-running query, or
just stored procedures.
I can understand that postgres created a plan for a query that relied on
an index, and then half-way through it's execution the index
disappeared. But in my understanding it shouldn't bail like that.
It should either
(1) 'lock index' - do not allow to drop the index until all queries that
are already planned and need that index are done executing. For all the
new queries that are planned while the index is 'locked' like this the
index is unavailable, and they'll have to do sequential scan/whatever.
(2) make the query execution 'smarter' - if an index we were relying on
disappears, instead of throwing 'Relation 12345 does not exist' re-plan
the query and make it do sequential scan or whatever else is available
instead of the index. I don't know if that's feasible
(3) make statements like 'DROP/CREATE INDEX' obey a transaction scope -
e.g.
BEGIN;
DROP INDEX "blah";
...
[do a lot of work here]
...
CREATE INDEX "blah"...
COMMIT;
will never leave a concurrently running query without an index. I do not
know if this is against some principles that are deep within postgres'
foundation though.
My complaint is that I can't drop an index without risking to bomb a
long-running stored procedure (option (2) above wouldn't be very helpful
either, but at least we won't bomb!); on the other hand I can't do
massive inserts and do them fast (faster than a couple hours!) without
dropping the index. At least without a ugly hack.
Can someone tell me if this request of mine makes sense?
Vlad
--
Vlad Krupin
Software Engineer
echospace.com
