Re: REINDEX and blocking SELECT queries

On 09/09/2016 03:41 PM, Tom Lane wrote:
> Satoshi Nagayasu <snaga(at)uptime(dot)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