Re: REINDEX locking

On Thu, Jun 13, 2019 at 4:10 PM Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
> 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.

Wow, that's not nice at all. I feel like we should at least document
this better.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company