Обсуждение: REINDEX locking
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
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
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
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