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
