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