Subject: Re: REINDEX and blocking SELECT queries
To: Tom Lane <tgl@sss.pgh.pa.us>, Satoshi Nagayasu <snaga@uptime.jp>
References: 
 <CAA8sozcK9eOo0Cj0ufBpAYi=oL2cJ-T9T7JN-_CqpYSWZ0GDcg@mail.gmail.com>
 <19413.1473428496@sss.pgh.pa.us>
Cc: pgsql-docs@postgresql.org
From: Vik Fearing <vik@2ndquadrant.fr>
Message-ID: <ecf2cf36-9fa9-e08c-e1f1-c603ae90426b@2ndquadrant.fr>
Date: Fri, 9 Sep 2016 15:52:20 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.2.0
MIME-Version: 1.0
In-Reply-To: <19413.1473428496@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org

On 09/09/2016 03:41 PM, Tom Lane wrote:
> Satoshi Nagayasu <snaga@uptime.jp> writes:
>> According to the manual, running REINDEX does not take any locks
>> on the parent table which block read operations.
>> Actually, REINDEX blocks SELECT queries, maybe in the planning phase.
> 
> Hm.  REINDEX does take out only ShareLock on the table, which would not
> block DML, but it takes out AccessExclusiveLock on the index.  That
> blocks the planner's attempts to acquire information about the table's
> indexes.
> 
> In the case of an update query I think there's little we can do about
> this; the executor would have to update the index anyway.  For a pure
> SELECT, you could imagine having the planner do a conditional lock acquire
> and ignore the index if that fails.  Would that be better than blocking?
> Not sure.  You could end up with a really bad plan if the index was
> critical for efficient processing of the query.

I agree, things could get awful without certain indexes (I'm thinking of
partial indexes in particular).

Also, the very next sentence after the one cited says that a SELECT will
block if it tries to use the index in question, so I'm not even sure
there's anything at all to do here.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


-- 
Sent via pgsql-docs mailing list (pgsql-docs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs