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

Re: Support for REINDEX CONCURRENTLY

From: Andres Freund <andres(at)2ndquadrant(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Greg Stark <stark(at)mit(dot)edu>
Subject: Re: Support for REINDEX CONCURRENTLY
Date: 2012-10-03 11:22:22
Message-ID: 201210031322.22641.andres@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wednesday, October 03, 2012 01:15:27 PM Michael Paquier wrote:
> On Wed, Oct 3, 2012 at 8:08 PM, Andres Freund <andres(at)2ndquadrant(dot)com>wrote:
> > On Wednesday, October 03, 2012 12:59:25 PM Greg Stark wrote:
> > > Just for background. The showstopper for REINDEX concurrently was not
> > > that it was particularly hard to actually do the reindexing. But it's
> > > not obvious how to obtain a lock on both the old and new index without
> > > creating a deadlock risk. I don't remember exactly where the deadlock
> > > risk lies but there are two indexes to lock and whichever order you
> > > obtain the locks it might be possible for someone else to be waiting
> > > to obtain them in the opposite order.
> > > 
> > > I'm sure it's possible to solve the problem. But the footwork needed
> > > to release locks then reobtain them in the right order and verify that
> > > the index hasn't changed out from under you might be a lot of
> > > headache.
> > 
> > Maybe I am missing something here, but reindex concurrently should do
> > 1) BEGIN
> > 12) drop old index
> 
> The code I sent already does that more or less btw. Just that it can be
> more simplified...
The above just tried to describe the stuff thats relevant for locking, maybe I 
wasn't clear enough on that ;)

> > I don't see where the deadlock danger is hidden in that?
> > I didn't find anything relevant in a quick search of the archives...
> 
> About the deadlock issues, do you mean the case where 2 sessions are
> running REINDEX and/or REINDEX CONCURRENTLY on the same table or index in
> parallel?
No idea. The bit about deadlocks originally came from Greg, not me ;)

I guess its more the interaction with normal sessions, because the locking 
used (SHARE UPDATE EXLUSIVE) prevents another CONCURRENT action running at the 
same time. I don't really see the danger there though because we should never 
need to acquire locks that we don't already have except the final 
AccessExclusiveLock but thats after we dropped other locks and after the index 
is made unusable.

Greetings,

Andres
-- 
Andres Freund		http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


In response to

pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2012-10-03 11:39:14
Subject: Re: ToDo: allow to get a number of processed rows by COPY statement [Review of Patch]
Previous:From: Michael PaquierDate: 2012-10-03 11:15:27
Subject: Re: Support for REINDEX CONCURRENTLY

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