Re: Indexes vs. cache flushes

On Thu, Jan 19, 2006 at 02:46:11AM -0500, Tom Lane wrote:
> The concerns that I find more interesting are changes in the underlying
> objects. We don't have an ALTER OPERATOR CLASS, much less an ALTER
> ACCESS METHOD, but it's certainly theoretically possible to change the
> definition of a support function used by an index. There isn't
> presently any mechanism to force timely propagation of such a change,
> and so you'd be largely on your own --- but realistically, wouldn't such
> a change require rebuilding the index anyway?

I wondered about the same problem when dealing with the collation
stuff. If you change anything about a collation, you essentially have
to invalidate any indexes, plans or views using it. Like you say,
there's isn't really a way of doing this.

This isn't the first time I've wondered about a flag on the an index
stating "broken, pending rebuild". If one of these details changes, we
really need to stop using the indexes ASAP until they're back into a
consistant state. The only question remaining is when to do the
rebuild: you can either wait for manual intervention or (probably
better) simply do the REINDEXes in the same transaction as the ALTER
OPERATOR CLASS et al.

The locking would be painful, perhaps a better way would be to clone
the opclass, clone the indexes with the new opclass, build them and
then drop the old indexes. Once you've rebuilt the views and indexes,
simply delete the old opclass next database restart since plans don't
survive DB restart, right?. (Isn't this Read-Copy-Update style
locking?).

Have a nice day,

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.