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

Re: Support for REINDEX CONCURRENTLY

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: Andres Freund <andres(at)2ndquadrant(dot)com>
Cc: Greg Stark <stark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Support for REINDEX CONCURRENTLY
Date: 2012-10-03 11:15:27
Message-ID: CAB7nPqT6TjA=V2SAFynhWWtB5ugnF+MN5usptOEd0ULj16CkMQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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
> 2) Lock table in share update exlusive
> 3) lock old index
> 3) create new index
> 4) obtain session locks on table, old index, new index
> 5) commit

Build new index.

> 6) process till newindex->insisready (no new locks)
>
validate new index

> 7) process till newindex->indisvalid (no new locks)
>
Forgot the swap old index/new index.

> 8) process till !oldindex->indisvalid (no new locks)
> 9) process till !oldindex->indisready (no new locks)
> 10) drop all session locks
> 11) lock old index exclusively which should be "invisible" now
> 12) drop old index
>
The code I sent already does that more or less btw. Just that it can be
more simplified...


> 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?
-- 
Michael Paquier
http://michael.otacoo.com

In response to

Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2012-10-03 11:22:22
Subject: Re: Support for REINDEX CONCURRENTLY
Previous:From: Andres FreundDate: 2012-10-03 11:08:24
Subject: Re: Support for REINDEX CONCURRENTLY

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