Re: REINDEX and blocking SELECT queries

2016-09-09 22:41 GMT+09:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.

Sorry if I didn't explain my consideration well.

In short, I would like to say, "REINDEX TABLE pgbench_accounts" would block
"SELECT count(*) FROM pgbench_accounts".
That's the situation what many of users don't expect from the manual.

For example,

SessionA> BEGIN;
SessionA> REINDEX TABLE pgbench_accounts;
SessionB> SELECT count(*) FROM pgbench_accounts; -- This statement
would be blocked by the REINDEX and the locks.

Many people understand that "REINDEX does not block read (SELECT) operations"
according to the manual. That seems misunderstanding.

So, I would like to improve the explanation of REINDEX and locks.
At least, I think we should add some explanation about the planning
phase would touch
the index, and it could be blocked by REINDEX.

> 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.

Well, I would not intend to modify the implementation for now.
I just notice the users that REINDEX could block even SELECT statements in
the official documents because it is very critical for web services.

Regards,
--
Satoshi Nagayasu <snaga(at)uptime(dot)jp>