Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group