Thread: REINDEX locking

REINDEX locking

From
Robert Haas
Date:
The documentation for the new REINDEX CONCURRENTLY option says:

"When this option is used, PostgreSQL will rebuild the index without
taking any locks that prevent concurrent inserts, updates, or deletes
on the table; whereas a standard reindex build locks out writes (but
not reads) on the table until it's done."

This statement appears to be false, not because it's wrong about
REINDEX CONCURRENTLY but because it's wrong about regular REINDEX.

S1:

rhaas=# begin;
BEGIN
rhaas=# select * from pgbench_branches where filler = 'afafa';
 bid | bbalance | filler
-----+----------+--------
(0 rows)

S2:

rhaas=# reindex index pgbench_branches_pkey;
-- hangs

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?

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



Re: REINDEX locking

From
Peter Geoghegan
Date:
On Thu, Jun 13, 2019 at 1:04 PM Robert Haas <robertmhaas@gmail.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



Re: REINDEX locking

From
Alvaro Herrera
Date:
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



Re: REINDEX locking

From
Robert Haas
Date:
On Thu, Jun 13, 2019 at 4:10 PM Alvaro Herrera <alvherre@2ndquadrant.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