Re: REINDEX locking

On 2019-Jun-13, Robert Haas wrote:

> Typing "COMMIT;" or "ROLLBACK;" in S1 unblocks the reindex and it
> succeeds, but otherwise it doesn't, contrary to the claim that a
> regular REINDEX does not block reads.  The reason for this seems to be
> that the REINDEX acquires AccessExclusiveLock on all of the indexes of
> the table, and a SELECT acquires AccessShareLock on all indexes of the
> table (even if the particular plan at issue does not use them); e.g.
> in this case the plan is a Seq Scan.  REINDEX acquires only ShareLock
> on the table itself, but this apparently does nobody wanting to run a
> query any good.

Yeah, this has been mentioned before, and it's pretty infuriating, but I
don't think we have any solution currently in the cards.  I think a
workaround is to use prepared queries that don't involve the index,
since it's only the planning phase that wants to acquire lock on indexes
that execution doesn't use.  I don't see this as a practical solution.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services