Re: Support for REINDEX CONCURRENTLY

On 2012/10/04, at 10:00, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
>> On Wed, Oct 3, 2012 at 5:10 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:
>> 14) Swap new and old indexes, consisting here in switching their names.
>>> I think switching based on their names is not going to work very well
>>> because
>>> indexes are referenced by oid at several places. Swapping
>>> pg_index.indexrelid
>>> or pg_class.relfilenode seems to be the better choice to me. We expect
>>> relfilenode changes for such commands, but not ::regclass oid changes.
>
>> OK, if there is a choice to be made, switching relfilenode would be a
>> better choice as it points to the physical storage itself. It looks more
>> straight-forward than switching oids, and takes the switch at the root.
>
> Andres is quite right that "switch by name" is out of the question ---
> for the most part, the system pays no attention to index names at all.
> It just gets a list of the OIDs of indexes belonging to a table and
> works with that.
Sure. The switching being done by changing the index name is just the direction taken by the first version of the patch, and only that. I just wrote this version without really looking for a bulletproof solution but only for something to discuss about.

>
> However, I'm pretty suspicious of the idea of switching relfilenodes as
> well. You generally can't change the relfilenode of a relation (either
> a table or an index) without taking an exclusive lock on it, because
> changing the relfilenode *will* break any concurrent operations on the
> index. And there is not anyplace in the proposed sequence where it's
> okay to have exclusive lock on both indexes, at least not if the goal
> is to not block concurrent updates at any time.
Ok. As the goal is to allow concurrent operations, this is not reliable either. So what is remaining is the method switching the OIDs of old and new indexes in pg_index? Any other candidates?

>
> I think what you'd have to do is drop the old index (relying on the
> assumption that no one is accessing it anymore after a certain point, so
> you can take exclusive lock on it now) and then rename the new index
> to have the old index's name. However, renaming an index without
> exclusive lock on it still seems a bit risky. Moreover, what if you
> crash right after committing the drop of the old index?
>
> I'm really not convinced that we have a bulletproof solution yet,
> at least not if you insist on the replacement index having the same name as the original. How badly do we need that?
And we do not really need such a solution as I am not insisting on the method that switches indexes by changing names. I am open to a reliable and robust method, and I hope this method could be decided in this thread.

Thanks for those arguments, I am feeling it is really leading the discussion to the good direction.

Thanks.

Michael