Re: Support for REINDEX CONCURRENTLY

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
Cc: Andres Freund <andres(at)2ndquadrant(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Support for REINDEX CONCURRENTLY
Date: 2012-10-04 01:00:27
Message-ID: 11562.1349312427@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

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.

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?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2012-10-04 01:18:58 Re: PQping command line tool
Previous Message Tom Lane 2012-10-04 00:29:24 bison location reporting for potentially-empty list productions