Re: REINDEX and blocking SELECT queries

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

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@uptime.jp>