Re: REINDEX locking

On Thu, Jun 13, 2019 at 1:04 PM Robert Haas <robertmhaas(at)gmail(dot)com> 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.
>
> Is it supposed to work this way? Am I confused?

I've always thought that this framing was very user-hostile.
Theoretically, REINDEX doesn't have to block reads (e.g. it won't with
prepared statements when various conditions are met), but in practice
the behavior isn't meaningfully different from blocking reads.

--
Peter Geoghegan